Echoing multiple rows from MySQL.. a little complicated this time

suttercain

Hi everyone!

Right now I have Two MySQL Tables set up as such:

COMIC:

title | comic_id

sup | 456
aos | 898

USER_COMICS

comic_id | user_id

456 | 11
898 | 11
986 | 14

So what I am trying to do, using the get method and a link, is GET all the results where $_GET['id'] = (let's say 11):

<?php 

//GETTING THE INFORMATION FROM THE USER_COMICS TABLE
$sql = "SELECT * FROM user_comics WHERE user_id = '" . mysql_real_escape_string($_GET['id']) . "'";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
$row = mysql_fetch_assoc($result);
    }
} 

//TRYING TO GET ALL THE ROW FROM THE COMICS TABLE 
$sql1 = "SELECT title, issue_number, cover_date, comic_id, type FROM comics WHERE comic_id ='" .$row['comic_id']."'"; 
$result1 = mysql_query($sql1) or die(mysql_error());
if (mysql_num_rows($result1)) {
while ($row1 = mysql_fetch_assoc($result1)) {
$cover_date = date('F, Y', strtotime($row1['cover_date']));
echo $row1['title'];
          }
}
?>

So instead of echoing all the results, it only echos the first one. I know WHY this is happening, because it's just taking the first result found from the first query, $row['comic_id'], in the WHERE statement.

My question is, how can I solve this with the current table setup, to echo all the comics from COMICS where the user_id and the comic_id are the SAME from the USER_COMICS table?

Thanks for any help or suggestions.

laserlight

Are you trying to get the titles of all comics related to a given user? If so, you are looking for a join, perhaps along these lines:

$query = sprintf("SELECT comics.title AS comic_title FROM comics, user_comics
                      WHERE comics.comic_id=user_comics.comic_id
                          AND user_comics.user_id='%s'",
                 mysql_real_escape_string($_GET['id']));
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    echo $row['comic_title'];
}