how to put between in Zend Framework query

Question

anyone correct this query according to ZF.i have a situation where user select differet parameters and on the base of that make my query

$user = new Zend_Session_Namespace('user');
$phone_service_id = $user->phone_service_id;

$start_date = $this->_getParam('start_date'); //02/07/2012
$end_date = $this->_getParam('end_date');     //02/21/2012
$option_call_log = $this->_getParam('option_call_log'); //COLUMN NAME
$option_call_log_asc_desc = $this->_getParam('option_call_log_asc_desc');  //ASC/DESC

i think i have a syntax error in query ,see it here

$select = $DB->select()
         ->from('CALL_LOG', array('caller_name','call_number','call_start_time','call_duration','call_direction'))
         ->where('phone_service_id = ?', $phone_service_id)
         ->where(DATE_FORMAT(date_created, '%m/%d/%Y') BETWEEN  $start_date  AND $end_date)
         ->order($option_call_log $option_call_log_asc_desc)
         ->limit(0,9);

whats wrong with this ??

Missing some quotes on ->where(DATE_FORMAT(date_created, '%m/%d/%Y') BETWEEN $start_date AND $end_date)? This should throw a PHP syntax error long before it even gets to querying the db. Shouldn't it be something like ->where("DATE_FORMAT(date_created, '%m/%d/%Y') BETWEEN $start_date AND $end_date")? (note inserted double-quotes) — David Weinraub
– David Weinraub, Commented Feb 9, 2012 at 7:15
what's the error you got? And try to echo $select and get what query you have found and run it on mysql. — Code Lღver
– Code Lღver, Commented Feb 9, 2012 at 7:15
In fact, it looks like you need quotes inside the ->order() clause, too. — David Weinraub
– David Weinraub, Commented Feb 9, 2012 at 7:16
@DavidWeinraub Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND )' at line 1 — Edward Maya
– Edward Maya, Commented Feb 9, 2012 at 7:18

Kevin Smith · Accepted Answer · 2016-07-20 11:17:43Z

2

The BETWEEN clause is inclusive, the correct way is shown below:

$select = $DB->select()
         ->from('CALL_LOG', array('caller_name','call_number','call_start_time','call_duration','call_direction'))
         ->where('phone_service_id = ?', $phone_service_id)
         ->where("DATE_FORMAT(date_created, '%m/%d/%Y') >= ?",  $start_date)
         ->where("DATE_FORMAT(date_created, '%m/%d/%Y') <= ?",  $end_date)
         ->order("".$option_call_log." ".$option_call_log_asc_desc)
         ->limit(0,9);

try the above code and your problem will be solved.

edited Jul 20, 2016 at 11:17

Kevin Smith

133 bronze badges

answered Feb 9, 2012 at 7:21

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

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

2 Comments

Code Lღver Over a year ago

I have make change in the answer. So now check it.

Ray Over a year ago

If I'm not mistaken, this will not utilize any index on the date and would require a full table scan.

R T · Accepted Answer · 2013-02-21 12:25:43Z

0

zend db also used in magento so i found out the way.

$sql = $read->select()
                ->from(array('g' => $res->getTableName('geoloc')));

        $whereSql = "INET_ATON('{$remoteAddr}') BETWEEN g.start AND g.end";

        $sql = $sql->where($whereSql); //echo $sql;exit;
        $result = $read->fetchRow($sql);

the output will be like below:

SELECT g.* FROM geoloc AS g WHERE (INET_ATON('41.98.30.186') BETWEEN g.start AND g.end)

hope it helps.

answered Feb 21, 2013 at 12:25

R T

4,5993 gold badges40 silver badges51 bronze badges

Collectives™ on Stack Overflow

how to put between in Zend Framework query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related