为COUNT查询连接表

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?

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.

If you could live with the counts on one line, you could write a query that uses :url exactly once:

select tp.num_topic, tp.num_people, count(*) as num_taxon
from (select t.url, t.num_topic, count(*) as num_people
      from (SELECT t.url, COUNT(URL) AS num_topic
            from pox_topics t
            where t.URL = :url
           ) t join
           people p
           on t.url = p.url
    ) tp join
    gz_life gl
    on gl.Taxon = tp.url;

This turns the :url into a column and then uses successive layers of subqueries to calculate the counts.

EDIT:

It is possible that some of the counts would be 0. To handle that situation:

select tp.num_topic, tp.num_people, count(gl.taxon) as num_taxon
from (select t.url, t.num_topic, count(p.url) as num_people
      from (SELECT const.url, COUNT(t.URL) AS num_topic
            from (select :url as url) const left outer join
                 pox_topics t
                 on t.url = const.url
           ) t left outer join
           people p
           on t.url = p.url
    ) tp left outer join
    gz_life gl
    on gl.Taxon = tp.url;