Pagination Explanation

[rkaz421]

Hello,

 

I was hoping someone could help me out. I want to paginate my results page, but am having trouble because I don't want to use data  directly from my database but from my query results. Any help would be great!

 

<?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";

?>