Limiting results to first 20 results, then next 20, then...

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

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

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Exactly as before:

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg' AND stockstatus= 'in stock' ORDER BY title LIMIT $offset, $rowsPerPage") or die (mysql_error());
.... though with two additional bits:
stockstatus, and ORDER BY
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

What does your query look like? Not in the script, the actual query that is sent to the DB.
As in, echo the query and copy the output here.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

UPDATE:

I missed the offset code out, but with it, the result is this:
SELECT * FROM products WHERE category = 'Accessories' AND stockstatus= 'in stock' ORDER BY title LIMIT 0, 20
Without the query outputted, I get blank.

-----------------------------

Oh now that's interesting....
SELECT * FROM products WHERE category = 'Accessories' AND stockstatus= 'in stock' ORDER BY title LIMIT , 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 '' at line 1
No offset.....??
Last edited by simonmlewis on Sun Jun 07, 2009 2:54 pm, edited 1 time in total.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

There's the problem. You haven't defined $offset.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

I assume you don't have error-reporting turned on, so hand me the code and I'll check it.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

I'm not exactly sure how to turn it on.
But here... thanks. :)

Code: Select all

<?php 
$mycateg = $_REQUEST['category'];
echo "<div class='categorytitle'>$mycateg</div>";
include "dbconn.php";
 
$result = mysql_query ("SELECT * FROM products WHERE cat = 'yes' AND category = '$mycateg'");
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='categorypromotionbox'>
            <div class='producttitle'>$row->title";
 
 
$cookieuser = $_COOKIE['user'];
if (isset($_COOKIE['user'])) { echo "
<form method='post' action='index.php?page=a_productedit&menu=a_productshelp'>
<input type='hidden' name='id' value='$row->id'>
<input type='submit' value='Edit' class='submittext'>
</form>";
} 
 
echo "</div>
            <div class='productimage'>
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id'><img src='images/productphotos/$row->photoprimary' border='0' title='$row->title' ></a></div>
            <div class='productdescription'>";
            
            $position=320; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->description,0,$position);
echo "$postcontent...<i>continued</i>...</div>
            <div class='productbuynow'><font color='#ff0000'>ONLY £$row->price</font>&nbsp;&nbsp;|&nbsp;&nbsp;";
            
            if ($row->stockstatus == "in stock") { echo "$row->cartbutton"; }
            elseif ($row->stockstatus == "sold out") { echo "Sorry, sold out."; }
            
            echo "</font></div>
            ";
            }
    mysql_free_result($result);
    echo "</div><img src='images/header_longblank.gif' style='margin-top:10px'/>";
    
      // how many rows to show per page
$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 "SELECT * FROM products WHERE category = '$mycateg' AND stockstatus= 'in stock' ORDER BY title LIMIT $offset, $rowsPerPage";
    
$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg' AND stockstatus= 'in stock' ORDER BY title LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>";
            
                        $cookieuser = $_COOKIE['user'];
if (isset($_COOKIE['user'])) { echo "<div style='width:20px; float:right'>
<form method='post' action='index.php?page=a_productedit&menu=a_productshelp'>
<input type='hidden' name='id' value='$row->id'>
<input type='submit' value='Edit' class='submittext'>
</form></div>";
}
            
            echo $row['title'] . "</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] . " title='Look at the ". $row['title'] ."><img src='images/productphotos/" . $row['photoprimary'] ."' border='0' /></a><br/>";
        
            
            echo "£". $row['price'] ."</div>";
            }
    
    $query   = "SELECT COUNT(id) AS numrows FROM products";
$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=categ&menu=categ&category=$mycateg&pagenum=$page\">$pagenum</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=categ&menu=categ&category=$mycateg&pagenum=$page\">[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=1\">[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=categ&menu=categ&category=$mycateg&pagenum=$page\">[Next]</a> ";
 
   $last = " <a href=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=$maxPage\">[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 $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

No syntax errors, which mean you'll have to turn on error-reporting in order for us to figure out the problem.

Put:

Code: Select all

error_reporting(E_ALL);
At the top (after <? but before everything else). If that doesn't display any error messages, try:

Code: Select all

error_reporting(-1);
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Sorry, both versions are not producing anything but what I sent before.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

Open your php.ini, search for "error_reporting" without the quotes, and make sure that row says:

Code: Select all

error_reporting  =  E_STRICT
Save the file, restart apache, and try again.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

But it's not being hosted on my laptop - it's hosted elsewhere?

Will my php.ini report the error still??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

If it does help, I can download everything and run it locally.... which I suspect will help anyway!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

upload phpinfo.php to your server, which contains only the following:

Code: Select all

<?php
phpinfo();
?>
And search for:

display_errors
error_log
error_reporting

And write what the values for those are. If error_log is set, see if you can find it via your ftp-client, download it, and browse it. error_reporting displays what level of errors are being reported.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Sorry, I cannot do that as it is a third party server.

However I now have it locally, and have this error:
Fatal error: Cannot use object of type stdClass as array in C:\xampp\phpmyadmin\company\includes\categ.inc on line 76
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

Ah, lovely thing, computers.

Code: Select all

<a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] . " title='Look at the ". $row['title'] ."><img src='images/productphotos/" . $row['photoprimary'] ."' border='0' /></a><br/>";
You'll have to change the $row thingys from $row['id'] to $row->id, and do the same with ->title and ->photoprimary
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Ok - brilliant, that problem is solved. It is ALL now working again, but I do have two queries:

1) why a second page I went to was empty, even though the 'next' was linked.
2) (stated before) why the page numbers are not shown in full, 2, 3, 4, 5.....

Only the next page number is shown.

But thanks so much for all your help and support so far.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply