为COUNT查询连接表

Question

I'm using the following script to display pages, where $URL matches a page URL (e.g. MySite/People/Carl_Sagan)...

$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'])
{
 case 1:
 break;
 case 2:
 break;
 default:
 break;
}

On another site, I want to join several tables together, forming sort of a mini-encyclopedia. I know how to use the UNION command, but it isn't working with this query. Note that the target field in the table gz_life is named Taxon, not URL. I thought I could somehow alias it - Taxon AS URL - but that doesn't seem to be working, either.

$sql= "SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :url
UNION ALL
SELECT COUNT(URL) AS num FROM people WHERE URL = :url
UNION ALL
SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :url";

Can anyone tell me the best way to join tables together in a PDO query?

Answer 1

There are a few ways to do this (if I understand what you're trying to achieve). One would be use what you already have, but do the last step to add up the counts:

SELECT SUM(num) FROM (
  SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :url
  UNION ALL
  SELECT COUNT(URL) AS num FROM people WHERE URL = :url
  UNION ALL
  SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :url
) as subquery

Note that you will need the alias for the subquery to make the query correct.