Hi,
Im putting together pageination for a result page and I’m trying to create the count of the Id’s and its only showing 1 when it should be a lot more or change obviosuly on a different selection.
There a link below where you can see the vardump output, if you need to see it in action
$rec_limit = 10;
$countList.="select count(Id_Hot) FROM tbl_hotels". ((!empty($sqlcategory)) ? $sqlcategory : "") ." WHERE Act_Hot='1' ". ((!empty($sqlcategory2)) ? $sqlcategory2 : "") ." ";
if ($sqlregion==""){
$countList.= " AND IdCntry_Hot='".$selectCountry."'" ;
} else {
$countList.= "" .$sqlregion . "";
}
if ($sqlstar==""){
$countList.= " AND IdCat_Hot IN (1,2,3,4,5,6)";
} else {
$countList.= "" . $sqlstar . "";
}
$order = " GROUP by Id_Hot DESC";
$order2 = " GROUP by tbl_hotels.Id_Hot DESC";
$countList.= "". ((!empty($sqlcategory)) ? $order2 : $order) ."";
var_dump($countList);
$retval = mysql_query($countList);
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
$row = mysql_fetch_array($retval, MYSQL_NUM );
$rec_count = $row[0];
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 1;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
echo $rec_count;
At work:
http://devchecksafetyfirst.csf.dcmanaged.com/result.php?Country=1
When I do a vardump this is the output:
string(128) "select count(Id_Hot) FROM tbl_hotels WHERE Act_Hot='1' AND IdCntry_Hot='1' AND IdCat_Hot IN (1,2,3,4,5,6) GROUP by Id_Hot DESC" 1
What is the query for the results? As obviously you want the number to reflect the results, so can you do a var dump of that query so we can see its SQL?
Ok I used varDump to show the query which works fine -
string(117) "select * FROM tbl_hotels WHERE Act_Hot='1' AND IdCntry_Hot='61' AND IdCat_Hot IN (1,2,3,4,5,6) GROUP by Id_Hot DESC"
I think that is what you meant isnt it…
So both together as I can see it on the page its:
var_dump($countList); = string(129) "select count(Id_Hot) FROM tbl_hotels WHERE Act_Hot='1' AND IdCntry_Hot='61' AND IdCat_Hot IN (1,2,3,4,5,6) GROUP by Id_Hot DESC"
var_dump($sql); = string(117) "select * FROM tbl_hotels WHERE Act_Hot='1' AND IdCntry_Hot='61' AND IdCat_Hot IN (1,2,3,4,5,6) GROUP by Id_Hot DESC"
echo $rec_count = 1
$countList.="select count(Id_Hot) FROM tbl_hotels". ((!empty($sqlcategory)) ? $sqlcategory : "") ." WHERE Act_Hot='1' ". ((!empty($sqlcategory2)) ? $sqlcategory2 : "") ." ";
if ($sqlregion==""){
$countList.= " AND IdCntry_Hot='".$selectCountry."'" ;
} else {
$countList.= "" .$sqlregion . "";
}
if ($sqlstar==""){
$countList.= " AND IdCat_Hot IN (1,2,3,4,5,6)";
} else {
$countList.= "" . $sqlstar . "";
}
$order = " GROUP by Id_Hot DESC";
$order2 = " GROUP by tbl_hotels.Id_Hot DESC";
$countList.= "". ((!empty($sqlcategory)) ? $order2 : $order) ."";
//var_dump($countList);
$retval = mysql_query($countList);
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
$row = mysql_fetch_array($retval, MYSQL_NUM );
$rec_count = $row[0];
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 1;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$left_rec = $rec_count - ($page * $rec_limit);
echo $rec_count;
?>
LOL I got it…
I commented these out and it came back with the right number.
//$order = " GROUP by Id_Hot DESC";
//$order2 = " GROUP by tbl_hotels.Id_Hot DESC";
Just seemed to make sense and to my suprise the correct figure appeared.
Right next step now.
Na rubbish that was, am going to have to scrap it and start again…
Back to the drawing board.
I have used another and better way of doing it from another site I have done, but I’m not getting any results at all now.
Can I post the whole code I’m using for someone to have a look at.
There doesnt seem to be any errors, just a breakdown in communication somewhere and I cant see it.
ok sorry got it working again - next step now.
Sorry should try for a bit longer and also go away from the computer for 10 mins (seems to work)
I have got one issue and its with the url.
Its not picking the whole url up as in the example below the full url is:
http://devchecksafetyfirst.csf.dcmanaged.com/result.php?Country=1
But when I roll over the numbers in the paging the url is:
http://devchecksafetyfirst.csf.dcmanaged.com/pagesize=10&absolutepage=5
the code to create the url on the numbers is - $mUrl = $PHP_SELF.“?pagesize=”.$mPageSize.$mConQry_Str;
How do I get to make sure the other bits in the url are also added, as if a regions is clicked it could be
http://devchecksafetyfirst.csf.dcmanaged.com/result.php?regions%5B%5D=171&Country=1
Ok have sort of got that sorted, so the url is actually correct when rolling over the numbers, but when clicking it doesnt actually change the results to the 2nd page and so on.
http://devchecksafetyfirst.csf.dcmanaged.com/result.php?Country=1
Your URL uses ‘absolutepage’ for the page number, but your code has ‘page’, you need to make the two consistent
I think I changed the code since then cpradio here is what i got so far, and in honesty it all looks fine.
$mConQry_Str="";
if($regions==0)
{
$mConQry_Str="&Country=$selectCountry";
}
else
{
$mConQry_Str="&Country=$selectCountry&regions=$regions";
}
$sql.="select * FROM tbl_hotels". ((!empty($sqlcategory)) ? $sqlcategory : "") ." WHERE Act_Hot='1' ". ((!empty($sqlcategory2)) ? $sqlcategory2 : "") ." ";
if ($sqlregion==""){
$sql.= " AND IdCntry_Hot='".$selectCountry."'" ;
} else {
$sql.= "" .$sqlregion . "";
}
if ($sqlstar==""){
$sql.= " AND IdCat_Hot IN (1,2,3,4,5,6)";
} else {
$sql.= "" . $sqlstar . "";
}
$order = " GROUP by Id_Hot DESC";
$order2 = " GROUP by tbl_hotels.Id_Hot DESC";
$sql.= "". ((!empty($sqlcategory)) ? $order2 : $order) ."";
$mQry=$sql;
$absolutepage=$post['absolutepage'];
$pagesize=$post['pagesize'];
//echo $absolutepage;
include "PageNavigator.php";
$mPageSize = @$HTTP_POST_VARS["txtPageSize"];
if ((!isset($mPageSize))||intval($mPageSize)==0)
{
// $mPageSize = @$HTTP_GET_VARS["pagesize"];
$mPageSize = $pagesize;
if ((!isset($mPageSize))||intval($mPageSize)==0)
$mPageSize = 10;
}
$mUrl = $PHP_SELF."?pagesize=".$mPageSize.$mConQry_Str;
$mAbsolutePage = $absolutepage;
if (!isset($mAbsolutePage))
{ // Set $mOffset and absolutepage if not set
$mAbsolutePage = 1;
$mOffset = 0;
} else {
$mOffset = ( $mAbsolutePage - 1 ) * $mPageSize ;
}
$mResult = mysql_query($mQry) or ("Wrong Select Query");
$mRecordCount = mysql_num_rows($mResult);
$mPageCount = intval($mRecordCount / $mPageSize);
if ($mRecordCount % $mPageSize ) $mPageCount++;
if ($mAbsolutePage>$mPageCount) $mAbsolutePage=$mPageCount;
$mOffset = ( $mAbsolutePage - 1 ) * $mPageSize ;
if ($mRecordCount == 0)
{
}
if($mOffset<0)
{
$mOffset=0;
}
$mQry .= " LIMIT $mOffset, $mPageSize "; // Get record set = $mPageSize every time
$mRes = mysql_query($mQry);
if ($mAbsolutePage == 1)
{
$mPag1 = $mAbsolutePage;
//echo $mAbsolutePage;
}
else
{
$mPag1 = (($mAbsolutePage - 1) * $mPageSize) + 1;
}
$mPag2 = $mPag1 + ($mPageSize - 1);
if ($mPag2 > $mRecordCount)
$mPag2 = $mRecordCount;
$retval=mysql_query($mQry) or die("Wrong Query");
$records=mysql_num_rows($retval);
//var_dump($records);
//$retval = mysql_query($sql);
<div id="result_Right_Results">
<? $d=mysql_query("select Id_Cntry, Nom_Cntry from tbl_countries WHERE (Id_Cntry=".$selectCountry.")");
while($f=mysql_fetch_assoc($d)){ ?>
<h1>Safe Hotels in <?=$f['Nom_Cntry']?></h1>
<?php if(!$mRecordCount == 0) { ?>
<?=PagNav($mPageSize, $mPageCount, $mAbsolutePage, $mRecordCount, $mQry, $mUrl);?> - Records <?=$mPag1?> to <?=$mPag2?> (of <?=$mRecordCount?>)
<? } ?>
<?
if($records>0)
{
$cnt=$records;
} ?>
<? } ?>
<div class="gap_Line_Result"></div>
<div class="gap_Result"></div>
<?
//while($q=mysql_fetch_array($retval)){
if(($i%0)==0 || $i==0 ){ ?>
<? for($j=1; $j<=$cnt; $j++) {
$morevalue=true;
$q=mysql_fetch_assoc($retval) or $morevalue=false;
if($morevalue){
?>
<div class="result_Hotel">
<div class="result_Hotel_Pic"><a href="hotel.php?hotel_ID=<?=$q['Id_Hot']?>" title="<?=$q['Nom_Hot']?> - More Info"><img src="<?=$q['Foto1_Hot']?>" width="176" height="118" border="0" /></a></div>
<div class="result_Middle_Section">
<div class="result_Middle_Section_Top"><span style="position:relative; line-height:27px; height:27px; vertical-align:8px"><a href="hotel.php?hotel_ID=<?=$q['Id_Hot']?>" title="<?=$q['Nom_Hot']?> - More Info" class="result_Link"><?=$q['Nom_Hot']?></a></span> <?php $int=$q['IdCat_Hot'];
if($int>0) { $k=0; while($k<$int) { ?><img src="site_images/orange_Star_White.jpg" width="21" height="27" /><?php $k++; } } ?></div>
<div class="result_Middle_Section_Middle_1"><?=$q['Dir_Hot']?></div>
<div class="result_Middle_Section_Middle_2">COMPLIANT WITH THESE STANDARDS</div>
<div class="result_Middle_Section_Bottom">
<?php
$x=mysql_query("SELECT distinct(tbl_standards.Id_Stand), tbl_standards.stand_Name, tbl_standards.Id_View, tbl_standards.Nom_Stand FROM tbl_standards LEFT JOIN tbl_hotnstand ON ( tbl_standards.Id_Stand = tbl_hotnstand.Id_Stand ) WHERE tbl_hotnstand.Id_Hot =".$q['Id_Hot']." AND (tbl_standards.Id_View=1)");
while($z=mysql_fetch_assoc($x)){ ?><a href="javascript:popUp('<? echo "standdata.php?srno=$z[Id_Stand]"; ?>','')" title="<?=$z['stand_Name']?>"><?=$z['Nom_Stand']?></a><? } ?>
</div>
</div>
</div>
<div class="gap_Result"></div>
<div class="gap_Line_Result"></div>
<div class="gap_Result"></div>
<? }
// End Build query
}
}
I’ve gone through it over and over and cant see any reason other than a small problem, but will keep looking.
The include pagenavigator.php includes the code below, and again that seems ok too.
<?
function PagNav($mPageSize, $mPageCount, $mAbsolutePage, $mRecordCount, $mQry, $mUrl)
{
global $mQry, $mUrl, $mPageSize, $mPageCount, $mAbsolutePage, $mRecordCount;
$mPad="" ;
$mMaxPages = $mPageCount ;
if ( ($mAbsolutePage % 10) == 0) {
$mCounterStart = $mAbsolutePage - 5 ;
} else {
$mCounterStart = $mAbsolutePage - ($mAbsolutePage % 10) + 1 ;
}
//echo "counte=$mCounterStart";
$mCounterEnd = $mCounterStart + 5 ;
echo "<b>Page ".$mAbsolutePage." of ".$mPageCount." : </b>";
if ($mCounterEnd > $mMaxPages) $mCounterEnd = $mMaxPages ;
if ($mCounterStart != 1) {
$mRef = "<a href='" . $mUrl ;
$mRef .= "&absolutepage=" . "1" ;
$mRef .= "'>First</a> | " ;
echo $mRef ;
$mRef = "<a href='" . $mUrl ;
$mRef .= "&absolutepage=" . ($mCounterStart - 1) ;
$mRef .= "'>Prev 10 Pages</a> " ;
echo $mRef ;
}
echo "<b> [ </b>" ;
for ($mCounter = $mCounterStart; $mCounter < $mCounterEnd +1;$mCounter++ ) {
if ($mCounter >= 10) $mPad="" ;
if (! ($mCounter == $mAbsolutePage)) {
$mRef = "<a href='" . $mUrl ;
$mRef .= "&absolutepage=" . $mCounter ;
$mRef .= "'>" . $mPad . $mCounter . "</a>" ;
} else {
$mRef = "<b>" . $mPad . $mCounter . "</b>" ;
}
echo $mRef ;
if (!($mCounter == $mCounterEnd)) print " " ;
}
echo "<b> ] </b>" ;
if ($mCounterEnd != $mMaxPages) {
$mRef = " <a href='" . $mUrl ;
$mRef .= "&absolutepage=" . ($mCounterEnd + 1) ;
$mRef .= "'>Next 10 Pages</a>" ;
echo $mRef ;
$mRef = " | <a href='" . $mUrl ;
$mRef .= "&absolutepage=" . $mMaxPages ;
$mRef .= "'>Last</a>" ;
echo $mRef ;
}
echo "";
}
?>
I turned errors and there a few undefined variables there but noticed tis warning -
Warning: Division by zero in \\DATASTORE101\CSFWEBDATA$\dev_checksafetyfirst\en\result.php on line 582
Its around this by the seems, so have tried playing with those figures and nothing.
if(($i%0)==0 || $i==0 ){ ?>
<? for($j=1; $j<=$cnt; $j++) {
$morevalue=true;
$q=mysql_fetch_assoc($result) or $morevalue=false;
if($morevalue){
?>
<div class="result_Hotel">
line 590 - <div class="result_Hotel_Pic"><a href="hotel.php?hotel_ID=<?=$q['Id_Hot']?>" title="<?=$q['Nom_Hot']?> - More Info"><img src="<?=$q['Foto1_Hot']?>" width="176" height="118" border="0" /></a></div>
I have shown where line 590 is, but its the code above more than likely
The pageing is working, but the results aren’t changing.
So its able to skip through the pages without any errors, but the same results show as first page.
Umm, Ive gone through it hundreds of times and compared to the other site and there seems nothing out of place.
OK I htink I have found the problem, but it doesnt seem to make sense as the variables are correct.
Basically the value in absolutepage in the url isnt being grabbed to create the values below:
$absolutepage=$post['absolutepage'];
echo $absolutepage;
$pagesize=$post['pagesize'];
I changed $absolutepage=$post[‘absolutepage’]; to $absolutepage=2;
And it went to page two, I cant seem to work out why the value within absolutepage in the url isnt able to be collected.
OK got it sorted, added:
if($_SERVER[‘REQUEST_METHOD’]==‘POST’ ) { $post=$_POST; }
elseif($_SERVER[‘REQUEST_METHOD’]==‘GET’ ) { $post=$_GET; }
and it works fine.
Cheers