Why this PDO querying is not working?

PHP
1

I am migrating from mysql to PDO and undergoing all my php pages (a hundred or so) which queries the database. I am stuck on this and hope there is a fix.

// code to generate $formquey according user selection for example:


$formquery = "SELECT * FROM fauna WHERE class LIKE 'arachnida' ORDER BY ord,fam,spp";

$db = conn(); // function to connect with db

$stm = $db->prepare($formquery); // I also tried -  $stm = $db->prepare("$formquery");
$stm->execute();
$num = $stm->fetchColumn();

print "Currently showing <b>" .  $num . "</b>" . " of data";

Result no errors but no output i.e. $num is as if $num=“”
Placed SELECT * FROM fauna WHERE class LIKE ‘arachnida’ ORDER BY ord,fam,spp in MySQL Workbench and there are results so there is nothing wrong with the sql syntax

Hope it’s a question of syntax or a missing line or two!

2

Missing semi-colon at the end of your query statement.

// I also tried - $stm = $db->prepare(“$formquery”);

Don’t quote the variable.

3

that’s a typo mistake in the post only - the semicolon is present in my code, so ignore that. Actually I am editing my original post to include the missing semicolon. Thank for pointing it out! :slight_smile:

4

Now I realised that I have not stated what I want to do! I want to count the number of rows (=number of results) to substitute the old Mysql_num_rows. I am sure I am doing something wrong in the syntax. Moreover, I do not want to mess with $formquery - it has to remain untouched!

5

For a number count Change

$num = $stm->fetchColumn();

TO

$num = $stm->rowCount();

To loop through result, use

while($row = $stm->fetch(PDO::FETCH_ASSOC)){
	//print for testing
	echo "<pre>";	
	print_r($row);	
	echo "</pre>";
}
6

Ok, here’s the solution:
I had to replace fetchColumn(); with rowCount();
$num = $stm->rowCount();

Works nicely!
Self-learning the hard way is best! Hope you dont mind writing such posts - I was stuck on rowCount() as the only way of counting which does work but the query must have Count(*) which was not good for me. Hope it helps someone who has the same problem.

7

We posted at the same time!! A big THANKS Drummin for helping me. Much appreciated!

8

and of course this also works for simple queries without variables:

$stm = $db->query($formquery);
$num = $stm->rowCount(); // a perfect replacement for mysql_num_rows

9

rowCount() does not require count(*) in the query. I use it all the time when updating records to say how many records were updated, or after queries to for example to see if a match was found.

10