Hi David
Not sure if this been asked before (if so please point me to page), is it possible to add pagination to voucher results.
I've been using the following for the page numbers
{code saved}
but can't seem to get the code right in vouchers.php
<?php
$now = time();
$sql = "SELECT * FROM `".$config_databaseTablePrefix."vouchers` WHERE ( (valid_from < '".$now."' AND valid_to = '0') OR (valid_from <= '".$now."' AND valid_to > '".$now."') ) ORDER BY merchant";
$offset = ($page-1) * $resultsPerPage;
$sql .= " LIMIT ".$offset.",".$resultsPerPage;
$sqlResultCount = "SELECT FOUND_ROWS() as resultcount";
database_querySelect($sqlResultCount,$rowsResultCount);
$resultCount = $rowsResultCount[0]["resultcount"];
if (isset($_GET["merchant"]))
{
$sql = str_replace("WHERE","WHERE merchant='".database_safe($_GET["merchant"])."' AND ",$sql);
}Chris
Hi David
For some reason I still can't get this to function, i've followed above and rechecked but no luck. This is what my vouchers.php script now looks like:
{code saved}
Chris
Hello Chris,
Please could you email me your complete script (including the pagination code) and I'll check it all out for you on my test server...
Cheers,
David.
--
PriceTapestry.com
Hi David
Thank you for looking at this, it works great when viewing vouchers from all merchants, ie results of 40 vouchers, 8 per page 5 pages. But when viewing the vouchers of a single merchant ie with 3 vouchers the script still results in 5 pages shown 4 of which are empty. It appears to be counting all available vouchers in both cases. Can we fix this?
Chris
Hi Chris,
I just checked the file - the additional WHERE clause to restrict by merchant was being inserted after the SELECT FOUND_ROWS() query - I've re-arranged slightly and re-sent the file for you..
Cheers,
David.
--
PriceTapestry.com
Hi Chris,
I think you're close; $page needs to be pulled in from $_GET, and also SQL_CALC_FOUND_ROWS included in the initial SQL, so in place of:
$now = time();$sql = "SELECT * FROM `".$config_databaseTablePrefix."vouchers` WHERE ( (valid_from < '".$now."' AND valid_to = '0') OR (valid_from <= '".$now."' AND valid_to > '".$now."') ) ORDER BY merchant";
...have a go with:
$page = (isset($_GET["page"])?intval($_GET["page"]):1);$now = time();
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM `".$config_databaseTablePrefix."vouchers` WHERE ( (valid_from < '".$now."' AND valid_to = '0') OR (valid_from <= '".$now."' AND valid_to > '".$now."') ) ORDER BY merchant";
That should work without a specific merchant selected, however the URLs can be tidied up by removing the unnecessary variables from the prev / next / page links (and merchant= added if necessary), so in the first section of code you posted, where you have:
$prevHREF = "?q=".urlencode($q)."&page=".$prevPage."&sort=".$sort;...REPLACE with:
$prevHREF = "?".(isset($_GET["merchant"])?"merchant=".urlencode($_GET["merchant"])."&":"")."page=".$prevPage;And
$pageHREF = "?q=".urlencode($q)."&page=".$i."&sort=".$sort;...REPLACE with:
$pageHREF = "?".(isset($_GET["merchant"])?"merchant=".urlencode($_GET["merchant"])."&":"")."page=".$i;...and finally
$nextHREF = "?q=".urlencode($q)."&page=".$nextPage."&sort=".$sort;...REPLACE with:
$nextHREF = "?".(isset($_GET["merchant"])?"merchant=".urlencode($_GET["merchant"])."&":"")."page=".$nextPage;Hope this helps!
Cheers,
David.
--
PriceTapestry.com