CODEWITHSUNDEEP
phpmysql
Ben Hathaway
Ben Hathaway

Reputation: 356

Displaying Multiple Columns Mysql

I am currently trying to display papers members have uploaded to my website, the user clicks on a topic and it shows the papers which are under that topic. However my query just shows me all the papers not the ones with the specific topic. 

//time to get our info
//time to get our info
$sql = "SELECT paper.title, paper.username, paper.abstract, paper.filelocation FROM `paper`, `topic` WHERE topic_name = 'artificial intelligence' ";
$result = mysql_query($sql);
while($file = mysql_fetch_array($result)){
    echo '<li>';
    echo '<h1>'.$file['title'].'</h1><br />';
    //now the file info and link
    echo '<h3>Uploaded By: '.$file['username'].'</h3><br />';
    echo '<a href="'.$file['filelocation'].'">'.$file['title'].'</a>';
    echo '<h1> Abstract : ' .$file['abstract'].'</h1><br />';
    echo '</li>';
}
?>
</ul

Here are my tables asso

paper_id topic_id

topic

topic_id topic__name

paper

paper_id title username abstract filelocation

Upvotes: 1

Views: 161

Answers (1)

mellamokb
mellamokb

Reputation: 56769

You need to join the two tables together with a key condition that links them:

SELECT
    paper.title,
    paper.username,
    paper.abstract,
    paper.filelocation
FROM `paper`
INNER JOIN `topic` on `paper`.`topic_id` = `topic`.`topic_id`
WHERE topic_name = 'artificial intelligence'

Otherwise you are cross joining both tables, which gives you every possible combination between the two tables.

Upvotes: 5

Related Questions