I have a bit of php as below which is being called by ajax, this isnt the issue as before I started doing what Im doing now it worked fine, and still does, I just cant work out why I cant count the number of rows in the db.
The final aim is to create a ajax driven ‘load more’ type pagination, so instead of numbers you just click ‘load more’ in a bar and more items appear down the page.
if(strlen($in)>0 and strlen($in) <40 ){
if ($radioB == "region") {
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1";
$result = $dbo->prepare($sql);
$result->execute();
$number_of_rows = $result->fetchColumn();
} elseif ($radioB == "hotel") {
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels where Nom_Hot like '%$in%' AND Act_Hot=1";
}
foreach ($dbo->query($sql) as $nt) {
if ($number_of_rows > 0){
echo "1";
} else {
echo "2";
}
$msg2="";
$int=$nt['IdCat_Hot'];
if (in_array($nt['IdCat_Hot'], array(6, 7))) {
} else {
if($int>0) { $k=0; while($k<$int) {$msg2.="<img src='http://www.checksafetyfirst.com/site_images/orange_Star_Transparent.png' width='11' height='10' style='vertical-align:1px;' alt='gold star' />"; $k++; } } }
$msg .="<div class='obj'><a href='#'><p class='text'>REQUEST REPORT</p><div class='item'><img src=http://www.checksafetyfirst.com".$nt['Foto1_Hot']." alt='pepsi' width='360' height='239'><div class='item-overlay top'></div></div></a><p class='nameHotel'>".$nt['Nom_Hot']."</p><p class='addressHotel'>".$nt['Dir_Hot']."</p></div>";
}}
echo $msg;
This is the very start as i need to add the ajax and everything, but disaapointed Im stuck on the first hurdle.
The bit I’m having a problem with is where Im echoing out the values 1 and 2, because it should be ‘1’ as the hotels im putputting is more than 0, I can see them on the page. But what its echoing out is the number 2, meaning there nothing in $number_of_rows
fetchColumn() returns either the specified column from the query results, or if you don’t specify a column number it fetches the first one, or it returns FALSE. Surely you would want to use rowCount() instead, if you are actually after the number of rows.
But, the value of $number_of_rows is only set if you’re searching by region, not if searching by hotel - is that intentional?
Yes on the last point droopsnoot, I am only testing it whilst searching by region, but it will need to work with hotel too, but thought id get this one working first.
And ye your right, changed it to rowCount() and it worked thank you.
But on looking at it, Ive got this wrong anyway. I need to create a count through select and then set a limit, to then use that limit in the second select statement to limit the number of hotels in show above the ‘Load More’ bar.
Like this instead -
$queryAll = mysqli_query($con,"SELECT COUNT(*) as num_rows FROM tutorials WHERE id < ".$_POST['id']." ORDER BY id DESC");
$row = mysqli_fetch_assoc($queryAll);
$allRows = $row['num_rows'];
$showLimit = 2;
//get rows query
$query = mysqli_query($con, "SELECT * FROM tutorials WHERE id < ".$_POST['id']." ORDER BY id DESC LIMIT ".$showLimit);
Cant get this to work now, sorry, I’m just swapping over to PDO and couldnt have given myself a harder project by the looks
$sqlAll="select COUNT(*) as num_rows from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1";
$result = $dbo->prepare($sqlAll);
$result->execute();
$number_of_rows = $result['num_rows'];
echo $number_of_rows."first";
$showLimit = 2;
Getting this error - Fatal error: Cannot use object of type PDOStatement as array in
Ah yes I see, thats where I was getting the previous bit from and got confused in how I was using it.
OK, looks like that is sorted as can easily add the limit to the second select statement, Then I need to add the ‘Show More’ button, and try and add the ajax in.
Oh, I’m never quite sure with quotes and non-quotes, and in fact one of the good things about prepared statements was not having to deal with them. I suspect as the only legal value there is a number (and possibly a comma and another number), there’s no need to quote it.
Right I see, oh well, at least I know to try both in the future.
Im following this tutorial, and I wondered if you could help me out to get the final bit working, as have been at this a while.
And this is where my project currently is - Test Area
As the radio button is already selected for region, just type somehting like Cancun to get the results, the limit is owrking as have set it to 9, and there 25 hotels in this db for cancun. Its just this last bit of php and then i got the ajax to try and fit in with the ajax thats already there
Just in case, I’ll post the full code for that 1 page
<div id="container">
<?php
require "config2.php";
header('Content-Type: text/html; charset=Windows-1252');
if(isset($_GET['radio'])){$radioB=$_GET['radio'];}else{$radioB='';}
if(isset($_GET['txt'])){$in=$_GET['txt'];}else{$in='';}
if(isset($_GET['sid'])){$sid=$_GET['sid'];}else{$sid='';}
$msg="";
if(strlen($in)>0 and strlen($in) <40 ){
if ($radioB == "region") {
$sqlAll="select COUNT(*) as num_rows from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1";
$result = $dbo->prepare($sqlAll);
$result->execute();
$num_rows = $result->fetchColumn();
$showLimit = 9;
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1 LIMIT $showLimit";
} elseif ($radioB == "hotel") {
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels where Nom_Hot like '%$in%' AND Act_Hot=1";
}
foreach ($dbo->query($sql) as $nt) {
if($num_rows > $showLimit){ ?>
<div class="show_more_main" id="show_more_main<?php echo $tutorial_id; ?>">
<span id="<?php echo $tutorial_id; ?>" class="show_more" title="Load more posts">Show more</span>
<span class="loding" style="display: none;"><span class="loding_txt">Loading….</span></span>
</div>
<?php }
$msg .="<div class='obj'><a href='#'><p class='text'>REQUEST REPORT</p><div class='item'><img src=http://www.checksafetyfirst.com".$nt['Foto1_Hot']." alt='pepsi' width='360' height='239'><div class='item-overlay top'></div></div></a><p class='nameHotel'>".$nt['Nom_Hot']."</p><p class='addressHotel'>".$nt['Dir_Hot']."</p></div>";
}}
echo $msg;
?>
<div class="obj push"></div>
<div class="obj push"></div>
<div class="pushend"></div>
</div>
I’m not really sure where to start to be honest without being able to actually play with the code directly. I can see that it shows “See More”, though it’s showing that several times across the top of the results list, and I imagine it should only do it once. That should then link back to the search PHP code and pass in the radio value, search string and last-ID from the original search, and you use that in the search page as the second LIMIT parameter.
Ye it should only do it once, and ye sorry about the lack of code etc, so I was in the process of editing the above message to include the code of the page thats being called from the index2.php page.
There isnt a lot in fairness, and it was working really well, but the next step was the ‘Show More’ option and its very tricky.
I could send you both files if that helped, its just the wrong db obviosuly
Ok sorry this is my last post for the day as I try and work it out, I put the foreach back and changed a few things and I’ve stepped it up a bit further, I have updated the live site and here is my code so far -
<div id="container">
<?php
require "config2.php";
header('Content-Type: text/html; charset=Windows-1252');
if(isset($_GET['radio'])){$radioB=$_GET['radio'];}else{$radioB='';}
if(isset($_GET['txt'])){$in=$_GET['txt'];}else{$in='';}
if(isset($_GET['sid'])){$sid=$_GET['sid'];}else{$sid='';}
$msg="";
if(strlen($in)>0 and strlen($in) <40 ){
if ($radioB == "region") {
$sqlAll="select COUNT(*) as num_rows from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1";
$result = $dbo->prepare($sqlAll);
$result->execute();
$num_rows = $result->fetchColumn();
$showLimit = 9;
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) where tbl_resorts.Nom_Rsrt like '%$in%' AND Act_Hot=1 LIMIT $showLimit";
} elseif ($radioB == "hotel") {
$sql="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels where Nom_Hot like '%$in%' AND Act_Hot=1";
}
//var_dump($sql);
foreach ($dbo->query($sql) as $nt) {
$msg .="<div class='obj'><a href='#'><p class='text'>REQUEST REPORT</p><div class='item'><img src=http://www.checksafetyfirst.com".$nt['Foto1_Hot']." alt='pepsi' width='360' height='239'><div class='item-overlay top'></div></div></a><p class='nameHotel'>".$nt['Nom_Hot']."</p><p class='addressHotel'>".$nt['Dir_Hot']."</p></div>";
}
if($num_rows > 0){
echo $msg;
}
if($num_rows > $showLimit){ ?>
<div class="show_more_main" id="show_more_main<?php echo $tutorial_id; ?>">
<span id="<?php echo $tutorial_id; ?>" class="show_more" title="Load more posts">Show more</span>
<span class="loding" style="display: none;"><span class="loding_txt">Loading….</span></span>
</div>
<?php } }
//echo $msg;
?>
<div class="obj push"></div>
<div class="obj push"></div>
<div class="pushend"></div>
</div>
So it now seems to be working, which I’m quite proud of lol, but Its now trying to integrate the ajax which is going to be a step up as not to familiar with it to go changing things around and integrating code into whats already there.
But if the above code isnt right, then please let me know
I think your $tutorial_id variable needs to be populated with the highest row ID number from your query. From what I could gather of the tutorial (and I don’t really have Ajax experience of any note) that’s how it passes the second LIMIT parameter through when you press “Show More”.
