Displaying Multiple Columns Mysql

Question

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

mellamokb · Accepted Answer · 2012-04-09 15:25:30Z

5

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.

answered Apr 9, 2012 at 15:25

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Marc B Over a year ago

Technically, the OP is, but hasn't specified a join condition so it's running as a cartesian join. But otherwise spot on. +1.

Ben Hathaway Over a year ago

I used the code but i got this error message Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\ai.php on line 30

mellamokb Over a year ago

Obviously, you need to modify the column names to match your table structure. What does your updated code look like, can you add an update to your question? Also, the error message sounds like the query is not able to execute. Can you dump the error, i.e., $result = mysql_query($sql) or die(mysql_error())?

Ben Hathaway Over a year ago

I think it may be because i keep the topic_id in another file called associated. Although i cant work out how to use it to display the papers

mellamokb Over a year ago

Can you post your table structures, i.e., using describe topic and descript paper?

|

Collectives™ on Stack Overflow

Displaying Multiple Columns Mysql

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related