Pagination Problem

[SkyRanger]

Error:

 

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10,10' at line 179

There is no line 179

 

Code

<?php
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
$query = "select * from iesnews";

$rows = $result->num_rows;

//This is the number of results displayed per page
$page_rows = 10;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets range that we will display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

$query = "SELECT * from iesnews ORDER BY 'posted' DESC $max"; <<<<<Figure error is here but can't figure out
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = $result->num_rows;

?>
 <table>
 <tr>
 <td width="31%">Title</td>
 <td width="34%">Summary</td>
 <td width="15%">Date Posted</td>
 <td width="13%">Extended News</td>
 <td width="7%"></td>
 </tr>
 <?php
for($i = 0; $i < $numofrows; $i++) {
$row = $result->fetch_assoc(); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR>\n";
} else { //if there isn't a remainder we will do the else
echo "<TR>\n";
}
$len = 45;
$data = $row['title'];
$outtitle = (strlen($data) > $len ? substr($data, 0, $len)."..." : $data);
?>

 <td><?php echo $outtitle; ?></td>
 <td>$summaryhere</td>
 <td>$datehere</td>
 <td><center>View News</center></td>
<td>ed/del</td>
</tr>
<?php } ?>
</table>
<?php

echo "<br><br><div align=\"right\" class=\"pagecount\"> --Page $pagenum of $last--</div> <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a class=\"sidelink\" href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a class=\"sidelink\" href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a class=\"sidelink\" href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a class=\"sidelink\" href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
} ?>

Edited February 8, 2013 by SkyRanger

[SkyRanger]

Can anybody see the error, I am not

[Jonline]

Not sure how lines 9 and 11 will work - you aren't actually querying the table at any time. As a side note, you should refrain from using SELECT * - it's bad practice and, as you're only counting the number of rows, it makes more sense to return only one value to reduce the overhead.

[SkyRanger]

Thanks Jonline, I totally over that. I was updating my script from mysql to mysqli and didn't even notice I did that. Works now. Thank you.

 

was suppose to be:

 

$query = "select * from iesnews";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
$rows = $result->num_rows;

[ngreenwood6]

Well this first problem I see is this:

 

$query = "select * from iesnews";
$rows = $result->num_rows;

 

Where is result coming from? It is not defined anywhere in the code you provided and maybe that is your first problem. Also if you have alot of rows or data in that table that query is going to take a long time to complete. I would recommend updating that to query to "SELECT COUNT(*) FROM iesnews" and then getting the result of that as the count (will be much much faster).

 

Now the next problem i see is that your query is doing a range of -10,10. Running that in a query tells it to fail itself because there is no -10. Which is why your query is failing. Which is also stemming from the problem above i believe because it is not actually getting the number of rows. The last part is the query :

 

$query = "SELECT * from iesnews ORDER BY 'posted' DESC $max";

 

I don't think that you meant to have the single quotes around posted. I would remove those unless they are ticks that got converted here somehow. If you fix the issue above with the rows being the incorrect number i believe that your query will start working with the exception off the "posted" issue maybe not sorting properly.