EternalSorrow Posted November 21, 2009 Share Posted November 21, 2009 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 More sharing options...
EternalSorrow Posted November 22, 2009 Author Share Posted November 22, 2009 Bump. Link to comment https://forums.phpfreaks.com/topic/182441-limit-results-subquery-difficulty/#findComment-963281 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.