Can I use select COUNT(*) in pdo

PHP
1

Am I able to use the following select statement, as I need to gather the value of num_rows using count

$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 WHERE tbl_hotels.Id_Hot < ".$_POST['id']." ORDER BY tbl_hotels.Id_Hot DESC";
$result = $dbo->prepare($sqlAll); 
$result -> execute(array($num_rows));
echo $result -> rowCount();

I need to use the value of num_rows to control a loop

As I need to put that value into a var as below, or somehting similar to use it

$allRows = $result['num_rows'];
2

first, youā€™re open to SQL Injection. Prepared Statements only protect you, when you use placeholders.

second,

$stmt = $dbo->prepare($sql);
$stmt->execute(...); // your data go in here !!
$num_rows = $stmt->fetchColumn();
3

Hi Dormilich.

OK cheers for the help on the injection issue, we changing all the ocde over and am still learning.

Back onto this though, have tried the below and am not getting the value when I echo it out.

$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 WHERE tbl_hotels.Id_Hot < ".$_POST['id']." ORDER BY tbl_hotels.Id_Hot DESC";
$stmt = $dbo->prepare($sqlAll); 
$stmt -> execute(array($num_rows));
$num_rows = $stmt->fetchColumn();
echo $num_rows."happy";
4

youā€™re still not using prepared statements right.

given your insecure code, you better would do

$stmt = $dbo->query($sqlAll);
$num_rows = $stmt->fetchColumn();

but you should definitely rewrite that into a prepared statement.

if that still doesnā€™t work, check if the SQL is correct in the first place.

5

Does the query return what you expect if you run it from phpmyadmin or whatever admin you use? Obviously putting in values for the variables.

6

There def one error, and its to do with passing the value of the search into this select statement and using it as LIKE ā€˜%$in$ā€™, its fine being used in the first select statement which is showing the first nice.

if(isset($_GET['txt'])){$in=$_GET['txt'];}else{$in='';}

$query="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 9";

which is coming from the ajax below and the form

<input type="radio" name="rdio" value="region" checked onclick="clear_form_elements(this.form)" class="radio-buttons" data-hint="eg. Cancun, Barcelona, London, Phuket..." ><label>City or Region</label>

if ($("input[name='rdio']:checked").val() == 'region')
genderValue= "region"
var url="ajax-search-demock_3.php";
url=url+"?txt="+str+"";
url=url+"&radio="+genderValue;
url=url+"&sid="+Math.random();
httpxml.onreadystatechange=stateChanged;
httpxml.open("GET",url,true);
httpxml.send(null);
document.getElementById("msg").innerHTML="Please Wait ...";
document.getElementById("msg").style.display='inline';
document.getElementById("displayDiv").style.display='block';

So when I then click the ā€˜SHOW MOREā€™ button

<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>

This ajax script comes into action

$(document).ready(function(){
$(document).on('click','.show_more',function(){
var ID = $(this).attr('id');
$('.show_more').hide();
$('.loding').show();
$.ajax({
type:'POST',
url:'ajax-search-demock_3.php',
data:'id='+ID,
success:function(html){
$('#show_more_main'+ID).remove();
$('.tutorial_list').append(html);
}
}); 
});
});

Which then gets picked up the section of code im working on to show the next load of hotels after show more but the value of $in isnt being passed over

if(isset($_POST["id"]) && !empty($_POST["id"])){
$msg="";

$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 WHERE tbl_hotels.Id_Hot < ".$_POST['id']." ORDER BY tbl_hotels.Id_Hot DESC";
$stmt = $dbo->prepare($sqlAll); 
$num_rows = $stmt->fetchColumn();
echo $num_rows."happy";

and the echo is empty but for the word happy, which im not lol.

This is mind bending, wish I could send you the two files as its hard to explain by type

7

I thin I need to send the value of $in on click of the first ā€˜SHOW MOREā€™ button to the second ajax script, so that sends the vale to be used in the second lot of php.

Think that might work, and have been having a go and cant get it to work

8

I donā€™t see anywhere in that code where the value of $in is being sent anywhere for the second query. Your PHP sends the ID value in this bit:

<span id="<?php echo $tutorial_id; ?>" class="show_more"

and your JavaScript code passes it back into the PHP code with these parts:

var ID = $(this).attr('id');
... and ...
data:'id='+ID,

which in turn produces a POST parameter called $_POST['id'] back into your search code so it can decide which was the last ID that it displayed. So somehow you need to also pass back the $in value. I havenā€™t much experience of Ajax (itā€™s something Iā€™m trying to learn) but Iā€™d be tempted to do something like, change the first bit of code:

<span id="<?php echo "id=" . $tutorial_id . "&in=" . $in; ?>" class= ....

where $in in the above is whatever your code has that variable as. Then change the JS code:

var ID = $(this).attr('id');  // still the same as before
.. and ..
data: ID,  // don't need to add the parameter name first, as it's in the ID value

In theory, the value of the data will be ā€œid=7&in=regionā€ which will come out as your two POST variables, then your code can check for the presence and value of $POST['in'] as well as $_POST['id'], and use them in the query.

10

OK Im glad you said as i didnt know how to add two values to the id to carry it over, thats where i was struggling.

So added it like this

<span id="<?php echo "id=".$tutorial_id. "&in=".$in; ?>" class="show_more" title="Load more posts">Show more</span>

Then as you said changed the script to be like this

var ID = $(this).attr('id');
$('.show_more').hide();
$('.loding').show();
$.ajax({
type:'POST',
url:'ajax-search-demock_3.php',
data:ID,
success:function(html){
$('#show_more_main'+ID).remove();
$('.tutorial_list').append(html);
}
});

But its then not making the show more button hide as the ID value has changed in

$('#show_more_main'+ID).remove();

To close it down here

<div class="show_more_main" id="show_more_main<?php echo $tutorial_id; ?>">
<span id="<?php echo "id=".$tutorial_id. "&in=".$in; ?>" class="show_more" title="Load more posts">Show more</span>
<span class="loding" style="display: none;"><span class="loding_txt">Loading....</span></span>
</div>

The top line.

And the second lot of hotels arent displaying at the start because maybe of this line

if(isset($_POST["id"]) && !empty($_POST["id"])){

But we def on the right path, and glad you said it as was thinking it myself but couldnt work out how to pass $in over in the id

The good news though is that the php bit did work, I echoā€™d out the value ID as below

success:function(html){
alert(ID);
$('#show_more_main'+ID).remove();
$('.tutorial_list').append(html);
}

and got id=247393&in=Phuket

11

Ah, I didnā€™t read down as far as the bit where it hides the div. I guess if all that id is used for, you could rename it to include the rest of the information that will be in the ID variable:

<div class="show_more_main" id="show_more_main<?php echo "id=" . $tutorial_id . "&in=" . $in;  ?>">

I donā€™t know whether that causes issues with CSS or whatever. The other way would be to use some more javascript to split out the original value of the $tutorial_id variable by searching for the first ā€˜=ā€™ and the next ā€˜&ā€™, and use that to hide the div. I donā€™t know how youā€™d do that in JS.

var ID = $(this).attr('id');
.. search for first "=" in ID
.. search for first "&" after the first "=" in ID
.. set ID2 = the section of string between those two

I would think that the id value would still be present, the only change weā€™ve made is that instead of having the JS code add the parameter name when itā€™s calling the PHP code, itā€™s now part of the ID string that comes from the HTML attribute so as to be able to add both values. You could add a debug echo() to your code to see whether that information is being received at all.

ETA - so the id value is correct, next is to see if that (and ā€œinā€) are getting to the PHP.

12

Ye just thinking about it now, the id value coming in is definatly going to have to be slplit up, leaving the original value with ID and putting the new $in value into IN perhaps, and then working out how thats passed over to with ID, as like you this AJAX stuff is brand new to me.

13

In theory, though, the string that you send in as ā€œdataā€ via Ajax should get parsed out into the $_POST array, so if you var_dump that array you should have elements id and in, and corresponding values for them. Then validate / sanitise into your local variables.

At least I think thatā€™s true - Iā€™ve seen different examples. The constraint is it needs to come in as a single string so that you can use the span id to pass it into the Ajax call, so what you actually want is to pass a single string and then split it out later. Iā€™m not 100% whether this method actually will cause it to parse out automatically, so var_dump($_POST) to be sure.

14

I just jumped onto the script forum and got some help with it, was advised to use

<span data-id="<?php echo $tutorial_id; ?>" data-id2="<?php echo $in; ?>" class="show_more" title="Load more posts">Show more</span>

And got the script working by changing it to

$(document).ready(function(){
$(document).on('click','.show_more',function(){
var id = this.dataset.id;
var id2 = this.dataset.id2;
$('.show_more').hide();
$('.loding').show();
$.post('ajax-search-demock_3.php', this.dataset, function (html) {
$('#show_more_main'+id).remove();
$('.tutorial_list').append(html);
});
});
});

Then when passing it back I gatherd them as below

if(isset($_POST["id"]) && !empty($_POST["id"])){    
if(isset($_POST["id2"]) && !empty($_POST["id2"])){
$ID2 = $_POST['id2'];
}

So now I have got it working, Iā€™m back to my original problem, which was to get the value num_rows from COUNT in the select statement, I still cant work it out

$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 '%".$ID2."%' AND Act_Hot=1 WHERE tbl_hotels.Id_Hot < ".$_POST['id']." ORDER BY tbl_hotels.Id_Hot DESC";

I used

$result = $dbo->prepare($sqlAll); 
$result->execute(); 
$num_rows = $result->fetchColumn();
echo $num_rows;

But it came back with 0, which I know isnt true

Just went back through the thread and seen you posted to use

$result = $dbo->query($sqlAll); 
$num_rows = $result->fetchColumn();
echo $num_rows;

and I get this error

Fatal error:  Call to a member function fetchColumn() on boolean in

And yes I checked the sql is correct and all seems to be fine. I used var_dump and got

"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 '%Cancun%' AND Act_Hot=1 WHERE tbl_hotels.Id_Hot < 247185 ORDER BY tbl_hotels.Id_Hot DESC"

Which seems correct to me

15

There are many conflicting statements around, what is the best way to row count using PDO in PHP? Before using PDO I just simply used mysql_num_rows

16

The mysql_ functions and interface have been deprecated for years now and have been removed from the latest version of PHP.

17

I have slightly changed it to

$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 '%".$_POST['id2']."%' AND Act_Hot='1' AND (tbl_hotels.Id_Hot < ".$_POST['id'].") ORDER BY tbl_hotels.Id_Hot DESC";
$result = $dbo->prepare($sqlAll); 
$num_rows = $result->rowCount();
echo $num_rows;

And I got ā€˜0ā€™

So didnt work

18

I may have found an issue with the select statement, as just ran it through SQL and I got this

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 ā€˜"select
COUNT(*) as num_rows from tbl_hotels LEFT JOIN tbl_resorts ON
(tbl_resorā€™ at line 1

For

"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 '%".$_POST['id2']."%' AND Act_Hot='1' AND (tbl_hotels.Id_Hot < ".$_POST['id'].") ORDER BY tbl_hotels.Id_Hot DESC"
19

And you did it wrong.

Imagine youā€™ve got a bank account. What it the proper way to get a balance:

  1. To ask a bank to bring all the money in cash and then count it yourself
  2. To ask a bank to provide the balance already?

With mysql_num_rows you effectively did the former.

The whole idea of using a database is to making it all the data manipulations for you. Counting rows included.
So, to count the rows, instead of requesting the rows themselves, then counting them and then discarding them, you have to run an SQL query thatā€™s literally counting the rows. And then returning a number.

So, to get the count of rows you have to do what was suggested to that poor guy who started the topic long time ago:

$stmt = $pdo->prepare("SELECT count(*) FROM goods WHERE category=?");
$stmt->execute([$category]);
$count = $stmt->fetchColumn();

Iā€™ve got even a dedicated section in my article, entirely devoted to various delusions and malpractices connected to counting rows in PHP

1 Like
20

Oh cheers,

I have changed it to yours but am getting an error on this line

$stmt->execute([$Nom_Rsrt]);

From

$stmt=$dbo->prepare("select 1 from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) WHERE tbl_resorts.Nom_Rsrt like '%".$_POST['id2']."%' AND Act_Hot='1' AND (tbl_hotels.Id_Hot < ".$_POST['id'].") ORDER BY tbl_hotels.Id_Hot DESC"); 
$stmt->execute([$Nom_Rsrt]);
$count = $stmt->fetchColumn();
echo $count;
21

Sorry, that was answer for @JulieJTaylor
You cannot just copy and paste it. To use it you have to understand basic PDO first.
In your case it should be something like this

$sql = "select count(*) from tbl_hotels 
        LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) 
        WHERE tbl_resorts.Nom_Rsrt like ? AND Act_Hot='1' AND (tbl_hotels.Id_Hot < ?) 
        ORDER BY tbl_hotels.Id_Hot DESC"
$stmt=$dbo->prepare($sql); 
$stmt->execute(["%$_POST[id2]%", $_POST['id']]);
$count = $stmt->fetchColumn();

echo $count;

1 Like