Next 5 Results

[sammy182]

Hello,
I am using PHP and SQL for my database. How do I, for example, show the first 5 results and then continue the the query (right word?) on the next page if the results are more than 5?

[aspen]

http://www.webmasterbase.com/article...d=228&pid=1039

Use Limits.

Chris

[asdn]

Try this"

$sql="SELECT * FROM db LIMIT $i*5,5";
$res=mysql_query($sql,$db);

And change $i from 0 to an appropriate number. The LIMIT clause should be placed after the ORDER BY clause (if you want to make a more complicated query).

Chris

[PeterW]

Hi,
you could see for a simple implementation (with working code).
http://www.phpbuilder.com/columns/rod20000221.php3

Unfortunately, I cant vouch for the efficiency of that code. I think if you do a search, freddydoesphp made a rather good snippet for this.

[freddydoesphp]

Yeah it basically works like this

YOu need to run a count query to get the total number of records then you need to run your query using the LIMIT function then you need to determine if you need a next page button. If you search for my other posts on this you can get the code for making 1 2 3 4 5 6 type of navigation or here is a more basic approach

if(!isset($offset)) $offset = 0;
$recordsperpage = 5;
$countquery = mysql_query("SELECT COUNT(*) as totalnum from tablename WHERE something = 'something'");
$countdata = mysql_fetch_array($countquery);
$totalrecords = $countdata["totalnum"];

$result = mysql_query("SELECT * from tablename WHERE something = 'something' LIMIT $offset, $recordsperpage");

while ($row = mysql_fetch_array($result)) {
print $row["data"]
}

if ($totalrecords > $recordsperpage) {
$newoffset = $offset + $recordsperpage;
printf('<a href="%s?offset=%s&criteria=%s">more &gt;&gt;</a>', $PHP_SELF, $newoffset, $criteria);
}
if ($offset > $recordsperpage) {
$newoffset = $offset - $recordsperpage;
printf('<a href="%s?offset=%s&criteria=%s">back &lt;&lt;</a>', $PHP_SELF, $newoffset, $criteria);
}


NOTE: YOu must pass all variables used in your query in the links for more and back unless of course you use sessions and register them as persistent variables. I didn't have a place to test this so if it has a bug tell me and I will have another look at it.

[Robo]

I tried it, and I think for the More>> link, the if statement should be if ($totalrecords > $offset)

'cos if I have 30 records, and the $recordsperpage is 5, 30 will always be greater than 5.

And also, you load the page the first time, there's a More>> link, which is good, but when you click on it, the next lot shows up, but that page doesn't have a Back<< link. Just gonna see why it's doing that now.

[freddydoesphp]

Like I said I had no way of testing that sorry guuys Robo you are right about the first if statement and for the back I tested for the current offset to be greater than the number of records per page and on the second page the offset will be 5 which is equal to the number of records per page so it should be $offset >= $recordsperpage

[sammy182]

I am a little confused here:
$countquery = mysql_query("SELECT COUNT(*) as totalnum from tablename WHERE something = 'something'");
$countdata = mysql_fetch_array($countquery);
$totalrecords = $countdata["totalnum"];

I change the tablename and something = 'something' but it gives me a "Supplied argument is not a valid MySQL".

[freddydoesphp]

You need to create your own WHERE clause whatever you use in your normal query with COUNT(*) instead of *

[sammy182]

Ok...I am lost...here..I'll show you what I have..I think I am going to read a few more SQL and PHP tutorials after I get over this problem:

if(!isset($offset)) $offset = 0;
$recordsperpage = 5;
$countquery = mysql_query("SELECT COUNT(*) as totalnum from cruises WHERE fromprice >= $smallprice AND fromprice <= $bigprice");
$countdata = mysql_fetch_array($countquery);
$totalrecords = $countdata["totalnum"];

$result = mysql_query("SELECT * FROM cruise , cruiseline , destination WHERE cruiseline=cruiseline.cruiselineid AND destinations=destination.destinationid AND fromprice >= $smallprice AND fromprice <= $bigprice ORDER BY fromprice LIMIT $offset, $recordsperpage");

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

$title = $row["title"];
$days = $row["days"];
$info = $row["info"];
$fromprice = $row["fromprice"];
$priceinfo = $row["priceinfo"];
$id = $row["id"];
$linename = $row["linename"];
$destination = $row["location"];
$date_start = $row["date_starting"];

echo( "<table width=\"400\" cellspacing=\"0\" cellpadding=\"0\"><tr>".
"<td width=\"200\" bgcolor=\"#CCCC00\">$title</td>".
"<td width=\"200\" bgcolor=\"#CCCC00\">Days: $days</td></tr></table>".
"<table width=\"400\" cellspacing=\"0\" cellpadding=\"0\"><tr>".
"<td width=\"400\" bgcolor=\"#FFFF99\">$info" .
"</td></tr></table>".
"<table width=\"400\" cellspacing=\"0\" cellpadding=\"0\"><tr>".
"<td width=\"200\" bgcolor=\"#CCCC00\">Starting Price: $fromprice</td>".
"<td width=\"200\" bgcolor=\"#CCCC00\">$priceinfo</td></tr></table>".
"<table width=\"400\" cellspacing=\"0\" cellpadding=\"0\"><tr>".
"<td width=\"200\" bgcolor=\"#FFFF99\">Cruiseline: $linename</td>".
"<td width=\"200\" bgcolor=\"#FFFF99\">Destination: $destination</td></tr></table>".
"<table width=\"400\" cellspacing=\"0\" cellpadding=\"0\"><tr><td width=\"400\" bgcolor=\"#CCCC00\">".
"<div align=\"center\">Departs: $date_start</div></td></tr></table><P>");



}

if ($totalrecords > $offset) {
$newoffset = $offset + $recordsperpage;
printf('<a href="%s?offset=%s&criteria=%s">more >></a>', $PHP_SELF, $newoffset, $criteria);
}
if ($offset > $recordsperpage) {
$newoffset = $offset - $recordsperpage;
printf('<a href="%s?offset=%s&criteria=%s">back <<</a>', $PHP_SELF, $newoffset, $criteria);
}

[JohnM]

Try chaning

$countquery = mysql_query("SELECT COUNT(*) as totalnum from cruises WHERE fromprice >= $smallprice AND fromprice <= $bigprice");

to

$countquery = mysql_query("SELECT COUNT(*) as totalnum from cruises WHERE fromprice >= $smallprice AND fromprice <= $bigprice") or die(mysql_error());

That'll print out the SQL error message.