PHP/SQL list order not displaying

Question

A little bit of information

I am currently working on a project where I can display my work as projects.

In my database I have set it up so the number within list tells the PHP where it should be - according to the MYSQL Query.

In my project page I have created pagination so that if there are more than 5 rows, a new project list page is added to the pagination.

(My forward and back pagination is done by images linked)

I use Jquery Ajax POST to call new pages in.

The problem:

I used to set my PHP query to order by name (project name) but I created a new column list so that I could present them in a custom order - according to the digit that is in list

But ever since I changed it from name to list - the page only shows 5 records and no longer gives me my pagination options (when it should)

I know the problem is to do with the 2 queries within the pagination - could anyone tell me how to resolve this.

I have tried making the list column Char, Varchar and INT all have not helped! :(

The Code:

PHP Pagination:

    <?
$get_stuff = mysql_query("SELECT * FROM `projects` WHERE `cat`='$arrc' ORDER BY `list` ASC")or die(mysql_error());
$getid = mysql_num_rows($get_stuff);
$r = mysql_fetch_row($get_stuff);
$numrows = $r[0];
// number of rows to show per page
$rowsperpage = 5;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_POST['pid']) && is_numeric($_POST['pid'])) {
   // cast var as int
   $currentpage = (int) $_POST['pid'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;
// get the info from the db 
$get_stuff2 = mysql_query("SELECT * FROM `projects` WHERE `cat`='$arrc' ORDER BY `list` ASC LIMIT $offset, $rowsperpage")or die(mysql_error());
while($projectz = mysql_fetch_array($get_stuff2)){
?>
<ul class="projex">
<li onClick="project('project','<? echo $projectz['id']; ?>','1','<? echo $currentpage; ?>')" class="onclick"> <? echo $projectz['name']; ?>
</ul>
<?
}
?>

I have not added my links code in as I know that is not the problem.

what does the following line: $numrows = $r[0]; ? I guess it returns an ID which can be less than 5 (the number of items on page in this case) and based on that ceil returns 1 — haynar
– haynar, Commented Aug 6, 2012 at 22:48
I presume so, it is displaying 5 but there are 7 rows (or records) I should be able to see. But the pagination links are not showing - only when I change the order from a name order to list number (In other words text to number) - therefore it is acting like a MAX/LIMIT or otherwise only displaying 5 records. — Andrew
– Andrew, Commented Aug 6, 2012 at 22:58
I think there is something strange about $numrows = $r[0]; too. I think it should be $numrows = mysql_num_rows($get_stuff); — drew010
– drew010, Commented Aug 6, 2012 at 23:02
I still don't understand the meaning of this two lines: $getid = mysql_num_rows($get_stuff); $numrows = $r[0];, I mean the names of variables are confusing or you wanted to assign them in the other way around? like this: $numrows = mysql_num_rows($get_stuff); $getid = $r[0]; — haynar
– haynar, Commented Aug 6, 2012 at 23:03
$numrows = mysql_num_rows($get_stuff); "Fatal error: Unsupported operand types" Also this is old code that I have reused (I found this pagination the easiest) so not all variables have been renamed for only I until now have had to put up with them, I am not asking for you to complain about the name just help me. THE PROBLEM (listening) is that only after I changed the MYSQL order by (from name where it showed the pagination links = 2 pages (or 7 records)) to (list where it now shows 5 records and no pagination links) therefore it must be a problem with the order by but I don't see why — Andrew
– Andrew, Commented Aug 6, 2012 at 23:08

John C · Accepted Answer · 2012-08-07 02:45:47Z

Your problem appears to be here:

<?
$get_stuff = mysql_query("SELECT * FROM `projects` WHERE `cat`='$arrc' ORDER BY `list` ASC")or die(mysql_error());
$getid = mysql_num_rows($get_stuff);
$r = mysql_fetch_row($get_stuff);
$numrows = $r[0];

You put the number of rows into $getid, then ignore that variable and put the first returned column from the results set into $numrows and use that. This means all the pagination calculations are based on the first column returned rather than the number of rows. I would guess that when ordered by name the first column was 7 and when ordered by list the first column is 5 or less.

My suggested fix would be to get the COUNT of rows in the SQL query, unless there is a pressing need for $getid that is not shown in your example:

<?php
$get_stuff = mysql_query("SELECT COUNT(*) FROM `projects` WHERE `cat`='$arrc'")or die(mysql_error());
$r = mysql_fetch_row($get_stuff);
$numrows = $r[0];

Collectives™ on Stack Overflow

PHP/SQL list order not displaying

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related