Page 1 of 1

Website search pagination trouble???

Posted: Sun Jun 13, 2010 6:27 am
by simmsy
Hi I was wondering if anyone could help with my php coding I recently started so any help would be appreciated ive got a simple website search and the search performs fine its just trouble with the pagination it constantly shows up that theres 3 pages even if there should only be 1 and when they click on next page that page doesn't exist so take them back to index. Heres the code please help if you can spot the problem please:

Code: Select all

 <?php
		  //connect to our datebase
include("connect.php"); 

$limit = 10; //Number of results per page 
$page=$_GET["page"]; //Get the page number to show 
if($page == "") $page=1; //If no page number is set, the default page is 1 

//Get the number of results 
$searchresult=mysql_query("SELECT * FROM search WHERE keywords LIKE '%$searchstring%' ORDER BY title") or die(mysql_error()); 
$numberofresults=mysql_num_rows($searchresult); 

//Get the number of pages 
$numberofpages=ceil($numberofresults/$limit); 

//get data
$button = $_GET['submit'];
$search = $_GET['search'];

if ($search==""){
    echo "<script language=\"JavaScript\">\n";
echo "alert('No search entered!');\n";
echo "window.location='search.php'";
echo "</script>";
}else{
        if (strlen($search)<=2){
                echo "<script language=\"JavaScript\">\n";
echo "alert('Search too short!');\n";
echo "window.location='search.php'";
echo "</script>";
        }else{
                echo "You searched for <b>'$search'</b><hr size='1'>";
               

               
                //explode our search term
                $search_exploded = explode(" ",$search);
               
                foreach($search_exploded as $search_each){
                        //construct query
                        $x++;
                        if ($x == 1){
                                $construct .= "keywords LIKE '%$search_each%'";
                        }else{
                                $construct .= "OR keywords LIKE '%$search_each%'";
                        }
                }
               
                $construct = ("SELECT * FROM search WHERE $construct ORDER BY title LIMIT " . ($page-1)*$limit . ",$limit");
                $run = mysql_query($construct);
               
                $foundnum = mysql_num_rows($run);
               
                if ($foundnum == 0){
                        echo "<script language=\"JavaScript\">\n";
echo "alert('No results found!');\n";
echo "window.location='index.php'";
echo "</script>";
die;

                }
                       
                        while ($runrows = mysql_fetch_assoc($run)){
                                //get data
                                $title = $runrows['title'];
                                $desc = $runrows['description'];
                                $url = $runrows['url'];
								$picture = $runrows['picture'];
								
                               echo "<table width='600' align='center'>
              <tr>
                <td rowspan='2' align='left' valign='middle' width='110'><a href='$url'><img src='$picture' height='100' width='100' border='0'></a></td>
                <td align='center' valign='middle'><a href='$url'><b>$title</b></a></td>
              </tr>
            </table>";
} 
}
?>
</div>
<div align="center">
<?				
						$nav=""; 
if($page > 1) { 
$nav .= "<a href=\"searching.php?page=" . ($page-1) . "&search=" .urlencode($search) . "&submit=" .urlencode($button) . "\"><< Prev</A>"; 
} 
for($i = 1 ; $i <= $numberofpages ; $i++) { 
if($i == $page) { 
$nav .= "<b>$i</b>"; 
}else{ 
$nav .= "<a href=\"searching.php?page=" . $i . "&search=" .urlencode($search) . "&submit=" .urlencode($button) . "\"> $i </A>"; 
} 
} 
if($page < $numberofpages) { 
$nav .= "<a href=\"searching.php?page=" . ($page+1) . "&search=" .urlencode($search) . "&submit=" .urlencode($button) . "\">Next >></A>"; 
} 

Echo "<br><br>" . $nav; 
                        }
      
?>
</div>
Any help welcome Thanks!

Re: Website search pagination trouble???

Posted: Sun Jun 13, 2010 4:36 pm
by cpetercarter
Your code runs two database queries. The first is used to count the number of rows "WHERE keywords LIKE '%$searchstring%". I cannot see where $searchstring is defined. You use the result of this query to calculate the number of records, and the number of pages which you need. You use this calculation of the number of pages in constructing the pagination string. I guess that whatever query you run, this calculation tells you that you need three pages to display the result.

The second database query is the one you use to actually find the records you want to display. It is structured to look for keywords which are "like" each separate word (space separated) in the submitted search term. The point is - it is not the same query as the first query. Not surprisingly, it returns a different number of records from the first query.

You should not need two different queries. Instead, construct a single basic query ("WHERE keywords LIKE 'abc' OR keywords LIKE 'def' ...). First run it as a COUNT query ("SELECT COUNT(*) FROM search WHERE etc..."). This will give you the total number of records, from which you can calculate the number of pages you will need. Then run it as an ordinary select query, but with a limit ($limit) and an offset (calculated as (page-1) * $limit) to return only the records appropriate to the page you want to display ( eg "SELECT * FROM search WHERE ...etc... ORDER BY title LIMIT $limit OFFSET $offset ")

Re: Website search pagination trouble???

Posted: Mon Jun 14, 2010 3:57 am
by simmsy
ahh got it working now thanks alot!