Board index   FAQ   Search  
Register  Login
Board index PHP PHP Scripts

Help with Pagination

Links for php scripts

Moderators: macek, egami, gesf

Help with Pagination

Postby rkaz421 » Tue Apr 10, 2012 1:56 pm

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.

Code: Select all
<?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($input, 0, $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";
?>
rkaz421
New php-forum User
New php-forum User
 
Posts: 1
Joined: Tue Apr 10, 2012 1:51 pm

Re: Help with Pagination

Postby FishBird » Thu Apr 12, 2012 9:19 am

Allright, a very dirty solution is this:

Code: Select all
$page = (int) $_GET['page'];
$stopBy = $page*5;
$startBy = $stopBy - 5;
$startBy<0? $startBy = 0:"";
$i = 0;
$query = "SELECT model, colour, registration, price, mot, make, origcountry FROM joinedresults LIMIT ".$startBy.",5 ";

$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);





Did not test it,but should work. Have fun :-)
FishBird
New php-forum User
New php-forum User
 
Posts: 21
Joined: Thu Apr 12, 2012 8:05 am


Return to PHP Scripts

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.