Static Value doesn't display (COUNT, UNION ALL query)

Question

With a lot of help from this site I now have a query that displays pages from several database tables, where $MyURL equals a page URL (e.g. MySite/Crazy_Horse matches the value Crazy_Horse in table people, field URL).

The only remaining problem is the static values (MySite2). I can't echo it on the display page. Yet it must be working, because if I comment out this line - $MySite2 = $row['MySite2']; - I get an error message saying $MySite2 hasn't been defined. But when I restore that line, the error message vanishes, yet echo $MySite2 doesn't display anything.

Can anyone see what I'm doing wrong?

$sql = "SELECT SUM(num) as num FROM (
  SELECT 'GZ' AS MySite2, COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL
  UNION ALL
  SELECT 'All' AS MySite2, COUNT(Name) AS num FROM gw_geog WHERE Name = :MyURL
  UNION ALL
  SELECT 'GS' AS MySite2, COUNT(URL) AS num FROM gs WHERE URL = :MyURL
 ) AS X";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();

while ($row = $stmt->fetch())
{
 $MySite2 = $row['MySite2'];
 $Total = $row['num'];
 switch($Total)
{
 case 1:
 require($BaseINC."/$MyPHP/inc/C/2_Child.php");
 break;
 case 0:
 require_once($BaseINC."/404.php");
 break;
 default:
 require($_SERVER['DOCUMENT_ROOT']."/Dupe.php");
 break;
  }
}

You can't call `MySite2` because it's not in your outer `SELECT` list. — Kermit, Jan 20 '14 at 23:08
You aren't getting errors when you execute this? According [to PDO docs](http://www.php.net/manual/en/pdo.prepare.php), and my experience, you cannot reuse `:MyURL` 3 times in one prepared statement, also [this question](http://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders) — Michael Berkowski, Jan 20 '14 at 23:08
try php's `var_dump` (http://www.php.net/var_dump) to help debug — tr33hous, Jan 20 '14 at 23:08

score 0 · Answer 1 · answered Jan 20 '14 at 23:08

0

Change your query in this way:

$sql = "SELECT X.MySite2, SUM(x.num) as num FROM (
  SELECT 'GZ' AS MySite2, COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL
  UNION ALL
  SELECT 'All' AS MySite2, COUNT(Name) AS num FROM gw_geog WHERE Name = :MyURL
  UNION ALL
  SELECT 'GS' AS MySite2, COUNT(URL) AS num FROM gs WHERE URL = :MyURL
 ) AS X group by X.MySite2"

If you want to show MySite2 you must group about this value your aggregate function.

You can't show because the visibility of your query has given by upper level, so I've added MySite2 in your field list.

Tell me if it's OK

answered Jan 20 '14 at 23:08

Joe Taras

15,166
7
42
55

@ Joe Taras - That's weird; your script works great...but only if the value for MySite2 is 'All'. If the value is 'GZ' or anything else, there are no error messages, but I get a 404 error page; apparently, the value for $Total = $row['num'] is 0 when it should be one. – Jan 20 '14 at 23:23
Here's my code: $sql = "SELECT X.MySite2, SUM(x.num) as num FROM ( SELECT 'All' AS MySite2, COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL UNION ALL SELECT 'All' AS MySite2, COUNT(Name) AS num FROM gw_geog WHERE Name = :MyURL UNION ALL SELECT 'All' AS MySite2, COUNT(URL) AS num FROM people WHERE URL = :MyURL UNION ALL SELECT 'GS' AS MySite2, COUNT(URL) AS num FROM gs WHERE URL = :MyURL ) AS X group by X.MySite2"; – Jan 20 '14 at 23:23
404 error is an HTTP error, you must analyze why your code return not found page error. Your new query it's OK about field visibility. – Joe Taras Jan 21 '14 at 07:56

Static Value doesn't display (COUNT, UNION ALL query)

1 Answers1