Using php DateTime object in mysql query

Question

I'm trying to create a query that pulls information about sellers from my database, but only if their store has launched in the last 3 days. The easiest way I can think of to calculate the date for the query is using a new DateTime() object. When I output my code to test it, it's in the proper string for MySQL to query it with, but whenever I try to bind the variable, I get an error. I'm using Zend_Db to query, (PDO adapter)

action:

public function indexAction()
{
    $dateMod = new DateTime();
    $dateMod->modify('-2 days');
    $dateMod->format('Y-m-d H:i:s');


    // get sellers initialized in last 3 days
    $sellerTable = new Application_Model_DbTable_Sellers();
    $select = $sellerTable->select()->setIntegrityCheck(false);
     $select->from(array('s' => 'seller'),array('sellerID', 'businessName'));
   // select firstName, lastName, picture from user table, and businessName and sellerID from seller table.  
   $select->join(array('u' => 'user'), 's.userID = u.userID', array('firstName', 'lastName', 'picture'));
   $select->where('s.active = 1 AND s.contentApproval = 1 AND s.paymentApproval = 1 AND s.featured = 1');
   $select->where('s.launchDate > ?', $dateMod);
   $select->order('s.launchDate DESC');
   $newSellers = $sellerTable->fetchAll($select);

When I assign $dateMod to the view, it outputs the correct Y-m-d H:i:s format. But when I plug it into the query, I get the following error:

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 ') ORDER BY `b`.`launchDate` DESC' at line 2

If I hardcode a value into dateMod in the mysql timestamp format, the query works fine. How can I access just the string value of the timestamp in the DateTime object? getTimestamp returns a unix formatted timestamp, even after assigning a format.

why not use mysql ADDDATE roseindia.net/sql/sqldate/sql-date-add.shtml — Grumpy
– Grumpy, Commented May 7, 2012 at 21:32
I typically avoid using the mysql functions for dates. I've found clients often change their minds about date functions, and putting it directly in the SQL means a lot of edits when the change is made. If I do it with PHP, I can change an add time function to a sub time function just by passing a different parameter, and assign that to a front end feature the site admin can assign, and I can use the calculation very easily in the view. But yes, that would work. — Brian Vanderbusch
– Brian Vanderbusch, Commented May 7, 2012 at 21:48

Tim Fountain · Accepted Answer · 2012-05-07 21:45:25Z

3

The format() function returns the formatted date, so you need to assign that to a variable for use in the query:

$dateFormatted = $dateMod->format('Y-m-d H:i:s');

$select->where('s.launchDate > ?', $dateFormatted);

answered May 7, 2012 at 21:45

Tim Fountain

33.2k5 gold badges44 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Using php DateTime object in mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related