CODEWITHSUNDEEP
phpmysql
Andrew
Andrew

Reputation: 359

PHP/SQL list order not displaying

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.

Upvotes: 0

Views: 128

Answers (1)

John C
John C

Reputation: 8415

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];

Upvotes: 1

Related Questions