Query in zend framework returns empty array

Question

i am running a custom query, no models involved, and it return an empty array.

Here is exact code that i use:

$query = 'SELECT SUM(open_diff) opens, SUM(revenue_diff) revenue, SUM(revenue) real_rev, manual_rev, SUM(opens) actual_opens 
                         FROM data.discrepancy
                         WHERE discrepancy_date >= \''.$dateStart.'\' AND discrepancy_date <= \''.$dateEnd.'\' AND  feed_id = '.$feeds[$i]["feed_id"];

                $db = Zend_Registry::get('db_slave');
                $stmt = $db->query($query);

               $records = $stmt->fetchAll();


Zend_Debug::dump($records); gets me this result:

array(1) {
[0] => array(5) {
["opens"] => NULL
["revenue"] => NULL
["real_rev"] => NULL
["manual_rev"] => NULL
["actual_opens"] => NULL
}
}

The data is in the database, and if i run that query directly in MySql, i have no problems.

Please advise.

Adrian Schneider · Accepted Answer · 2011-08-31 21:56:02Z

2

MySQL will return null for sum() if there are no matching records. What is the final query being executed (with the variables evaluated)?

I'd try running that in MySQL directly as well and you'll probably get the same results.

var_dump($dateStart, $dateEnd, $feeds[$i]['feed_id']);

See what those contain and you'll probably see the problem.

answered Aug 31, 2011 at 21:56

Adrian Schneider

7,4791 gold badge21 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Adrian Schneider Over a year ago

Right - seeing the actual type's of those values may be of use as well.

Will22 Over a year ago

Variables evaluated just fine, i am running resulting queries directly in database and getting the results. Also, i removed SUMs and left real columns to select... still empty arrays

Will22 Over a year ago

Here is actual evaluation:string(10) "2011-08-24" string(10) "2011-08-24" int(913)

Adrian Schneider Over a year ago

Which database adapter are you using. If you run $db->fetchAll($query) instead of query, fetchAll do you get any difference?

Will22 Over a year ago

I'm using PDO. $db->fetchAll($query) did not make any difference, i even ran plain PHP in this action (mysql_connect... etc) - the same result. I am really confused at this point

|

Dinuka Thilanga · Accepted Answer · 2011-09-03 16:08:36Z

0

Try following code

$dbAbstract = new Zend_Db_Table_Abstract();

$select = $dbAbstract->select()
    ->from(
          array('a' => 'data.discrepancy'),
          array('SUM(open_diff) AS opens', 'SUM(revenue_diff) AS revenue'), 'SUM(revenue) AS real_rev', 'SUM(opens) AS actual_opens'
    )
    ->where('discrepancy_date >=?', $dateStart)
    ->where('discrepancy_date <=?', $dateEnd)
    ->where('feed_id =?', $feeds[$i]["feed_id"]);

$result = $dbAbstract->fetchAll($select);

Please remove AS that is not working

edited Sep 3, 2011 at 16:08

answered Sep 1, 2011 at 8:58

Dinuka Thilanga

4,35010 gold badges59 silver badges94 bronze badges

Collectives™ on Stack Overflow

Query in zend framework returns empty array

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related