help limiting search results

cs1h · September 18, 2007

Hi,

I have written a search script for my database but now I want to limit the results to ten a page and then make it possible to see the next (or previous) page. All my attempts so far have failed. Can anyone help me.

My script so far is,

<?

$limit = 10;

$targetb = $_POST['menuFilesDMA'];
$targetb = str_replace(' ','_', $targetb);

mysql_connect("localhost","adder","clifford"); 


mysql_select_db("real") or die("Unable to select database"); 

$keywords = preg_split("/[\s,]+/", trim($_POST['keyword']));
$sql = "SELECT * FROM items WHERE country='" . mysql_real_escape_string($targetb) . "' AND type='" . mysql_real_escape_string($_POST['Type']) . "' AND Abstract LIKE '%$keyword%'";

$numresults=mysql_query ($query);
$row_num_links_main =mysql_num_rows ($numresults);
if (empty($s)) {
         $s=0;
     }

{
// now let's get results.
      $query .= " LIMIT $s,$limit" ;
      $numresults = mysql_query ($query) or die ( "Couldn't execute query" );
      $row= mysql_fetch_array ($numresults);

      //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
      do{
//EDIT HERE and specify your field name that is primary key
          $adid_array[] = $row[ 'id' ];
      }while( $row= mysql_fetch_array($numresults));
} //end foreach

if($row_num_links_main == 0 && $row_set_num == 0){
   $resultmsg = "<p>Search results for:" . $trimmed  ."</p><p>Sorry, your search returned zero results</p>" ;
} else {
while($row = mysql_fetch_array($result)) {
$Country = $row['country']; 
$Type = $row['type'];
$More = $row['id'];
$Title = $row['Title'];
$Abs = $row['Abstract'];
echo "<table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td width=\"14\" height=\"28\" background=\"line_left_corner_top.png\"> </td>
    <td height=\"28\" colspan=\"4\" background=\"line_top.png\"> </td>
    <td height=\"28\" background=\"line_right_corner_top.png\"> </td>
  </tr>
  <tr>
    <td width=\"14\" rowspan=\"3\" background=\"line_left.png\"> </td>
    <td width=\"290\" height=\"21\"><span class=\"Large_Blue\">$Title</span></td>
    <td width=\"114\" height=\"21\"><img src=\"stars_five.png\" width=\"114\" height=\"21\" /></td>
    <td width=\"14\"> </td>
    <td width=\"128\" height=\"128\" rowspan=\"3\"><<img src=\"/searchthumbs/$Country$Type.png\" width=\"128\" height=\"128\" /></td>
    <td width=\"14\" rowspan=\"3\" background=\"line_right.png\"> </td>
  </tr>
  <tr>
    <td height=\"86\" colspan=\"2\"><span class=\"Small_Black\">$Abs</span></td>
    <td width=\"14\"> </td>
  </tr>
  <tr>
    <td width=\"290\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"style5\">>></span> <span class=\"style7\"><a href=asearcher.php?text=$More&Submit=Submit>Read More</a> </span> <span class=\"style5\">>></span></td>
    <td width=\"114\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"Small_Grey\">12/09/2007</span></td>
    <td width=\"14\"> </td>
  </tr>
  <tr>
    <td width=\"14\" height=\"19\" background=\"line_left_corner.png\"> </td>
    <td height=\"28\" colspan=\"4\" background=\"line_base.png\"> </td>
    <td width=\"14\" height=\"19\" background=\"line_right_corner.png\"> </td>
  </tr>
</table>";

}
}
}   //end foreach $trimmed_array 
   if($row_num_links_main > $limit){
   // next we need to do the links to other search result pages
      if ($s>=1) { // do not display previous link if 's' is '0'
        $prevs=($s-$limit);
         echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</a></div>";
      }
     // check to see if last page
     $slimit =$s+$limit;
       if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
     // not last page so display next link
          $n=$s+$limit;
           echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
        }
    }
}  //end foreach $newarr

?>

Any help is much appriciated,

Thanks,

Colin

cooldude832 · September 18, 2007

right after sql add

$sql .= "LIMIT ".$limit;

Also its considered better query structure to not have the injection prevention in the query string, but instead format the variables outside the query string and use the formatted variables in the string.

Note I didnt' read far enough, the idea is sorta right except you need to do a two fold step

First figure out the number of relavent records, which $sql looks to do, but don't use that nasty star operator, use no operator because you just need a count so you can actually select count(id) and you have the number of realvent records.

Then you need to do some math

You have a limit set in the page and a page number (assumed being passed via get) so you need to then figure out what the begining of the query is based on $limit*$page then set the second part of limit to $limit (It will get all records from the record number $limit*$page till the next 10 show up.

Then you are all set, but don't use that nasty star operator on your row count.

remlabm · September 18, 2007

$query .= " LIMIT $s,$limit" ;

$numresults = mysql_query ($query) or die ( "Couldn't execute query" );

should be:

$sql .= " LIMIT $s,$limit" ;

$numresults = mysql_query ($sql) or die ( "Couldn't execute query" );

cs1h · September 18, 2007

Hi,

Thanks for your help, I made the changes you surgested and I now get the error,

Parse error: syntax error, unexpected '}' in D:\Inetpub\vhost\myroho.com\httpdocs\absearch.php on line 76

Does anyone know why this is, sorry for all these questions I'm still very new to php.

Cheers,

Colin

remlabm · September 18, 2007

on line 22 remove the {

and line 76 remove the }

remlabm · September 19, 2007

after looking at the code further.... you had alot of extra {} in ther... i have updated the code below... copy paste should do the trick.

$limit = 10;

$targetb = $_POST['menuFilesDMA'];
$targetb = str_replace(' ','_', $targetb);

mysql_connect("localhost","adder","clifford"); 


mysql_select_db("real") or die("Unable to select database"); 

$keywords = preg_split("/[\s,]+/", trim($_POST['keyword']));
$sql = "SELECT * FROM items WHERE country='" . mysql_real_escape_string($targetb) . "' AND type='" . mysql_real_escape_string($_POST['Type']) . "' AND Abstract LIKE '%$keyword%'";

$numresults=mysql_query ($query);
$row_num_links_main =mysql_num_rows ($numresults);
if (empty($s)) {
$s=0;
}


// now let's get results.
$sql.= " LIMIT $s,$limit" ;
$numresults = mysql_query ($sql) or die ( "Couldn't execute query" );
$row= mysql_fetch_array ($numresults);

//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
do{
//EDIT HERE and specify your field name that is primary key
$adid_array[] = $row[ 'id' ];
while( $row= mysql_fetch_array($numresults));


if($row_num_links_main == 0 && $row_set_num == 0){
	$resultmsg = "<p>Search results for:" . $trimmed  ."</p><p>Sorry, your search returned zero results</p>" ;
} else {
	while($row = mysql_fetch_array($result)) {
		$Country = $row['country']; 
		$Type = $row['type'];
		$More = $row['id'];
		$Title = $row['Title'];
		$Abs = $row['Abstract'];
		echo "<table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
		<tr>
		<td width=\"14\" height=\"28\" background=\"line_left_corner_top.png\"> </td>
		<td height=\"28\" colspan=\"4\" background=\"line_top.png\"> </td>
		<td height=\"28\" background=\"line_right_corner_top.png\"> </td>
		</tr>
		<tr>
		<td width=\"14\" rowspan=\"3\" background=\"line_left.png\"> </td>
		<td width=\"290\" height=\"21\"><span class=\"Large_Blue\">$Title</span></td>
		<td width=\"114\" height=\"21\"><img src=\"stars_five.png\" width=\"114\" height=\"21\" /></td>
		<td width=\"14\"> </td>
		<td width=\"128\" height=\"128\" rowspan=\"3\"><<img src=\"/searchthumbs/$Country$Type.png\" width=\"128\" height=\"128\" /></td>
		<td width=\"14\" rowspan=\"3\" background=\"line_right.png\"> </td>
		</tr>
		<tr>
		<td height=\"86\" colspan=\"2\"><span class=\"Small_Black\">$Abs</span></td>
		<td width=\"14\"> </td>
		</tr>
		<tr>
		<td width=\"290\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"style5\">>></span> <span class=\"style7\"><a href=asearcher.php?text=$More&Submit=Submit>Read More</a> </span> <span class=\"style5\">>></span></td>
		<td width=\"114\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"Small_Grey\">12/09/2007</span></td>
		<td width=\"14\"> </td>
		</tr>
		<tr>
		<td width=\"14\" height=\"19\" background=\"line_left_corner.png\"> </td>
		<td height=\"28\" colspan=\"4\" background=\"line_base.png\"> </td>
		<td width=\"14\" height=\"19\" background=\"line_right_corner.png\"> </td>
		</tr>
		</table>";

	}
}
//end foreach $trimmed_array 
if($row_num_links_main > $limit){
	// next we need to do the links to other search result pages
	if ($s>=1) { // do not display previous link if 's' is '0'
		$prevs=($s-$limit);
		echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</a></div>";
	}
	// check to see if last page
	$slimit =$s+$limit;
	if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
		// not last page so display next link
		$n=$s+$limit;
		echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
	}
}
}

cs1h · September 19, 2007

Hi sorry for this but I now get this error,

Parse error: syntax error, unexpected ';', expecting T_WHILE in D:\Inetpub\vhost\myroho.com\httpdocs\absearch.php on line 93

Any help is much appriciated,

Thanks,

Colin

remlabm · September 19, 2007

remove line 32 completely.

while( $row= mysql_fetch_array($numresults));

cs1h · September 19, 2007

Cheers for the reply,

It solved that error but now I get this one,

Parse error: syntax error, unexpected ';', expecting T_WHILE in D:\Inetpub\vhost\myroho.com\httpdocs\absearch.php on line 93

All help is appriciated.

Cheers

Colin

trq · September 19, 2007

Which is line 93? Seriously, parse errors are something you really need to find yourself. They are caused by simple mistakes.

cs1h · September 19, 2007

Line 93 is the last line, the script now looks like this,

<?

$limit = 10;

$targetb = $_POST['menuFilesDMA'];
$targetb = str_replace(' ','_', $targetb);

mysql_connect("localhost","adder","clifford"); 


mysql_select_db("real") or die("Unable to select database"); 

$keywords = preg_split("/[\s,]+/", trim($_POST['keyword']));
$sql = "SELECT * FROM items WHERE country='" . mysql_real_escape_string($targetb) . "' AND type='" . mysql_real_escape_string($_POST['Type']) . "' AND Abstract LIKE '%$keyword%'";

$numresults=mysql_query ($query);
$row_num_links_main =mysql_num_rows ($numresults);
if (empty($s)) {
$s=0;
}


// now let's get results.
$sql.= " LIMIT $s,$limit" ;
$numresults = mysql_query ($sql) or die ( "Couldn't execute query" );
$row= mysql_fetch_array ($numresults);

//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
do{
//EDIT HERE and specify your field name that is primary key
$adid_array[] = $row[ 'id' ];



if($row_num_links_main == 0 && $row_set_num == 0){
	$resultmsg = "<p>Search results for:" . $trimmed  ."</p><p>Sorry, your search returned zero results</p>" ;
} else {
	while($row = mysql_fetch_array($result)) {
		$Country = $row['country']; 
		$Type = $row['type'];
		$More = $row['id'];
		$Title = $row['Title'];
		$Abs = $row['Abstract'];
		echo "<table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
		<tr>
		<td width=\"14\" height=\"28\" background=\"line_left_corner_top.png\"> </td>
		<td height=\"28\" colspan=\"4\" background=\"line_top.png\"> </td>
		<td height=\"28\" background=\"line_right_corner_top.png\"> </td>
		</tr>
		<tr>
		<td width=\"14\" rowspan=\"3\" background=\"line_left.png\"> </td>
		<td width=\"290\" height=\"21\"><span class=\"Large_Blue\">$Title</span></td>
		<td width=\"114\" height=\"21\"><img src=\"stars_five.png\" width=\"114\" height=\"21\" /></td>
		<td width=\"14\"> </td>
		<td width=\"128\" height=\"128\" rowspan=\"3\"><<img src=\"/searchthumbs/$Country$Type.png\" width=\"128\" height=\"128\" /></td>
		<td width=\"14\" rowspan=\"3\" background=\"line_right.png\"> </td>
		</tr>
		<tr>
		<td height=\"86\" colspan=\"2\"><span class=\"Small_Black\">$Abs</span></td>
		<td width=\"14\"> </td>
		</tr>
		<tr>
		<td width=\"290\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"style5\">>></span> <span class=\"style7\"><a href=asearcher.php?text=$More&Submit=Submit>Read More</a> </span> <span class=\"style5\">>></span></td>
		<td width=\"114\" height=\"19\" align=\"left\" valign=\"bottom\"><span class=\"Small_Grey\">12/09/2007</span></td>
		<td width=\"14\"> </td>
		</tr>
		<tr>
		<td width=\"14\" height=\"19\" background=\"line_left_corner.png\"> </td>
		<td height=\"28\" colspan=\"4\" background=\"line_base.png\"> </td>
		<td width=\"14\" height=\"19\" background=\"line_right_corner.png\"> </td>
		</tr>
		</table>";

	}
}
//end foreach $trimmed_array 
if($row_num_links_main > $limit){
	// next we need to do the links to other search result pages
	if ($s>=1) { // do not display previous link if 's' is '0'
		$prevs=($s-$limit);
		echo "<div align='left'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</a></div>";
	}
	// check to see if last page
	$slimit =$s+$limit;
	if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
		// not last page so display next link
		$n=$s+$limit;
		echo "<div align='right'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
	}
}
}

?>

Thanks

Colin

sasa · September 19, 2007

remove do in line 29

Sign In

help limiting search results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information