Page 4 of 6
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 2:40 pm
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
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 2:47 pm
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.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 2:50 pm
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.....??
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 2:54 pm
by Raph
There's the problem. You haven't defined $offset.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:01 pm
by Raph
I assume you don't have error-reporting turned on, so hand me the code and I'll check it.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:02 pm
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> | ";
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 = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}
// print the navigation link
echo $first . $prev . $nav . $next . $last;
mysql_close($sqlconn);
?>
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:13 pm
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:
At the top (after <? but before everything else). If that doesn't display any error messages, try:
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:18 pm
by simonmlewis
Sorry, both versions are not producing anything but what I sent before.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:21 pm
by Raph
Open your php.ini, search for "error_reporting" without the quotes, and make sure that row says:
Save the file, restart apache, and try again.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:25 pm
by simonmlewis
But it's not being hosted on my laptop - it's hosted elsewhere?
Will my php.ini report the error still??
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:27 pm
by simonmlewis
If it does help, I can download everything and run it locally.... which I suspect will help anyway!
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:29 pm
by Raph
upload phpinfo.php to your server, which contains only the following:
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.
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:31 pm
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
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:35 pm
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
Re: Limiting results to first 20 results, then next 20, then...
Posted: Sun Jun 07, 2009 3:44 pm
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.