[SOLVED] Selecting multiple rows... this is a lil more complicated for me

[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.

 

SC

[zq29]

I think you're probably wanting to do this:

<?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)) {
        while($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'];
                }
            }
        }
    }
} 
?>

Though, it might be a little more efficient to do the following:

<?php
$r = mysql_query("SELECT c.* FROM `comics` as c, `user_comics` as uc WHERE uc.`user_id`='$_GET[id]' AND uc.`comic_id` = c.`comic_id`") or die(mysql_error());
while($rr = mysql_fetch_assoc($r)) {
    echo "$rr[title]<br />";
}
?>

[suttercain]

Thanks Semi,

 

That second one is a table join right?

 

It worked. Thanks for the help!

 

SC

[zq29]

Correct, no problem