SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: Next 5 Results

  1. #1
    SitePoint Zealot
    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?

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris 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

  3. #3
    SitePoint Member
    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

  4. #4
    SitePoint Evangelist
    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.

  5. #5
    Dumb PHP codin' cat
    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 &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.

    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    midnight coder
    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.

  7. #7
    Dumb PHP codin' cat
    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.

  8. #8
    SitePoint Zealot
    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".

  9. #9
    Dumb PHP codin' cat
    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.

  10. #10
    SitePoint Zealot
    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);
    }

  11. #11
    SitePoint Enthusiast JohnM's Avatar
    Join Date
    Dec 2000
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •