[RESOLVED] Topic: 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.

sid

change

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>";
}

if (mysql_num_rows($results)) {
    while ($rows = mysql_fetch_array($results, MYSQL_ASSOC)) {
        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>";
    }
}

suttercain

Hi Sid,

Thanks for the reply. I entered the code and ran this:

<?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'] . "' LIMIT 0, 3";
    if ($results = mysql_query($sqls)) {
      if (mysql_num_rows($results)) {
    while ($rows = mysql_fetch_array($results, MYSQL_ASSOC)) {
        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>";
    		}
		} 
		}
	?>

I added the LIMIT 0, 3 to the second query but I am still getting the same results. It is only echoing the one record and not three.

Any other suggestions? Thanks again.

laserlight

Perhaps you are looking to use some sort of join, e.g.

$query = "SELECT * FROM comics, reviews WHERE comics.comic_id=reviews.comic_id LIMIT 3";
if ($result = mysql_query($query)) {
    while ($row = mysql_fetch_assoc($result)) {
        // ...
    }
}

suttercain

Hi Sid and Laserlight, my friends from around the globe. I thank you both for taking the time to assist me with this. I just tried Laserlight's join suggestions and that got it working!

Thanks again!

laserlight

You're welcome 🙂
You might want to mark this thread as resolved.