Jump to content

LIMIT Results - subquery difficulty


EternalSorrow

Recommended Posts

I currently have a simple listing for results found in my databases which has two queries with one inside the other.  The table and page layout is like so:

 

PEOPLE

Paul Muni

Errol Flynn

Olivia de Havilland

Alan Hale

Basil Rathbone

 

FILM

Angel On My Shoulder

The Adventures of Robin Hood

 

RELATED

Angel On My Shoulder | Paul Muni

The Adventures of Robin Hood | Errol Flynn

The Adventures of Robin Hood | Olivia de Havilland

The Adventures of Robin Hood | Alan Hale

The Adventures of Robin Hood | Basil Rathbone

 

So the output would be like this:

 

Angel On My Shoulder | Paul Muni | The Adventures of Robin Hood | Errol Flynn | Olivia de Havilland | Alan Hale | Basil Rathbone

 

The codes themselves work find, but I've realized the results are too many for the custom layout I've created.  I would like to do an IF statement, using the mysql_num_rows function, to limit the number of results shown but the subquery is a little tricky:

 

$select_cast = mysql_query("SELECT * FROM people LEFT JOIN related ON people.name = related.name WHERE related.fid = '$fid' AND people.name != '{$name}' ORDER BY datetime DESC ") or die (mysql_error());

$cast = "";
while ($row2 = mysql_fetch_array($select_cast)) {
   $cast .= "<li><a href=\"biography.php?name={$row2[name]}\"><img src=\"images/thumbnails/{$row2[img]}.jpg\" alt=\"\"  title=\"{$row2[name]} ({$row2[occupation]})\"></a></li>";
}

 

Simply performing a mysql_num_rows for $select_cast wouldn't retrieve the correct number of results if there's more than one movie.  It would read only the number of cast in the first movie listed, like so:

 

Angel On My Shoulder | (reads nothing here) | The Adventures of Robin Hood | 4

 

My question is: how would I go about finding the total number of results for the subquery ($select_cast)?

 

Here's the total code:

<?php

 

$query = "SELECT * FROM related LEFT JOIN film ON related.fid = film.fid WHERE related.name = '$name' ORDER BY datetime DESC ";

$result = mysql_query( $query ) or die(mysql_error());

$numb = mysql_num_rows($result);

 

while ($row = mysql_fetch_array($result))

{

extract($row);

 

$select_cast = mysql_query("SELECT * FROM people LEFT JOIN related ON people.name = related.name WHERE related.fid = '$fid' AND people.name != '{$name}' ORDER BY datetime DESC ") or die (mysql_error());

 

$cast = "";

while ($row2 = mysql_fetch_array($select_cast)) {

  $cast .= "<li><a href=\"biography.php?name={$row2[name]}\"><img src=\"images/thumbnails/{$row2}.jpg\" alt=\"\"  title=\"{$row2[name]} ({$row2[occupation]})\"></a></li>";

}

 

echo '<li><a href="filmography.php?fid='.$fid.'"><img src="images/thumbnails/'.$image.'.jpg" alt="" title="'.$title.' ('.$year.')"></a></li>

'.$cast.'';

 

}

 

echo '<li class="arrow"><a href="collectiontopic.php?name='.$name.'" title="click to view more related topics"></a></li>';

?>

Link to comment
https://forums.phpfreaks.com/topic/182441-limit-results-subquery-difficulty/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.