How to implement sql between operator in zend_db

Question

I have the following queries which calculates the total order sale happened in last 5 months,

>     SELECT SUM(o.total), DATE(o.order_date) dateonly FROM `order` as o where (o.order_date BETWEEN  last_day(NOW() - INTERVAL 5 MONTH) AND 
> date_format(NOW(), '%Y-%m-%d')  AND (o.order_status = "P" OR
> o.order_status = "T" OR o.order_status = "S" OR o.order_status = "D"
> )) GROUP BY month(o.order_date)

I am trying to implement above query in zend. But I stuck on middle. I have the code taken from models/DbTable,

class Default_Model_DbTable_Order extends Zend_Db_Table_Abstract
{
    protected $_name = 'order';
    protected $_primary = 'order_id';
    public function month_based_orderlist()
    {
        $oDb = Zend_Registry::get("db");        
        $whereSQL  = 'o.order_id > 0';
        $whereSQL .= ' AND o.status = 1';
        $whereSQL .= ' AND o.order_date BETWEEN  last_day(NOW() - INTERVAL 5 MONTH) AND  date_format(NOW(),'.%Y-%m-%d.')';
        $whereSQL .= ' AND o.order_status = "P"';
        $whereSQL .= ' OR o.order_status = "T"';
        $whereSQL .= ' OR o.order_status = "S"';
        $whereSQL .= ' OR o.order_status = "D"';
        $select = $oDb->select()
            ->from(
                array('o' => $this->_name), 
                array(
                    'lifetimesale' => new Zend_Db_Expr('SUM(o.total)'),
                    'dateonly' => DATE('o.order_date')
                )
            )->where($whereSQL);

        //echo $select; exit;
        $result = $this->getAdapter()->fetchAll($select);
        // print_obj($result);
        return $result;            

    }
}
?>

I am trying to implement the sql query like above,what i done wrong on this. Kindly help me on this

You need to be more specific. How did you get 'stuck'? Can you post the error you received. Also, if you think this issue has to deal with zend, then post that code as well. — Mike Purcell
– Mike Purcell, Commented Nov 16, 2011 at 6:26
Could you, please, explain what problem do you mean? Do you use Zend_Db? — Oleg
– Oleg, Commented Nov 16, 2011 at 6:27
@Dinesh: Ok, now what is the specific problem? Are you not getting the results you expect, is it throwing a sql error, or is zend throwing an error? — Mike Purcell
– Mike Purcell, Commented Nov 16, 2011 at 7:02

Community · Accepted Answer · 2017-05-23 11:55:51Z

3

The bug is here:

.%Y-%m-%d.

Edit

Phil pointed out my solution was wrong. I rechecked and he was right. The solution should be just to remove the ticks, and put in double quotes:

// assume $date is set 
$whereSQL .= ' AND o.order_date BETWEEN last_day(NOW() -'
          . ' INTERVAL 5 MONTH) AND date_format(NOW(), "%Y-%m-%d")';

Alternatively, you could escape the single quotes using the backslash and remove the sting concat operator:

... date_format(NOW(), \'%Y-%m-%d\')';

Also, I see you're extending Zend_Db_Table_Abstract and, as such, you don't need the line:

$oDb = Zend_Registry::get('db');

Just omit that and use $this instead. And it would look something like this (simplified with GROUP BY clause added):

$select = $this->select()
    ->from($fromTable, $columns)
    ->where($whereSQL)
    ->group('month(o.order_date)');

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

To add the GROUP BY clause, simply do this (or see above example using method chaining):

$select->group('month(o.order_date)');

Thanks Phil for the help!

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Nov 16, 2011 at 6:44

Yes Barry

9,9465 gold badges54 silver badges77 bronze badges

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

5 Comments

mymotherland Over a year ago

Thanks, date_format(NOW(), '%Y-%m-%d') will give todays date. so what i need to pass instead '%Y-%m-%d'

Yes Barry Over a year ago

@Phil Oops! You're spot on. Thanks for the heads up. Mind checking it now?

Yes Barry Over a year ago

@Dinesh Thanks to Phil I fixed my answer and this solution should work now. Please check and let me know.

mymotherland Over a year ago

@mmmshuddup also i missed to use 'group by', so how can i use month function here in zend?

Yes Barry Over a year ago

Oh, I didn't realize that that was part of your question. I edited my answer. Please check once more.

Collectives™ on Stack Overflow

How to implement sql between operator in zend_db

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related