Trying to get LIMIT 0, 3 order from two MySQL tables

[suttercain]

Good morning everyone,

 

I am attempting to get LIMIT 0, 3 from two MySQL tables and I don't think I have it set up the right way. Both tables have the column comic_id in which they are relational.

 

When I run this I get the effect I want, but only 1 record:

<?php
				//REVIEW Genrator
    $sqlz = "SELECT * FROM reviews";
    if ($resultz = mysql_query($sqlz)) {
      if (mysql_num_rows($resultz)) {
        $rowz = mysql_fetch_assoc($resultz);
      }
    }
  //Comic Information Query
    $sqls = "SELECT * FROM comics WHERE comic_id = '" . $rowz['comic_id'] . "'";
    if ($results = mysql_query($sqls)) {
      if (mysql_num_rows($results)) {
        $rows = mysql_fetch_assoc($results);
	echo "<tr>";
	echo "<td width='70'><a href='view_reviews.php?id={$rowz['review_id']}'><img src='images/4ndvddb/" . $rows['cover_art'] . "' height='75px' width='50px'/></a><br></td>";
	echo "<td><b><a href='view_reviews.php?id={$rowz['review_id']}'>" . $rows['title'] . " #" . $rows['issue_number'] . "</a></b><br>Cover Date: " . $rows['cover_date'] . "<br>Written By: " . 
	$rows['writers'] . "<br>Cover Artists: " . $rows['cover_artists'] . "<br>Artists: " . $rows['pencillers'] . "</td>";
	echo "</tr>";
      }
    }
?>

 

But when I add LIMIT 0, 3 to the query I get no results. Is there a way to do this? Should I restructure the query for a more efficient set-up? Thanks, this is the first time I am trying to get results based on the information from two tables.

 

SC

[lopez86100]

I don't know if I understand you exactly but if I think right way You should do something like this:

 

$con=mysql_connect('host',........................

$res1=mysql_query("select * from reviews");
$limit=3; // your limit;
while($res=mysql_fetch_array($res1)&&$r<$limit){


$com_id=$res['comic_id'];

$res2=mysql_query("select * from comics where comic_id = \"$com_id\"");

$res3=mysql_fetch_array($res2);

$res4=$res3['comic_id'];

echo // here you can output the comic information retrieved from the table reviews and the second table



}


I didn't test it , but you should do something like this. 

If you will give an example of output you want to get then I will correct this example.

[Barand]

to select comic and related reviews in single query

    SELECT r.review_id, c.cover_art, c.title. c.issue_number, c.writers, c.cover_artists, c.pencillers
    FROM reviews r
    INNER JOIN comics c ON r.comic_id = c.comic_id