Help with Pagination

[rachelkaz]

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

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

[m_tt]

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.

[m_tt]

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:

$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:

$lists['model']
$lists['colour']
$lists['registration']

You could also add code to sort() the arrays as needed.

[mysamsung]

Find this link, http://net.tutsplus.com/tutorials/php/how-to-paginate-data-with-php/ ,may be it help you.

[rachelkaz]

Thank you very much for both tips!

[Ashley_Sheridan]

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.

[Derokorian]

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.

[Ashley_Sheridan]

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!

[Weedpacket]

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.