Zend DB - Count(*) not working

Question

My sql statement is not working with Zend, its complaining about the Count(*) field... what am I doing wrong?

// get open/closed
$stmt = $db->query('SELECT status, count(*) as total FROM reported_issues WHERE date_reported >= '.$today.' AND status IN (0,1) GROUP BY status');
while ($row = $stmt->fetch())
{
    switch ($row['status'])
    {
        case 0:
            $totalIssuesToday = $row['total'];
            break;

        case 1:
            $totalIssuesClosedToday = $row['total'];
            break;
    }
}

and the errors...

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message
'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Y' in 'where clause''
in C:\xampp\htdocs\dating\trunk\library\Zend\Db\Statement\Pdo.php:238

Stack trace:
#0 C:\xampp\htdocs\dating\trunk\library\Zend\Db\Statement.php(283): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\xampp\htdocs\dating\trunk\library\Zend\Db\Adapter\Abstract.php(484): Zend_Db_Statement->execute(Array)
#2 C:\xampp\htdocs\dating\trunk\library\Zend\Db\Adapter\Pdo\Abstract.php(235): Zend_Db_Adapter_Abstract->query('SELECT status, ...', Array)
#3 C:\xampp\htdocs\dating\trunk\html\siteadmin.php(59): Zend_Db_Adapter_Pdo_Abstract->query('SELECT status, ...')
#4 {main} thrown in C:\xampp\htdocs\dating\trunk\library\Zend\Db\Statement\Pdo.php on line 238

"its complaining" - Got some detail on the error? Are you getting a Zend_Db_Exception or what? — Peter Bailey, Jun 05 '09 at 15:04
What does your $today variable look like? Can you put all of the sql in a variable and output it so we can see how it evaluates in PHP? — jonstjohn, Jun 05 '09 at 15:21

score 6 · Answer 1 · answered Jun 05 '09 at 15:27

Aside from what others have noted about the value you have in $today, you really should be using bound parameters with your queries

<?php

$stmt = $db->query(
    "SELECT status, count(*) as total
       FROM reported_issues
      WHERE date_reported >= ?
        AND status IN (0,1)
      GROUP BY status"
    ,array( $today )
);

score 3 · Accepted Answer · answered Jun 05 '09 at 15:21

3

The complaint is actually about the WHERE -- looks like your $today variable does not contain what you think it should. Specifically, since it says:

'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Y' in 'where clause''

it seems your variable contains a Y that shouldn't be there. The count appears to have nothing to do with the issue.

answered Jun 05 '09 at 15:21

Alex Martelli

854,459
170
1,222
1,395

nailed it... forgot the DATE(' ... ') around my mask... DOH – Kladskull Jun 05 '09 at 15:34

score 3 · Answer 3 · answered Jun 05 '09 at 15:23

I'm guessing your $today variable is corrupt. Change the first line (for testing)

from

$stmt = $db->query('SELECT status, count(*) as total FROM reported_issues WHERE date_reported >= '.$today.' AND status IN (0,1) GROUP BY status');

to:

$sql='SELECT status, count(*) as total FROM reported_issues WHERE date_reported >= '.$today.' AND status IN (0,1) GROUP BY status';
try{
   $stmt = $db->query($sql);
} catch (Exception $e) {
   echo $sql."\n";
   throw $e;
}

Then you can see the raw SQL it's trying to execute.

Zend DB - Count(*) not working

3 Answers3