Adding a condition to a SQL statement in PHP

Sina
Sina used Ask the Experts™
on
This is my first post/question in Expert Exchange: I have a PHP file (it's drupal and a custom module in drupal, but everything related to this question is happening between PHP and mysql DB), There is a function that I call which needs to go get a few columns from the DB (it will execute and then a 'for each' loop will put the returned values in to an array and return it) and it is working fine,
But I need to add a condition to the SQL statement (which sounds easy) but the statement is (I think) a prepared statement so I am not sure how to add the 'AND' condition here. The condition should limit the SQL output to those that have a date of past 3 months.

function is: function sbmod_user_webinars($type)
udi will have user's id in it because before our select we do: $uid = $user->uid;
Table name: sb_webinars_paid
Columns: paid_id (PK), mid, eid, uid, oid, line_item_id, path and timestamp (2014-11-02 12:44:58):
sample piece of the table querying..
Here is the SQL query in the function:

   $sql = db_select('sb_webinars_paid', 'uw');
   $sql->fields('uw', array($type));
   $sql->condition('uw.uid', $uid, '=');
// need to add a AND condition here, to check against date and only output past 3 months
   $result = $sql->execute();

I tried the code below but didn't work:
$sql->condition('DATE(uw.timestamp)', 'array(CURDATE() - INTERVAL 3 MONTH,CURDATE())', 'BETWEEN');

I appreciate any advice.
Thanks a lot,
Sina
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004

Commented:
Instead of using condition(), try using where().  I'm assuming this is Drupal 7.

https://api.drupal.org/api/drupal/includes%21database%21select.inc/function/SelectQuery%3A%3Awhere/7
I am not sure how db_select supports the BETWEEN statement, but between can be split into two statements. instead of
x between a and b
write
x > a and x < b
In drupal the equivalent will be

  $sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->condition('uw.uid', $uid, '=')
   ->condition('DATE(uw.timestamp)', '(CURDATE() - INTERVAL 3 MONTH)', '>')
   ->condition('DATE(uw.timestamp)', 'CURDATE()', '<');
   $result = $sql->execute();

If that fails, calculate php variables $startdate, $enddate and do

 $sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->condition('uw.uid', $uid, '=')
   ->condition('DATE(uw.timestamp)', $startdate, '>')
   ->condition('DATE(uw.timestamp)', $enddate, '<');
   $result = $sql->execute();
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
FYI
BETWEEN, in all SQL variants, INCLUDES both the lower and upper values

[column] between [low] and [high]
        is equivalent to
[column] >= [low] and [column] <= [high]

nb: "between" is really just a syntax shortcut of the >= and <= operators
see: "Beware of Between"
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

& I also believe you should use PHP date functions to calculate the date range

$sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->condition('uw.uid', $uid, '=')
   ->condition('uw.timestamp', $startdate, '>=')
   ->condition('uw.timestamp', $enddate, '<');  
   $result = $sql->execute();

nb: because you are using less than, make $enddate one day more than today, that way you do NOT need to alter every row of data using the DATE() function and this allows the query to use indexes on that column (better performance)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2004

Commented:

Author

Commented:
Thanks. I guess I can't individually reply here.

@Steve: Thanks, Your assumption is correct, this is Drupal 7. Do you mean I use 'where' instead of condition in both lines? I can try this solution.

@Shalomc: Thanks, I will try both of your suggestions and get back to you.

@Paul: Thanks Paul, you are correct, I also do not prefer to use between, I wasn't sure if listing multiple 'conditions' will cause 'and' operator between them, so I was trying to get it done in one line. (just wondering,  what if you need 'or' in your conditions).
Your solution is almost like what Shalmoc said, with the correction on checking for >= and <. I hope the interval function works. When mysql has these type of functions, I prefer to skip two extra calculations for start and end date.

I will try all the suggestions. and let you know. Thanks again.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I should have added that I know nothing about Drupal's api, so YES, my PHP coe snippet was just a small amendment to Shalomc's, apologies for not mentioning that.

I don't know if the conditions are additive or not, I was rather hoping that Shalmoc's snippet worked.

NB I was concentrating on the correct use of between, or, how to avoid using it which is better.
& Personally I think using PHP for calculation of the dates is better in this scenario.

Author

Commented:
Hi All,
Unfortunately Drupal is a strange fragile platform that there is more headache to it than anything else. So I spent 5 hours and tried the proposed solutions:

The solution with the interval keyword (unfortunately) did not work, although it works if you translate the same thing in to a SQL query in phpmyadmin, so it's not the code's issue but something between the way it runs in Drupal maybe.

Both CURDATE() and DATE() being used in the condition threw drupal off, and didn't work. So I decided to go with separate startdate and enddate as you also suggested, and no use of DATE() (more similar to Paul's code), to only deal with full timestamp (date and time):

$sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->condition('uw.uid', $uid, '=')
   ->condition('uw.timestamp', $startdate, '>=')
   ->condition('uw.timestamp', $enddate, '<');  
   $result = $sql->execute();

Here if you give it a fixed static date, like: $startdate = '2015-09-21'; and something to enddate (before the query), it works fine. So I am trying to figure out how to provide the startdate and enddate:

(It worked with both now() and Date()),  I am using:
$enddate = date('Y-m-d H:i:s');
and seems to be working fine (I am assuming this gives me full date and time).
But I am stuck at how would be the easiest to populate my startdate variable with a date which is 3 months or 90 days before current date and time? so I can use it later in the condition clause.

Appreciate your comments.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
try using strtotime() e.g.

$startdate = strtotime(date('Y-m-d H:i:s') . ' -90 days');

or

$startdate = strtotime(date('Y-m-d H:i:s') . ' -3 months');

but I'm not a PHP expert.

What you want for the SQL side of this problem is a date WITHOUT the time of day (or, the time is set to 00:00:00 +00000)

so, I'm not sure if you really need the  H:i:s


$startdate = strtotime(date('Y-m-d') . ' -90 days');

or

$startdate = strtotime(date('Y-m-d') . ' -3 months');

Also don't forget that if you want to include ALL of "today" in the query results then $enddate needs "plus 1 day" too because you are now using "less than" in the second condition
Top Expert 2004

Commented:
The difficulty you are encountering is because condition() is meant to take a field and a value.  Both are escaped, or made safe, in the database layer before Drupal executes the query.  As you found, the call will work fine when you are using a basic operator, such as '=' or '<'.  

If you're fine with that, then do exactly as your example demonstrates: set $enddate and $startdate, and pass them through the call to condition().  If you would prefer to use MySQL-specific functions like NOW(), you'll need to use a call to where() instead.  The where() function is designed to take an arbitrary conditional clause.  It is designed much like  parameterized query, but you'll still need to verify the SQL syntax yourself.

In the below example, the two calls are functionally equivalent:
//using condition
$sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->condition('uw.uid', $uid, '=');

//using where
$sql = db_select('sb_webinars_paid', 'uw')
   ->fields('uw', array($type))
   ->where('uw.uid = :uid', array(':uid'=>$uid));

Open in new window

Author

Commented:
Thanks all.
@Steve: thanks for the explanation. I understood that difference but still not sure why shouldn't Drupal using condition be able to run a one line between condition with the interval as we tried to do it. Because the way condition works it seems safer but both date() failed and the between command.
But to make this work I switched to the startdate and enddate as all you 3 suggested.

@Paul: Thanks for the comment. I finally made it work.
The timestamp column in that table has full date and full time (every other date column in this database has a mysql timestamp in this format: 123456789 but this one column has non-unix and readable date and time) so as date() in condition was failing and we were left to compare our start and enddate to this timestamp column, I used the code below (same thing as you said, just no s at the end of day and/or month):

 $startdate = date('Y-m-d H:i:s' , strtotime('-90 day'));  
 $enddate = date('Y-m-d H:i:s');

...
   ->condition('uw.timestamp', $startdate, '>=')
   ->condition('uw.timestamp', $enddate, '<=');  
   $result = $sql->execute();

I used = in both sides to make sure it covers both start date and end date (might mean 91 days) but that's OK for this program.

Thanks again.

Author

Commented:
day  or month shouldn't have 's' at the end.
combination of previous comment and this one solved the problem.
$startdate = strtotime(date('Y-m-d') . ' -90 day'); is correct.
Top Expert 2004

Commented:
>>> still not sure why shouldn't Drupal using condition be able to run a
>>> one line between condition with the interval as we tried to do it.

Because when you create a query condition, you're passing a field, a value, and an operator.  In order to make the resulting SQL safe, all three are run through a filtering process.  When calling condition(), the value is explicitly cast and escaped, meaning instead of creating "mydate = NOW()", it will create "mydate = 'NOW()'"  (note the interior single-quotes).  

On the other hand, calling where() requires only a snippet and any parameterized variables needed.  Drupal does not escape this, but rather dumps it into the resulting query verbatim, and binds the parameters appropriately.  In essence, it bypasses part of the Drupal process to make safe queries - the responsibility to create safe SQL then lies with the developer.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Sina I very pleased you got your solution but feel I only assisted

I still strongly recommend using >= and <

Initially you were using DATE([column]) >= lowval and DATE([column] <= highval

the impact of DATE() in that predicate is that you INCLUDE any records during the highval day

When you leave using DATE([column]) you only include the highval POINT in time and OMIT any other records during the highval day

The most precise method to include all rows during the enddate is as follows


$startdate = strtotime(date('Y-m-d') . ' -90 day');
$enddate = strtotime(date('Y-m-d') . ' +1 day');

   ->condition('uw.timestamp', $startdate, '>=')
   ->condition('uw.timestamp', $enddate, '<');  
   $result = $sql->execute();

this way you get all of endate and absolutely nothing after that date

Author

Commented:
Thanks for your comment.
I actually noticed that the result was failing to bring something from today. So a webinar I was purchasing today was not being returned when it was looking for 90 days ago until now. I'll try your suggestion but yesterday what I did to fix it was to comment out enddate, and only ask it to limit the result to >= start date. As I need the webinars list that user registered/paid for 90 days ago or less.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial