Stuck on a Page Number script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Stuck on a Page Number script

Post by simonmlewis »

Hello.
I have had this code used before, but in a slightly different way.
This really should work, but I am getting the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 20' at line 1
The table names are correct, as is the variable name $search. Can anyone see anything obviously I have cocked up on please, coz my head is getting an ache!!

Code: Select all

<?php 
$search = $_POST['search'];
include "dbconn.php";
echo "<div class='head'>Search for $search</div>";
 
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE (title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
if (mysql_num_rows($result)==0) { echo "<tr><td>Sorry, there are no results for $search.</td></tr>"; }
else {
 
while ($row = mysql_fetch_object($result))
      while ($row = mysql_fetch_object($result)) {
    echo "
<div class='cat_prodlistbox'>
<div class='cat_producttitle'>";
 
                    $position=43; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->title,0,$position);
echo "$postcontent ...</div>
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a></div
            </div>
                    
            ";
            }}          
    mysql_free_result($result);
    echo "</table>";
            
    echo "<div style='clear:both' />";
    $query   = "SELECT COUNT(id) AS numrows FROM products WHERE title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search'";
    
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=search&menu=search&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=search&menu=search&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=search&menu=search&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=search&menu=search&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=search&menu=search&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Stuck on a Page Number script

Post by jackpf »

Looks like you're missing a closing parenthesis in your query.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Stuck on a Page Number script

Post by simonmlewis »

Thanks - where?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Stuck on a Page Number script

Post by jackpf »

Try this:
[sql]SELECT * FROM products WHERE title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage[/sql]
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

RESOLVED Re: Stuck on a Page Number script

Post by simonmlewis »

Thanks -spotted it.

:)

Crikey, I could have spent hours looking and never found it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Stuck on a Page Number script

Post by jackpf »

Lol no problem. You get used to spotting stuff like that after a while.
Post Reply