I'm working with PHP and MySQL. This query works, where my page URL is MySite/Carl_Sagan (Carl_Sagan = $MyURL) and Carl_Sagan is also a value in a database table named people, field URL:
$sql= "SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();
switch($Total['num'])
{
case 1:
echo "
";
require($BaseINC."/$MyPHP/inc/C/2_Child.php");
break;
case 0:
break;
default:
break;
}
But when I link several tables together through UNION ALL, turning it into a subquery, it doesn't work. I'm not getting any error messages, but the value for $Total['num'] is 0, when it should be 1.
Can anyone see the problem with my subquery, posted below?
$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();
I should note that the subquery doesn't work even if I strip it down to the original table, like this:
$sql = "SELECT SUM(num) FROM (
SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL
) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();
