While Loops with COUNT Queries

[PHPBear]

In this multi-table query, I gave each table a static value (e.g. 'GW' AS Site, 'GZ' AS Site)...

 $stm = $pdo->prepare("SELECT 'GW' AS Site, CGW.N, CGW.URL, CGW.Date, CGW.Year, CGW.Brief
  FROM calendar_gw CGW
  WHERE CGW.URL = :MyURL
  UNION ALL
  SELECT 'GZ' AS Site, CGZ.N, CGZ.URL, CGZ.Date, CGZ.Year, CGZ.Brief
  FROM calendar_gz CGZ
  WHERE CGZ.URL = :MyURL
  ORDER BY Year");
 $stm->execute(array(
  'MyURL'=>$MyURL
 ));

[PHPBear]

(Why do my posts always get cut off after the code?)

 

Anyway, I change Site into a variable ($Site) in a while loop:

 

 

while ($row = $stm->fetch())
{
 $Site = $row['Site'];

[PHPBear]

Now I'm trying to do something similar with a COUNT query - but it isn't working. Can you use a while loop with a COUNT query to begin with? If so, I probably have the wrong syntax on this line: while ($row = $stmt->fetch())

 

But I'm not sure how to change it. I pasted the complete script below. Thanks.

 

 $sql = "SELECT SUM(num) as num FROM (
  SELECT 'PX' AS MySite2, COUNT(URL) AS num FROM pox_topics WHERE URL = :MyURL
  UNION ALL
  SELECT 'PX' AS MySite2, COUNT(URL) AS num FROM people WHERE URL = :MyURL
  UNION ALL
  SELECT 'GZ' AS MySite2, 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();
 
while ($row = $stmt->fetch())
{
 $Site = $row['Site'];
}
 
switch($Total['num'])
{
 case 1:

[mac_gyver]

by using the SUM() aggregate function, there will only be one row in the result set. there's no point in having the extra code needed to loop if you are fetching just one row.

[PHPBear]

So does that mean I can't assign static values to data from specific tables? If so, then I'll go to Plan B - create a second set of subqueries targeting one or two fields in each table with the sole purpose of giving each table and section an ID. That will allow me to create a PHP switch based on site and section, allowing me to store more advanced queries targeting one site/section at a time.

 

Thanks.

[Barand]

So does that mean I can't assign static values to data from specific tables?

No, it doesn't mean that. You can assign static values. But it is a waste of time if you then put them in a subquery and just select an aggregation of the counts.

$sql = "
  SELECT 'PX' AS MySite2, COUNT(URL) AS num FROM pox_topics WHERE URL = :MyURL
  UNION ALL
  SELECT 'PX' AS MySite2, COUNT(URL) AS num FROM people WHERE URL = :MyURL
  UNION ALL
  SELECT 'GZ' AS MySite2, COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL
  ";
 
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
 
while ($row = $stmt->fetch())
{
    $Site = $row['Site'];
    $Total = $row['num'];
    switch($Total)
    {
        case 1:
        // etc
    }
}

[PHPBear]

I like it! Thanks.

[PHPBear]

However, I just discovered that the values for MySite2 aren't echoing on the display page. If I comment out this - $MySite2 = $row['MySite2']; - then I get an error message telling me $MySite2 hasn't been defined. When I restore that line, the error message goes away, yet nothing displays when I echo $MySite2.

 

 

$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;
    }
}

[Barand]

I wonder if it because you are only selecting "SUM(num) as num" (Again)

Edited January 20, 2014 by Barand