Help limiting search results

[cs1h]

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.
$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.
{
//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>";
	}
}
}

?>

 

Also from this version I am getting the following error,

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in D:\Inetpub\vhost\myroho.com\httpdocs\absearch.php on line 17

 

 

If any one can help me with the problem or tell me how to solve it, i would be very grateful.

 

Thanks,

Colin

[mgs019]

Hi,

 

Easiest way to set the number of results is to use LIMIT x,y in the sql query where x is start point (from 0) and y is duration. You will have to put a page number variable and do a bit of maths but pretty simple.

 

The variable is wrong before the mysql_num_rows. you have $query but your query is $sql.

 

Martin

[AdRock]

Sounds like you need to use pagination

 

Have a look here http://www.phpfreaks.com/tutorial_cat/25/Page-Number--Pagination.php

 

or if you want google style pagination for lots of pages, I have a script which does the job which you can edit to suit your needs

<h2>News / Events</h2><hr />
<div>
<?php 

include "includes/connection.php";

$webpage = basename(news); //this is the page that the paging is on i.e. news.php (i have used mod rewrite for my page news)
function pagination_five($total_pages,$page){ 

    global $webpage; 

    $max_links = 10; 
    $h=1; 
    if($page>$max_links){ 
        $h=(($h+$page)-$max_links); 
    } 
    if($page>=1){ 
        $max_links = $max_links+($page-1); 
    } 
    if($max_links>$total_pages){ 
        $max_links=$total_pages+1; 
    } 
    if($total_pages>1){ 
echo '<div class="page_numbers"><ul>'; 
if($page>"1"){ 
    echo '<li class="current"><a href="/'.$webpage.'/1">First</a></li> 
        <li class="current"><a href="/'.$webpage.'/'.($page-1).'">Prev</a></li> 
    '; 
} 

if($total_pages!=1){ 
    for ($i=$h;$i<$max_links;$i++){ 
	if($i==$page){ 
	    echo '<li><a class="current">'.$i.'</a></li>'; 
	} 
	else{ 
	    echo '<li><a href="/'.$webpage.'/'.$i.'">'.$i.'</a> </li>'; 
	} 
    } 
} 

if(($page >="1")&&($page!=$total_pages)){ 
    echo '<li class="current"><a href="/'.$webpage.'/'.($page+1).'">Next</a></li> 
    	<li class="current"><a href="/'.$webpage.'/'.$total_pages.'">Last</a></li> 
    '; 
} 
echo '</ul></div>';
    }  
}

$result = mysql_query("Select count(*) from news WHERE archived='n'") 
or die (mysql_error()); 
$numrows = mysql_fetch_row($result); 
  
if(isset($_GET['pagenum'])?$page = $_GET['pagenum']:$page = 1); 
$entries_per_page = 1;   

$total_pages = ceil($numrows[0]/$entries_per_page); 
$offset = (($page * $entries_per_page) - $entries_per_page); 

    //after we have $total_pages and $page, we can include the  
    //pagination style wherever we want on the page. 
    //so far there is no output from the script, so we could have  
    //pagination before or after the pages results 
     
    //before the results  

$result = mysql_query("SELECT id,title,content, DATE_FORMAT(time, '%W %D %M %Y') as date, photo, alternate FROM news WHERE archived='n' ORDER BY id DESC LIMIT  
                       $offset,$entries_per_page");
  if(!$result) die(mysql_error()); 
     $err = mysql_num_rows($result); 
       if($err == 0) die("No matches met your criteria."); 

while($row=mysql_fetch_array($result)){ 

$title = htmlentities(strtoupper($row['title'])); 
$content = htmlentities($row['content']);

    if (!$row['photo']){ 
echo "<div id='right'><h3>".$title."</h3><p class='italic'>Date added: ".$row['date']."</p><p>".nl2br($content)."</p><br /></div>\n";	
    } else {
echo "<div id='right'><h3>".$title."</h3><p class='italic'>Date added: ".$row['date']."</p><p><img src='/images/large/".$row['photo']."' alt='".$row['alternate']."' class='right' />".nl2br($content)."</p><br /></div>\n";

    } 
} 

  //or after the results 
   
pagination_five($total_pages,$page); 

?>
</div>

and the css

.page_numbers { 
    width: 600px; 
    padding: 5px 0px; 
    float:left;
    clear:left;
    margin:0 auto; 
} 

.page_numbers ul { 
    margin: 0 auto; 
    list-style-type: none; 
    padding: 0px; 
    text-align: center; 
} 

.page_numbers li { 
    display: inline; 
    float: left; 
    margin:1px; 
    background: #a7a7a7; 
    width:25px; 
} 

.page_numbers li.current{ 
  width:50px; 
} 

.page_numbers li a { 
    background: #fff; 
    border: 1px solid #a7a7a7; 
    padding: 1px; 
    text-decoration: none; 
    color: #000; 
    font:bold 8px verdana,sans-serif; 
    display:block; 
} 

.page_numbers a.current, .page_numbers li a:hover { 
    background: #a7a7a7; 
    color: #fff; 
}