php - How to implement sql between operator in zend_db
Get the solution ↓↓↓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
Answer
Solution:
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 extendingZend_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 withGROUP BY
clause added):
$select = $this->select()
->from($fromTable, $columns)
->where($whereSQL)
->group('month(o.order_date)');
$result = $this->fetchAll($select);
To add theGROUP BY
clause, simply do this (or see above example using method chaining):
$select->group('month(o.order_date)');
Thanks Phil for the help!
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: uncaught error: call to undefined function mysqli_connect()
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.