current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 2 down vote

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.

share|improve this answer
    
Or var_dump($query) –  Bill Karwin Aug 31 '11 at 22:02
    
Right - seeing the actual type's of those values may be of use as well. –  Adrian Schneider Aug 31 '11 at 22:07
    
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 Aug 31 '11 at 22:23
    
Here is actual evaluation:string(10) "2011-08-24" string(10) "2011-08-24" int(913) –  Will22 Aug 31 '11 at 22:38
    
Which database adapter are you using. If you run $db->fetchAll($query) instead of query, fetchAll do you get any difference? –  Adrian Schneider Aug 31 '11 at 22:43
up vote 0 down vote

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

share|improve this answer
    
form() should be from() –  Adrian Schneider Sep 2 '11 at 20:27
    
ok. thanks. I'll change it. –  Asuraya Sep 3 '11 at 16:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.