What's wrong with my DB query (COUNT, UNION ALL, PDO)

[PHPBear]

Someone gave me a script that works - but it doesn't work for me. I've been trying to debug it, but I'm striking out.

 

First, let's start with a simple query:

$sql= "SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL";

It works, where a URL is MySite/Carl_Sagan and $MyURL = 'Carl_Sagan'

 

I have several similar queries for various websites and sections. I want to try to merge everything into a new website that will function kind of like a mini encyclopedia. So I use UNION ALL to link several queries together, like this...

 $sql = "SELECT SUM(num) FROM (
  SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :MyURL
  UNION ALL
  SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL
) AS X";
 
 $stmt = $pdo->prepare($sql);
 $stmt->bindParam(':MyUrl',$MyURL,PDO::PARAM_STR);
 $stmt->execute();
 $Total = $stmt->fetch();
 
switch($Total['num'])
{
 case 1:
// ETC.

Edited January 19, 2014 by PHPBear

[PHPBear]

That's weird; the end of my post doesn't display. I just wanted to add that the value for $Total['num'] - where the URL is MySite/Carl_Sagan - should be 1, but it's 0. When I revert to the original query (just one table), it works.

 

I can't find anything wrong, but I'm not really familiar with subqueries, and I'm still fairly new to PDO.

 

Thanks for any tips.

[jazzman1]

Take a look at mac_gyver's reply - http://forums.phpfreaks.com/topic/285170-delete-multiple-rows-with-pdo-and-checkboxes/

[mac_gyver]

you have a problem that is preventing the query from working and once it works, a problem that is preventing the value you are fetching from working.

 

1) your code has no error checking logic in it to test if the query is working or not, then reporting the pdo/mysql error when it does not. if you did, you would be getting a somewhat vague but true-full error that would be calling your attention to the place-holder/bound statement - Error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens.

 

the reason for this error is the place-holder(s) being put into the query are not spelled/capitalized the same as in the bindParm() statement, i.e. the number of them don't match because the names being used don't match.

 

2) you don't have php's error_reporting set to E_ALL and display_errors set to ON so that a nonexistent variable reference would be reported. if you did, you would be getting an error at the $Total['num'] reference - Notice: Undefined index: num in your_file on line x

 

this error is because the query as written doesn't select anything named 'num'. you would need to add an alias in the query statement.

Edited January 19, 2014 by mac_gyver

[PHPBear]

Thanks for the tips. I changed that last MyURl to MyURL and have error reporting on now. But I still get the same result - even when I strip the subquery down to just the original table, like this...

 

 

 $sql = "SELECT SUM(num) FROM (
  SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL
) AS X";

[PHPBear]

Now I get it; I changed  $sql = "SELECT SUM(num) to  $sql = "SELECT SUM(num) as num and it works great. Thanks!