php/sql search help

[danx30]

My search is working just perfect, except since there are well over 1500 entries they are all listed on the same page, how would I change this code so that it only displays 25 or 50 results on each page?  I know there are topics on this, and I have read them but with no luck.

 

<table border="0" width="100%">
<tr>
<td width="6%" bgcolor="#00FFFF"><p align="center"><small><font face="Verdana">Rank</font></small></td>
<td width="7%" bgcolor="#00FFFF"><p align="center"><small><font face="Verdana">Name</font></small></td>
<td width="11%" bgcolor="#00FFFF"><p align="center"><small><font face="Verdana">Amount Owed</font></small></td>
<td width="76%" bgcolor="#00FFFF"><p align="left"><small><font face="Verdana">IP Number</font></small>
</td>

<?php

  // Get the search variable from URL

  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }


if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

mysql_connect("localhost","username","password"); //(host, username, password)
mysql_select_db("db") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * FROM users WHERE name LIKE \"%$trimmed%\"  
  order by name ASC"; // EDIT HERE and specify your table and field names for the SQL query

$result = mysql_query($query);
if(mysql_num_rows($result)) {
  $rank = 1;
  while($row = mysql_fetch_row($result))
  {
    print("</tr><tr>");
    if($color == "#D8DBFE") {
     $color = "#A6ACFD";
    } else {
      $color = "#D8DBFE";
    }
   print("<td width=\"6%\" bgcolor=\"$color\"><center><small>");
   print("<font face=\"Verdana\">$rank</font></small></center></td>");
   print("<td width=\"7%\" bgcolor=\"$color\"><center><small>");
   print("<font face=\"Verdana\"><strong>$row[0]</strong></font></small></center></td>");
   print("<td width=\"11%\" bgcolor=\"$color\">$row[2]<center><small>");
   print("<td width=\"76%\" bgcolor=\"$color\"><left><small>");
   print("<font face=\"Verdana\">$row[15]</font></small></left></td>");
  $rank++;
  }
}
?>

[genericnumber1]

Did you read this tutorial?

 

http://www.phpfreaks.com/tutorials/43/0.php

 

I think it's helped others in the past, though I've never read it all the way through.

[danx30]

I did read that.  It didn't help me. 

[genericnumber1]

Well do you have a more specific problem? Just throwing "I need pagination" out doesn't really help us help you... I help those who help themselves there are way too many articles out there on pagination for me to believe they don't help you.

 

Is there anything you need explained perhaps?

[rcorlew]

I just did something like that here is what I used, it may or may not work but it is worth a shot:

<?php
$page = $_GET["page"];
             $row_max = 20; //or how many results you want per page

if(!isset($page)) {
 $page = 1;
}

 if($page == 1) {	 	
 	$pagein = 0;
 	$pageout = $row_max;
 }	

if($page > 1) { 
	$pagein = $page * $row_max - $row_max;
	$pageout = $pagein * $row_max;
}

$query = "SELECT * FROM tablename WHERE thing = '$thing'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$query2 = "SELECT * FROM tablename WHERE thing = '$thing' LIMIT $pagein, $pageout";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2)) {

//show table results here

}
			$pagecount = ceil($num / $row_max);
			echo "Page ";
$x = 1;
$y = $pagecount;
for( $i = $x; $i <= $y; $i++ ) {
print "<a href='index.php?page=$i'><b>[$i]</b></a> ";
}
?>

 

I hope you get the idea

[rcorlew]

I just did something like that here is what I used, it may or may not work but it is worth a shot:

<?php
$page = $_GET["page"];
             $row_max = 20; //or how many results you want per page

if(!isset($page)) {
 $page = 1;
}

 if($page == 1) {	 	
 	$pagein = 0;
 	$pageout = $row_max;
 }	

if($page > 1) { 
	$pagein = $page * $row_max - $row_max;
	$pageout = $row_max;// changed the limit out to $row_max sorry for the mistake
}

$query = "SELECT * FROM tablename WHERE thing = '$thing'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$query2 = "SELECT * FROM tablename WHERE thing = '$thing' LIMIT $pagein, $pageout";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2)) {

//show table results here

}
			$pagecount = ceil($num / $row_max);
			echo "Page ";
$x = 1;
$y = $pagecount;
for( $i = $x; $i <= $y; $i++ ) {
print "<a href='index.php?page=$i'><b>[$i]</b></a> ";
}
?>

 

I hope you get the idea

[danx30]

I tried to combine what you posted with my script and I get nothing but a blank page.    I'm sure it's something simple I'm not seeing.

 

<table border="0" width="100%">
<tr>
<td width="6%" bgcolor="#00FFFF"><p align="center"><small><font 

face="Verdana">Rank</font></small></td>
<td width="7%" bgcolor="#00FFFF"><p align="center"><small><font 

face="Verdana">Name</font></small></td>
<td width="11%" bgcolor="#00FFFF"><p align="center"><small><font face="Verdana">Amount 

Owed</font></small></td>
<td width="76%" bgcolor="#00FFFF"><p align="left"><small><font face="Verdana">IP 

Number</font></small>
</td>

<?php
$page = $_GET["page"];

        $var = @$_GET['q'] ;
        $trimmed = trim($var);
                $row_max = 20; //or how many results you want per page

if(!isset($page)) {
 $page = 1;
}

 if($page == 1) {	 	
 	$pagein = 0;
 	$pageout = $row_max;
 }	

if($page > 1) { 
	$pagein = $page * $row_max - $row_max;
	$pageout = $pagein * $row_max;
}

mysql_connect("localhost","username","password");
mysql_select_db("db") or die("Unable to select database");
$query = "select * FROM users WHERE name LIKE \"%$trimmed%\"  
  order by name ASC";
$result = mysql_query($query);
$num = mysql_num_rows($result);
$query2 = "SELECT * FROM users WHERE name LIKE \"%trimmed%\" LIMIT $pagein, $pageout";
$result2 = mysql_query($query2);
$rank = 1;
while ($row = mysql_fetch_array($result2)) {

    print("</tr><tr>");
    if($color == "#D8DBFE") {
     $color = "#A6ACFD";
    } else {
      $color = "#D8DBFE";
    }
   print("<td width=\"6%\" bgcolor=\"$color\"><center><small>");
   print("<font face=\"Verdana\">$rank</font></small></center></td>");
   print("<td width=\"7%\" bgcolor=\"$color\"><center><small>");
   print("<font face=\"Verdana\"><strong>$row[0]</strong></font></small></center></td>");
   print("<td width=\"11%\" bgcolor=\"$color\">$row[2]<center><small>");
   print("<td width=\"76%\" bgcolor=\"$color\"><left><small>");
   print("<font face=\"Verdana\">$row[15]</font></small></left></td>");
  $rank++;
  }



}
			$pagecount = ceil($num / $row_max);
			echo "Page ";
$x = 1;
$y = $pagecount;
for( $i = $x; $i <= $y; $i++ ) {
print "<a href='newsearch.php?page=$i'><b>[$i]</b></a> ";
}
?>

[danx30]

Solved it, found a missing $ and that fixed everything.

 

Thanks for all the help.

[rcorlew]

Glad to be of serivce.