PDO Count Query

PHP
1

This is an example of the type of query I use to display dynamic web pages. This particular query is associated with a website/section that displays URL’s like MySite/People/Don_Juan, where Don_Juan = both $MyURL and a value in the field URL, table people…


 $res = mysql_query("SELECT COUNT(URL)
  FROM people
  WHERE URL = '$MyURL' AND Site = 'PX'
  OR '$MyURL' = CONCAT('Washington/', URL) AND Site = 'SM'");

  if (!$res) {
    die('Invalid query: ' . mysql_error());
 }

 $result = mysql_result($res, 0);

// PART 2 - Interpret the results...
switch ($result)
{
 case 1:
 // include related files
 break;

 case 0:
 // include 404 NOT FOUND error message
 break;

 default:
 // multiple entries; deal with as needed
 break;
}

Now I’m trying to figure out how to convert this to a PDO query. Since COUNT is the simplest query, I thought it would be the easiest to convert. To my surprise, many online references say PDO can’t perform COUNT queries. As I understand it, all the PDO queries that do this sort of thing are workarounds.

Anyway, I’ve tried several PDO scripts, but nothing works.

I start out with my PDO database connection (replacing “USERNAME” and “PASSWORD” with my username and password, of course…


try {
    $db = new PDO('mysql:host=localhost;dbname=db_new;charset=utf8', 'USERNAME', 'PASSWORD');
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

The above code is on a separate file that’s included in every page on my website.

Next comes this code…


$sql= "SELECT URL FROM people
WHERE URL = '$MyURL'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$total = $stmt->rowCount();

But it doesn’t work, and there’s no error message to indicate what the problem is. (However, if I modify my username or password in the connection script, I do get an error message referring to my database connection.) Once I do get it to work, I assume I can finish the job by simply replacing $result with $total…


switch ($total)
{
 case 1:
 // include related files
 break;

 case 0:
 // include 404 NOT FOUND error message
 break;

 default:
 // multiple entries; deal with as needed
 break;
}

So can anyone tell me what’s wrong with my code? Or should I be using a different script altogether?

Thanks.

2

You should put this:

$sql= "SELECT URL FROM people
WHERE URL = '$MyURL'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$total = $stmt->rowCount();

Inside a try catch statement as well and see if it throws a exception.

3

I -believe- what you’re referring to is PDO’s inability to do a $stmt->rowCount() on mysql queries (as well as others). There should be NO problem with PDO running a query involving a COUNT(), because PDO doesnt actually have anything to do with the execution of the query - only handling the result. As your database engine has not changed, it would be perfectly capable of running the query. (And the way you’re doing it is the ‘correct’ way anyway - use a Database COUNT instead of a Row Count if you dont want the data!)

So instead of trying to do a rowCount, execute the query, then read the first (and only) record from the result:


$sql= "SELECT COUNT(URL) AS num FROM people
WHERE URL = '$MyURL'";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$total = $stmt->fetch();
switch($total['num']) {
//...

Though if you’re getting into the habit of Preparing statements (a good habit!), you should look into making that parameter bound as well…


$sql= "SELECT COUNT(URL) AS num FROM people
WHERE URL = :url";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':url',$myURL,PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->fetch();
switch($total['num']) {
//...

(There are other ways to bind parameters; check the manual page for Execute for more examples!)

If you DO need the data, try a fetchAll, which will give you the full result set in an array - which can then be count()'d as a PHP native function, or else run a counter inside your fetch loop.

4

Thanks for the tips; both of the suggest queries work. FINALLY, I have a PDO query that actually works. :wink:

StarLion - I do need to fetch more data, but I do that with a separate query. I want to keep my page display queries as simple as possible, as the queries I use to fetch more data tend to get pretty complex and confusing - and that was before I started working with PDO, which appears to be even more complex.