Page 1 of 1

Stuck on a Page Number script

Posted: Fri Aug 21, 2009 6:34 am
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);
?>

Re: Stuck on a Page Number script

Posted: Fri Aug 21, 2009 6:55 am
by jackpf
Looks like you're missing a closing parenthesis in your query.

Re: Stuck on a Page Number script

Posted: Fri Aug 21, 2009 7:04 am
by simonmlewis
Thanks - where?

Re: Stuck on a Page Number script

Posted: Fri Aug 21, 2009 7:49 am
by jackpf
Try this:
[sql]SELECT * FROM products WHERE title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' LIMIT $offset, $rowsPerPage[/sql]

RESOLVED Re: Stuck on a Page Number script

Posted: Fri Aug 21, 2009 4:28 pm
by simonmlewis
Thanks -spotted it.

:)

Crikey, I could have spent hours looking and never found it.

Re: Stuck on a Page Number script

Posted: Sat Aug 22, 2009 6:25 am
by jackpf
Lol no problem. You get used to spotting stuff like that after a while.