Help with Pagination
Results 1 to 9 of 9

Thread: Help with Pagination

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    3

    Help with Pagination

    Hello,

    I was hoping someone could help me out by adding pagination to my script.

    I want 5 results to be displayed on each page.

    PHP Code:
    <?php

    // carsDatabaseSearchResultsWithMySQLi.php

    $title "Cheap Heaps Search Results";
    $LIB_PATH='../../Scripts/Server/';

    include(
    $LIB_PATH."mysql_cars.php");
    $connection mysqli_connect($hostName$userName$password);
    mysqli_select_db($connection$dbName);
       

    $modelQuery "SELECT DISTINCT model FROM joinedresults ORDER BY model";
    $modelResult mysqli_query($connection$modelQuery);
    $modelList = array();
    $modelCount 0;
    while (
    $model mysqli_fetch_row($modelResult))
    {
        
    $modelList[$modelCount] = $model[0];
        
    $modelCount++;
    }
       
    $colourQuery "SELECT DISTINCT colour FROM joinedresults ORDER BY colour";
    $colourResult mysqli_query($connection$colourQuery);
    $colourList = array();
    $colourCount 0;
    while (
    $colour mysqli_fetch_row($colourResult))
    {
        
    $colourList[$colourCount] = $colour[0];
        
    $colourCount++;
    }

    $registrationQuery "SELECT DISTINCT registration FROM joinedresults ORDER BY registration";
    $registrationResult mysqli_query($connection$registrationQuery);
    $registrationList = array();
    $registrationCount 0;
    while (
    $registration mysqli_fetch_row($registrationResult))
    {
        
    $registrationList[$registrationCount] = $registration[0];
        
    $registrationCount++;
    }

    $motQuery "SELECT DISTINCT mot FROM joinedresults ORDER BY mot";
    $motResult mysqli_query($connection$motQuery);
    $motList = array();
    $motCount 0;
    while (
    $mot mysqli_fetch_row($motResult))
    {
        
    $motList[$motCount] = $mot[0];
        
    $motCount++;
    }

    $makeQuery "SELECT DISTINCT make FROM joinedresults ORDER BY make";
    $makeResult mysqli_query($connection$makeQuery);
    $makeList = array();
    $makeCount 0;
    while (
    $make mysqli_fetch_row($makeResult))
    {
        
    $makeList[$makeCount] = $make[0];
        
    $makeCount++;
    }

    $origcountryQuery "SELECT DISTINCT origcountry FROM joinedresults ORDER BY origcountry";
    $origcountryResult mysqli_query($connection$origcountryQuery);
    $origcountryList = array();
    $origcountryCount 0;
    while (
    $origcountry mysqli_fetch_row($origcountryResult))
    {
        
    $origcountryList[$origcountryCount] = $origcountry[0];
        
    $origcountryCount++;
    }

       
    // function from Williams and Lane, Chapter 5:
    function clean($input$maxlength)
    {
        
    $input substr($input0$maxlength);
        
    $input EscapeShellCmd($input);
        return (
    $input);
    }
           
    $ordering "";
    $maxPrice "";
    $colour "";
    $model "";
    $registration "";
    $mot "";
    $make "";
    $origcountry "";
    if (
    array_key_exists("ordering",$_GET))
        
    $ordering clean($_GET["ordering"],12);
    if (
    array_key_exists("maxPrice",$_GET))
        
    $maxPrice clean(trim($_GET["maxPrice"]),15);
    if (
    array_key_exists("colour",$_GET))
        
    $colour clean($_GET["colour"],20);
    if (
    array_key_exists("model",$_GET))
        
    $model clean($_GET["model"],30);
    if (
    array_key_exists("registration",$_GET))
        
    $registration clean($_GET["registration"],30);
    if (
    array_key_exists("mot",$_GET))
        
    $mot clean($_GET["mot"],3);
    if (
    array_key_exists("make",$_GET))
        
    $make clean($_GET["make"],30);
    if (
    array_key_exists("origcountry",$_GET))
        
    $origcountry clean($_GET["origcountry"],30);

    function 
    validInput($ordering$maxPrice$colour$model$registration$mot$make$origcountry$colourList$modelList$registrationList$motList$makeList$origcountryList)
    {
        
    $numberPattern "^\s*[1-9][0-9][0-9][0-9][0-9]{0,2}\s*$";
        if (!(
    $ordering == "model" || $ordering == "colour" || $ordering == "make" || $ordering == "price"))
            return 
    false;
        if (!
    ereg($numberPattern,$maxPrice) && !ereg("^\s*$",$maxPrice))
            return 
    false;
        if (!(
    $colour == "all" || in_array($colour,$colourList)))
            return 
    false;
        if (!(
    $model == "all" || in_array($model,$modelList)))
            return 
    false;
        if (!(
    $registration == "all" || in_array($registration,$registrationList)))
            return 
    false;
        if (!(
    $mot == "all" || in_array($mot,$motList)))
            return 
    false;
        if (!(
    $make == "all" || in_array($make,$makeList)))
            return 
    false;
        if (!(
    $origcountry == "all" || in_array($origcountry,$origcountryList)))
            return 
    false;
        return 
    true;
    }
       
    if (!
    validInput($ordering,$maxPrice,$colour,$model,$registration,$mot,$make,$origcountry,$colourList,$modelList,$registrationList,$motList,$makeList,$origcountryList))
    {
        echo 
    "<body><center><h1>\nInvalid input!\n";
        echo 
    "</h1></center></body></html>";
        exit;
    }
       
    $sourceForTable "<table border=\"1\" cellpadding=\"1\" align=\"center\">\n";
    $sourceForTable .= "<tr><td><b>Model</b></td>";
    $sourceForTable .= "<td><b>Colour</b></td>";
    $sourceForTable .= "<td><b>Registration</b></td>";
    $sourceForTable .= "<td><b>Price</b></td>";
    $sourceForTable .= "<td><b>MOT</b></td>";
    $sourceForTable .= "<td><b>Make</b></td>";
    $sourceForTable .= "<td><b>OrigCountry</b></td>";
    $sourceForTable .= "</tr>\n";
    $carsFound false;
       
       
    // query construction:
       

    $query "SELECT model, colour, registration, price, mot, make, origcountry FROM joinedresults ";
       
    $nextSQLconnective "WHERE";
    if (!
    ereg("^\s*$",$maxPrice))
    {
        
    $query .= $nextSQLconnective." price <= ".$maxPrice." ";
        
    $nextSQLconnective "AND";
    }
    if (
    $colour != "all")
    {
        
    $query .= $nextSQLconnective." colour = \"".$colour."\" ";
        
    $nextSQLconnective "AND";
    }
    if (
    $model != "all")
    {
        
    $query .= $nextSQLconnective." model = \"".$model."\" ";
        
    $nextSQLconnective "AND";
    }
    if (
    $registration != "all")
    {
        
    $query .= $nextSQLconnective." registration = \"".$registration."\" ";
        
    $nextSQLconnective "AND";
    }
    if (
    $mot != "all")
    {
        
    $query .= $nextSQLconnective." mot = \"".$mot."\" ";
        
    $nextSQLconnective "AND";
    }
    if (
    $make != "all")
    {
        
    $query .= $nextSQLconnective." make = \"".$make."\" ";
        
    $nextSQLconnective "AND";
    }
    if (
    $origcountry != "all")
        
    $query .= $nextSQLconnective." origcountry = \"".$origcountry."\" ";
       

    $query .= "ORDER BY ".$ordering;

    //construction over!
       
       
    $carsFound false;
    $result mysqli_query ($connection$query);
    while (
    $row mysqli_fetch_row($result))
    {
        
    $carsFound true;
        
    $sourceForTable .= "<tr>";
        for (
    $i 0$i mysqli_num_fields($result); $i++)
            
    $sourceForTable .= "<td>$row[$i]</td>";
        
    $sourceForTable .= "</tr>\n";
    }
    mysqli_close($connection);

       
    $sourceForTable .= "</table>\n\n";

    require(
    $LIB_PATH."simpleXHTMLtransitionalHead.php");
    echo 
    "<body>\n<center>\n<h1>$title</h1>\n";
    if (
    $carsFound)
        echo 
    $sourceForTable;
    else
        echo 
    "<h3>Sorry - we do not sell any cars matching the given criteria.</h3>\n";
    echo 
    "\n<p><small><br><br><tt>[ ".$query." ]</tt><br><br></small></p>\n\n";
    echo 
    "</center>\n</body>\n</html>\n";
    ?>

  2. #2
    Syntax Error
    Join Date
    Feb 2003
    Location
    San Diego, CA
    Posts
    443
    How are you going to learn if someone else does your code? Use the search, I'm sure there are lots of threads on pagination.

  3. #3
    Syntax Error
    Join Date
    Feb 2003
    Location
    San Diego, CA
    Posts
    443
    I will give you some other tips however. I believe the mysqli_* functions have been deprecated so I recommend using the mysqli and mysqli_result classes instead.

    It looks like the 6 array building queries can be reduced to 1 query and 1 array. I would have probably done something like this:

    PHP Code:
    $lists = array();
    $res $con->query("SELECT `model`, `colour`, `registration`, `mot`, `make`, `origcountry` FROM `joinedresults`");
    if (
    $res->num_rows 0) {
        while(
    $row $res->fetch_assoc()) {
            foreach(
    $row as $key => $value) {
                
    $lists[$key][] = $value// Add each $value under $key
            
    }
        }

    Then you have access to each of the arrays like so:

    PHP Code:
    $lists['model']
    $lists['colour']
    $lists['registration'
    You could also add code to sort() the arrays as needed.

  4. #4
    Junior Member
    Join Date
    Apr 2010
    Posts
    13
    Find this link, http://net.tutsplus.com/tutorials/ph...data-with-php/ ,may be it help you.

  5. #5
    Junior Member
    Join Date
    Apr 2012
    Posts
    3
    Thank you very much for both tips!

  6. #6
    Senior Member
    Join Date
    Aug 2008
    Location
    London, UK
    Posts
    753
    I believe it's not the mysqli_* functions, just the mysql_* functions, although I can't actually find this information just now on php.net to verify it.
    Ashley Sheridan
    www.ashleysheridan.co.uk

  7. #7
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,767
    Ashley I think you are looking for http://php.net/manual/en/mysqlinfo.api.choosing.php
    It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  8. #8
    Senior Member
    Join Date
    Aug 2008
    Location
    London, UK
    Posts
    753
    Yes, I was just looking at the main manual pages for functions and saw nothing there. Seen quite a bit of debate on the whole mysql_* vs mysqli_* functions recently, that link will certainly be useful!
    Ashley Sheridan
    www.ashleysheridan.co.uk

  9. #9
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,848
    Yeah, well; the debate can be settled pretty easily: MySQL is being deprecated .

    It's in the same situation now that PHP4 was in during 2004-2008.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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