SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
Thread: Next 5 Results
Hybrid View
-
Dec 22, 2000, 13:58 #1
- Join Date
- Oct 2000
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Dec 22, 2000, 14:09 #2
- Join Date
- Aug 1999
- Location
- East Lansing, MI USA
- Posts
- 12,937
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
http://www.webmasterbase.com/article...d=228&pid=1039
Use Limits.
ChrisChris Beasley - I publish content and ecommerce sites.
Featured Article: Free Comprehensive SEO Guide
My Guide to Building a Successful Website
My Blog|My Webmaster Forums
-
Dec 22, 2000, 14:17 #3
- Join Date
- Dec 2000
- Location
- Poland
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Dec 22, 2000, 14:29 #4
- Join Date
- Jul 2000
- Location
- Warwickshire, England
- Posts
- 557
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 23, 2000, 12:20 #5
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 >></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);
}
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.
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Dec 23, 2000, 18:33 #6
- Join Date
- Dec 2000
- Location
- The flat edge of the world
- Posts
- 838
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Dec 24, 2000, 12:02 #7
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Dec 25, 2000, 15:50 #8
- Join Date
- Oct 2000
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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".
-
Dec 25, 2000, 17:25 #9
- Join Date
- Aug 2000
- Location
- San Diego, CA
- Posts
- 5,460
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You need to create your own WHERE clause whatever you use in your normal query with COUNT(*) instead of *
Please don't PM me with questions.
Use the forums, that is what they are here for.
-
Dec 25, 2000, 18:01 #10
- Join Date
- Oct 2000
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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);
}
-
Dec 26, 2000, 11:47 #11
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.- John M
Bookmarks