rondog Posted March 4, 2008 Share Posted March 4, 2008 I've done the pagination tutorial on here awhile ago, but at the time I was displaying a list of non searchable items so basically it displayed everything. I have now run into a problem where I need to search through a database of videos and I want to display 10 results per page. The problem is my query is only returning 10 items even though there is 150+..Here is my pagination.php file. I know their is a lot, but I think my main focus would be the query. The results are being displayed fine, just an incorrect amount. Any help would be much appreciated. <?php $searchtxt = $_GET['q']; $page = $_GET['page']; $limit = 10; if(empty($page)) { $page = 1; } $arr = explode(' ', $searchtxt); $limitvalue = $page * $limit - ($limit); $query = "SELECT * FROM video WHERE keywords LIKE '%$searchtxt%'"; foreach($arr as $v) { $query .= " OR keywords LIKE '%$v%'"; } $query .= " LIMIT $limitvalue, $limit"; $result = mysql_query($query) or die(mysql_error()); $numresults = mysql_num_rows($result); if($numresults == 0 || $numresults > 1) { $status = "Found <b>$numresults</b> results for <b>$searchtxt</b>.<br /><br /><br />"; } else { $status = "Found <b>$numresults</b> result for <b>$searchtxt</b>.<br /><br /><br />"; } echo $status; echo("<table width=\"0\" border=\"0\" cellspacing=\"0\" cellpadding=\"5\">\n"); while($row = mysql_fetch_array($result)) { $tapeid = $row["tape_id"]; $sql = mysql_query("SELECT shoot_id FROM tape WHERE id = '$tapeid'") or die(mysql_error()); $shootar = mysql_fetch_array($sql); $shootid = $shootar["shoot_id"]; $sql = mysql_query("SELECT client_id FROM shoot WHERE id = '$shootid'") or die(mysql_error()); $clientar = mysql_fetch_array($sql); $clientid = $clientar["client_id"]; $vidid = $row["id"]; $descr = $row["keywords"]; $image = $row["thumbnail"]; $title = $row["seg_title"]; echo("<div id=\"srchrs\"><tr><td width=\"140\"><a href=\"tape.php?cid=$clientid&sid=$shootid&tid=$tapeid&vid=$vidid\"><img src=\"videos/thumbnails/$image\" width=\"140\" height=\"140\" border=\"0\"/></a></td><td>Video: <span class=\"titletxt\"><a href=\"tape.php?cid=$clientid&sid=$shootid&tid=$tapeid&vid=$vidid\">$title</a></span><br /><span class=\"tagtxt\">Tags: $descr</span></td></tr></div>\n"); } echo("</table>"); if($page != 1){ $pageprev = $page-1; echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$pageprev\">« Prev</a> "); } else { echo("« Prev "); } $numofpages = $totalrows / $limit; for($i = 1; $i <= $numofpages; $i++){ if($i == $page){ echo($i." "); } else { echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$i\">$i</a> "); } } if(($totalrows % $limit) != 0){ if($i == $page){ echo($i." "); } else { echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$i\">$i</a> "); } } if(($totalrows - ($limit * $page)) > 0){ $pagenext = $page+1; echo("<a href=\"$PHP_SELF?tape=$t_loc&cam=$c_loc&page=$pagenext\">Next »</a>"); } else { echo("Next »"); } mysql_free_result($result); ?> Quote Link to comment Share on other sites More sharing options...
rbrown Posted March 4, 2008 Share Posted March 4, 2008 You are limiting the query, you only want to use the limit to display the number of results. So you are actually are only returning 10 results total. Then in your next prev statement add or substact the limit to get the next x amount of results. And pass the "count" to the next page So on the first page you want it to display 1-10 so your count will be 10 then on secound page you have to add the limit so it will display 11-21 so your count will be 21 and so on... With the prev you would substract the limit from the count and write an if statement so if the count is less than the limit ther is no prev button and if the count is greater than the total rusults returned there is no next button I don't have anything simple off hand to show you. All I have is a really complicated one with functions called into it. Quote Link to comment Share on other sites More sharing options...
rondog Posted March 4, 2008 Author Share Posted March 4, 2008 thanks rbrown...your explanation helped me run through my code and pick and choose what was wrong. I got it working. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.