[RESOLVED] pagination script problems

[chrisdee]

Hello. Here I am again:o

Im having problems finding out why my pagination script won't work properly. Im listing out the contents of a mysql table wich I also have dynamic sorting for. This works. Part of the pagination works, but not the part wich should list out all the pages as numeric (1, 2, 3, 4, 5, etc) links below my table.

I would greatly apreciate if someone would take a look at my script and comment on what could be wrong.

Here is my "Frankenstein" script:glare:

<?php echo "<a href=./>Home</a><br>";
// connect to db
$connect = mysqli_connect('localhost','user','pass','db');

// set the default sort
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'dato';

// define sort order
$sort_order = 'desc';

if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// html table
echo "<table border='1'>";

// query mysql table column keys
$query_keys = mysqli_query($connect,("SELECT * FROM table"));
$fetch_keys = mysqli_fetch_array($query_keys, MYSQLI_ASSOC);

// print out table columns keys
echo "<tr>";
foreach(array_keys($fetch_keys) as $key) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "</tr>";
// free query keys
mysqli_free_result($query_keys);


$records_per_page = 4;
$total = mysqli_query($connect,("SELECT COUNT(*) FROM table"),0);
$page_count = ceil($total / $records_per_page);

$page = 1;
if (isset($_GET['page']) && $_GET['page'] >= 1 && $_GET['page'] <= $page_count) {
 $page = (int)$_GET['page'];
}

$skip = ($page - 1) * $records_per_page;




// query mysql table content
$query_content    = mysqli_query($connect,("SELECT * FROM table ORDER BY $sort $sort_order LIMIT $records_per_page"));

// print out table contents
while($row = mysqli_fetch_array($query_content, MYSQLI_ASSOC)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "</tr>";
}
// free query content
mysqli_free_result($query_content);

echo "</table>";



for ($i = 1; $i <= $page_count; ++$i) {
 echo "<a href='?page=" . $i . "'>" . $i . "</a> ";
}



// close connection
mysqli_close($connect);
?>

[Derokorian]

$total = mysqli_query($connect,("SELECT COUNT(*) FROM table"),0); 
$page_count = ceil($total / $records_per_page);

This is the problem, $total isn't a number but a result resource... Try this:

$total = mysqli_query($connect,("SELECT COUNT(*) cnt FROM table"),0); 
$total = mysqli_fetch_assoc($total); // Get the row returned
$total = $total['cnt']; // Get the value of count in that row
$page_count = ceil($total / $records_per_page);

[chrisdee]

Thanks for your answer. When I try what you suggest I get a error message :

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 49

[johanafm]

Because your query failed. This question has been answered loads of times allready and the same process applies every time.

[chrisdee]

johanafm;10997636 wrote:

Because your query failed. This question has been answered loads of times allready and the same process applies every time.

Sorry for asking but what is the process that applies every time ?

[johanafm]

The process which have been described in all the other threads asking the same question about "expects parameter 1 to be mysqli_result, boolean given" (or "expects parameter 1 to be mysql_result, boolean given")

[bradgrafelman]

There's also a link in my signature that describes the 'process' in full detail. Essentially, you should be checking to see if your SQL query fails by examining the returned value from [man]mysqli_query/man and, if so, either output or log the error message returned from the MySQL server.

[chrisdee]

It took me well over 3 weeks but I finally solved the problems in my pagination/sort script. After giving my variables more logical names (atleast to me) It was easier to se where each part is supposed to go.

Here is the full working script if anybody is interessted, and thanks for all help🙂 :

<?php echo "<a href=./>Home</a><br>";
// connect to db
$connect = mysqli_connect('localhost','username','password','databasename');

// set the default sort
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id';

// define sort order
$sort_order = 'desc';

if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// html table
echo "<table border='1'>";

// query mysql table column keys
$query_keys = mysqli_query($connect,("SELECT * FROM table"));
$fetch_keys = mysqli_fetch_array($query_keys, MYSQLI_ASSOC);

// print out table columns keys
echo "<tr>";
foreach(array_keys($fetch_keys) as $key) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "</tr>";
// free query keys
mysqli_free_result($query_keys);

// pagination
$rows_per_page    = 5;
$query_rows    = mysqli_query($connect, "SELECT * FROM table");
$num_rows    = mysqli_num_rows($query_rows);
$num_of_pages    = ceil($num_rows / $rows_per_page);
$current_page    = 1;

if (isset($_GET['current_page']) && $_GET['current_page'] >= 1 && $_GET['current_page'] <= $num_of_pages) {
 $current_page = (int)$_GET['current_page'];
}

$offset        = ($current_page -1) * $rows_per_page;
mysqli_free_result($query_rows);

// query mysql table content
$query_content    = mysqli_query($connect,("SELECT * FROM table ORDER BY $sort $sort_order LIMIT $offset, $rows_per_page"));

// print out table contents
while($row = mysqli_fetch_array($query_content, MYSQLI_ASSOC)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "</tr>";
}
// free query content
mysqli_free_result($query_content);

echo "</table>";

// echo pagination
for ($i = 1; $i <= $num_of_pages; ++$i) {
 if($i==$current_page) {
  echo "<a href='?current_page=$i'><font color=red>$i</font></a> ";
 }
 else {
  echo "<a href='?current_page=$i'>$i</a> ";
 }
} 

// close connection
mysqli_close($connect);
?>

[Derokorian]

One comment:

if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 

This seems backwards, if the get sort is set to ASC do you really you want to instead sort it DESC and vice versa?

[chrisdee]

Derokorian;10998011 wrote:

One comment:

if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 

This seems backwards, if the get sort is set to ASC do you really you want to instead sort it DESC and vice versa?

Thanks for your comment. If I understand you correctly you are asking why I set desc and default sort ?

In that case its because I initially want to sort after date, with the newest date showing at the top. I just have not changed id to date in this example.

[Derokorian]

That's not what I'm asking, what I'm asking is why do you set $sort_order to desc when the query string has it set to ASC. That means if I go to page.php?sort_by=ASC you are going to give me descending results instead. Should it not be

if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'asc'; 
} else { 
  $sort_order = 'desc'; 
} 

Where sort_order is set to the same as the query string?

[bradgrafelman]

Additionally, note that all of this:

// set the default sort 
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id'; 

// define sort order 
$sort_order = 'desc'; 

if(isset($_GET['sort_by'])) { 
if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 
} 

could be more simply written as this:

$sort_order = (isset($_GET['sort_by']) && $_GET['sort_by'] == 'asc') ? 'asc' : 'desc';

Also, don't use 'SELECT ' - instead, only SELECT the columns from which you actually need data. To get a row count, you should probably use a 'SELECT COUNT()' statement instead for efficiency.

[Derokorian]

Also this would block can be shortened:

// define sort order 
$sort_order = 'desc'; 

if(isset($_GET['sort_by'])) { 
if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 
} 

To:

$sort_order = isset($_GET['sort_by']) && $_GET['sort_by'] == 'asc' ? 'asc' : 'desc';

More on ternary at http://php.net/manual/en/language.operators.comparison.php#language.operators.comparison.ternary

Edit: Lost to BG again.

[chrisdee]

bradgrafelman;10998014 wrote:

Additionally, note that all of this:

// set the default sort 
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id'; 

// define sort order 
$sort_order = 'desc'; 

if(isset($_GET['sort_by'])) { 
if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 
} 

could be more simply written as this:

$sort_order = (isset($_GET['sort_by']) && $_GET['sort_by'] == 'asc') ? 'asc' : 'desc';

Thanks for your suggestion, but that breakes my script and gives me the following error messages :

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 51

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 59

[chrisdee]

Derokorian;10998016 wrote:

Also this would block can be shortened:

// define sort order 
$sort_order = 'desc'; 

if(isset($_GET['sort_by'])) { 
if($_GET['sort_by'] == 'asc') { 
  $sort_order = 'desc'; 
} else { 
  $sort_order = 'asc'; 
} 
} 

To:

$sort_order = isset($_GET['sort_by']) && $_GET['sort_by'] == 'asc' ? 'asc' : 'desc';

More on ternary at http://php.net/manual/en/language.operators.comparison.php#language.operators.comparison.ternary

Edit: Lost to BG again.

Thanks for your suggestion too, but this only sorts the table one way, and not both ways as I want.

[bradgrafelman]

chrisdee;10998121 wrote:

Thanks for your suggestion, but that breakes my script and gives me the following error messages :

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 51

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /var/www/pdb/index.php on line 59

Since my code has nothing to do with any MySQLi functions, I'd say it's not my suggestion that broke your script - it was you who broke your script. 😉