Using A Dropdown Menu to Order a List Taken from a Database With Pagination

phil5529 · February 22, 2012

I am very new new php (wrote my first PHP script 5 Days ago) and am trying to give myself a crash course but I have hit a pit-stop which is killing me a little!

I hope that title makes sense.... Basically I created PHP script to take data from a database and display in, I then wrote some code to use a drop down menu to order that data. That all worked ok until I tried to utilise some pagination. I can make the pagination work, and I can make the ordering work, but not at the same time! At the moment the code that I have will allow me to order the list and almost paginate it. There are 40 results and I want to display 10 at a time. When not using the ordering code I can paginate it perfectly but when I try to intergrate the two bits of code it will only display the first 10 results and not give me an option to go to the next page to see the rest!

The code:

if (!isset($_GET['start'])) {

$_GET['start'] = 0; }

$per_page = 10; $start = $_GET['start'];

if (!$start)

$start = 0;

$sort = @$_POST['order'];

if (!empty($sort)) {

$get = mysql_query("SELECT bookname, bookauthor, bookpub, bookisbn

FROM booktable

ORDER BY ".mysql_real_escape_string($_POST['order'])." ASC

LIMIT $start, $per_page");

}

else {

$get = mysql_query("SELECT bookname, bookauthor, bookpub, bookisbn

FROM booktable

ORDER BY bookname ASC

LIMIT $start, $per_page");

}

$record_count = mysql_num_rows($get);

?>

<?php

if (isset($_GET['showerror']))

$errorcode = $_GET['showerror'];

else

$errorcode = 0;

?>

wont include all the html rubbish and the ordering menu!

<?php

echo "<table>";

echo "<tr>";

echo "<th>";

echo "</th>";

echo "<th>";

echo "Book Title";

echo "</th>";

echo "<th>";

echo "Book Author";

echo "</th>";

echo "<th>";

echo "Book Publisher";

echo "</th>";

echo "<th>";

echo "Book ISBN";

echo "</th>";

echo "<th>";

echo "</th>";

echo "</tr>";

while ($row = mysql_fetch_assoc($get))

{

// get data

$bookname = $row['bookname'];

$bookauthor = $row['bookauthor'];

$bookpub = $row['bookpub'];

$bookisbn = $row['bookisbn'];

echo "<tr>";

echo "<td>";

echo "<a href='addtolist.php?bookname=".$bookname."&bookauthor=".$bookauthor."&bookpub=".$bookpub."&bookisbn=".$bookisbn."'>Add to basket</a>";

echo "</td>";

echo "<td>";

echo $bookname;

echo "</td>";

echo "<td>";

echo $bookauthor;

echo "</td>";

echo "<td>";

echo $bookpub;

echo "</td>";

echo "<td>";

echo $bookisbn;

echo "</td>";

echo "</tr>";

}

echo "</table>";

$prev = $start - $per_page;

$next = $start + $per_page;

if (!($start<=0))

echo "<a href='products.php?start=$prev'>Prev</a> ";

//set variable for first page number

$i=1;

//show page numbers

for ($x = 0; $x < $record_count; $x = $x + $per_page)

{

if ($start != $x)

echo "<a class='pagin' href='products.php?start=$x'> $i </a>";

else

echo "<a class='pagin' href='products.php?start=$x'><b> $i </b></a>";

$i++;

}

//show next button

if (!($start >= $record_count - $per_page))

echo "<a class='pagin' href='products.php?start=$next'> Next </a>";

?>

Thank you so much for reading!

Psycho · February 22, 2012

You should start with the pagination and then add the sorting ability. The issue you need to overcome is that for each page load you need to determine both the page and the sort order. How you do that is up to you. Typically for the links to change pages you will have an href with a parameter added for the page to load. But, if that page loads and you don't know the sort order, well it fails. You could add the sort order to each link as well. Or, alternatively, you could add it to a session value. I would use the latter.

Your code was kind of messy, so here is a quick rewrite. I put into what I feel is a more logical structure and changed some variable names to be more descriptive. Since I don't have your DB this is not tested, so there will likely be some typos. But, I think the comments should help explain what the code is supposed to be doing and you can fix them

<?php

session_start();

//Config variables
$records_per_page = 10;

//If user set the sort order, save to session var - else set default
if($_POST['order'])
{
    $_SESSION['order'] = trim($_POST['order']);
}
elseif(!isset($_SESSION['order']))
{
    $_SESSION['order'] = 'bookname';
}

//Determine the total records and pages
$query = "SELECT COUNT(bookname)
          FROM booktable";
$result = mysql_query($query) or die mysql_error();

$total_records = mysql_result($result, 0);
$total_pages = ceil($total_records / $records_per_page);

//Set the page to load
$page = (isset($_GET['page'])) ? (int) $_GET['page'] : 1;
if($page<1 || $page>$total_pages)
{
    $page = 1;
}

//Create limit var for query
$limit_start = ($page-1) * $records_per_page;

//Create and run query for records on current page
$query = "SELECT bookname, bookauthor, bookpub, bookisbn
          FROM booktable
          ORDER BY ".mysql_real_escape_string($_SESSION['order'])." ASC
          LIMIT $limit_start, $records_per_page"
$result = mysql_query($query) or die mysql_error();

//Prepare output
$recordsHTML = '';
while ($row = mysql_fetch_assoc($get))
{
    $urlParams = "bookname={$row['bookname']}&bookauthor={$row['bookauthor']}&bookpub={$row['bookpub']}&bookisbn={$row['bookisbn']}";
    $recordsHTML .= "<tr>\n";
    $recordsHTML .= "  <td><a href='addtolist.php?{$urlParams}'>Add to basket</a></td>\n";
    $recordsHTML .= "  <td>$bookname</td>\n";
    $recordsHTML .= "  <td>$bookauthor</td>\n";
    $recordsHTML .= "  <td>$bookpub</td>\n";
    $recordsHTML .= "  <td>$bookisbn</td>\n";
    $recordsHTML .= "</tr>\n";
}

//Create pagination links
$navLinks = '';
//Prev page
if($page > 1)
{
    $prevPage = $page - 1;
    $navLinks .= "<a href='products.php?page=$prevPage'>Prev</a> ";
}
else
{
    $navLinks .= "Prev ";
}
//Individual pages
for($p=1; $p<=$total_pages; $p++)
{
    $pageNo = ($p == $page) ? "<b>{$p}</b>" : $p;
    $navLinks .= "<a class='pagin' href='products.php?page=$p'>$pageNo</a> ";
}
//next page
if($page < $total_pages)
{
    $nextPage = $page + 1;
    $navLinks .= "<a href='products.php?page=$nextPage'>Next</a>";
}
else
{
    $navLinks .= "Next";
}


//$record_count = mysql_num_rows($get);


//if (isset($_GET['showerror']))
//$errorcode = $_GET['showerror'];
//else
//$errorcode = 0;
?>
<html>
<head></head>

<body>
wont include all the html rubbish and the ordering menu!
<div id="mid">

<table>
  <tr>
    <th></th>
    <th>Book Title</th>
    <th>Book Author</th>
    <th>Book Publisher</th>
    <th>Book ISBN</th>
    <th></th>
  </tr>
  <?php echo $recordsHTML; ?>
</table>

<?php echo $navLinks; ?>

</body>
</html>

EDIT: you didn't include the part of the code with the select list for determining the sort order. I would update that code to auto-populate with the currently selected sort order.

phil5529 · February 22, 2012

Thank you very much! I am reading and reading, a few parts dont make sense to me (as in i dont understand them) but I am looking them up as we speak... I have sorted out other errors however I am still getting an error but I cant see for the life of me what is wrong with it, I recieve the error:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, string given in C:\xampp\htdocs\blurb2\products.php on line 55

Line 55 is:

Line 53: //Prepare output

Line 54: $recordsHTML = '';

Line 55: while ($row = mysql_fetch_assoc($query))

Line 56: {

Line 57: $urlParams = "bookname={$row['bookname']}&bookauthor={$row['bookauthor']}&bookpub={$row['bookpub']}&bookisbn={$row['bookisbn']}";

Line 58: $recordsHTML .= "<tr>\n";

Line 59: $recordsHTML .= " <td><a href='addtolist.php?{$urlParams}'>Add to basket</a></td>\n";

Line 60: $recordsHTML .= " <td>$bookname</td>\n";

Line 61: $recordsHTML .= " <td>$bookauthor</td>\n";

Line 62: $recordsHTML .= " <td>$bookpub</td>\n";

Line 63: $recordsHTML .= " <td>$bookisbn</td>\n";

Line 64: $recordsHTML .= "</tr>\n";

Line 65: }

I really cant explain how grateful I am for your wisdom... thank you!

[=

Psycho · February 23, 2012

Ah, yes, line 55 should be

while ($row = mysql_fetch_assoc($result)) // <== use $result, not $query

If you look above, I create the query as a string variable ($query) then run it and assign the results to $result

//Create and run query for records on current page
$query = "SELECT bookname, bookauthor, bookpub, bookisbn
          FROM booktable
          ORDER BY ".mysql_real_escape_string($_SESSION['order'])." ASC
          LIMIT $limit_start, $records_per_page"
$result = mysql_query($query) or die mysql_error();

This is a VERY good process to follow instead of writing your queries directly in the mysql_query() function like this:

$result = mysql_query("SELECT * FROM table")

Many times when you have errors running queries it is due to some dynamic data in the query and it is difficult to debug without seeing the query with those dynamic values. So, using the method I provided above you could echo the $query variable to the page if you need to debug an issue.

Sign In

Using A Dropdown Menu to Order a List Taken from a Database With Pagination

Recommended Posts

phil5529

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

phil5529

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information