PHP PDO Prepared Statement MySQL Count of Select where X LIKE

Question

Using PHP v. 5.2.14 and PDO-MySQL extension.

I am new to prepared statements. Need to create a search form (so user input) and a working query of the "Select all X where X like…" variety.

Code and Results:

$sql = 'SELECT COUNT(*) as num_books from t_books where title LIKE :search_term';
// Later access as num_books
$prep = $dbh->prepare($sql);
$num = $prep->execute(array(':search_term' => '%'.$search_term. '%'));
$total=$num->fetchColumn();

Var dump of $prep: object(PDOStatement)#2 (1) { ["queryString"]=> string(58) "SELECT COUNT(*) from t_books where title LIKE :search_term" }

Fatal error: Call to a member function fetchColumn() on a non-object

If $prep is an object, then $num should be an object. But it is boolean (true). What happened here?

But more importantly, how can I do a count?

I read Row count with PDO -. Suggestions there do not work for me because I need a Prepared Statement. Will have user input.

Thanks for your help.

Michael Berkowski Michael Berkowski · Accepted Answer · 2012-08-22T20:30:48

PDOStatement::execute() only returns a boolean indicating success or failure, so your variable $num is not what you want to fetch from. By assigning the result to $num, you are not assigning the object $prep, but rather only the return value of the method call to execute() (TRUE).

Instead, fetchColumn() from $prep, your PDOStatement object.

$num = $prep->execute(array(':search_term' => '%'.$search_term. '%'));
if ($num) {
  $total = $prep->fetchColumn();
}

Beyond that, your SQL should execute correctly and the one column it returns should contain the count you need.

In this case (especially since you are calling fetchColumn() with no argument) it doesn't matter, but for future readers, it is advisable to name the COUNT() aggregate with an alias that can then be used in an associative or object fetch:

$sql = 'SELECT COUNT(*) AS num_books from t_books where title LIKE :search_term';
// Later access as num_books

PHP PDO Prepared Statement MySQL Count of Select where X LIKE

1 Answers