CODEWITHSUNDEEP
phpsql
Hugo Cornellier
Hugo Cornellier

Reputation: 161

Filtering by date range in SQL query

I am unable to get the following code to work:

    // dd/mm/yyyy for dates in SQL queries
    $todayforw = date('d/m/Y');
    $aweekago  = date('d/m/Y', time() - 604800);

    $week_e_check = mysql_query("SELECT * FROM earningslog WHERE user_id = '".$info['id']."' WHERE day >='".$aweekago."' AND day <'".$todayforw."'");

    while ($week_e_info = mysql_fetch_array($week_e_check)) {
        $week_e = $week_e + $week_e_info['user_earnings_amnt'];
    }

The query returns zero rows, however, it should be returning data that matches the criteria.

Upvotes: 1

Views: 2697

Answers (4)

ronalchn
ronalchn

Reputation: 12335

Try change the format of your strings from from d/m/Y to Y-m-d.

MySQL might be expecting it year first. In which case it could be doing the wrong thing with d/m/Y.

Also don't use the WHERE clause twice. Instead, combine conditions using AND, eg:

WHERE      user_id = '".$info['id']."'
       AND day >='".$aweekago."'
       AND day <'".$todayforw."'

By the way, you can also try saying WHERE day BETWEEN ".$aweekago." AND ".$todayforw.", which might be easier syntax to read (as long as you change $todayforw to be the day before).

Upvotes: 1

Robert
Robert

Reputation: 8767

Your issue appears to be with your query syntax. You are stating WHERE twice, whereas you should only state it once and then use the AND or OR operators for further criteria. I would also suggest that you either move your statement into a variable or use die() to assist with debugging.

 $week_e_check = mysql_query("SELECT * FROM earningslog WHERE user_id = '".$info['id']."' AND day >='".$aweekago."' AND day <'".$todayforw."'") or die(mysql_error());

In addition, you should not be using the mysql extension as use of this extension is discouraged. Instead, use the MySQLi or PDO_MySQL extension. Using one of these alternative extensions will help serve as the first step in preventing SQL injection. I would also suggest that you avoid using * and specify the column names to be returned instead.

Using PDO:

<?php

/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT * FROM earningslog WHERE user_id = ? AND day >= ? AND day < ?');
$sth->execute(array($info['id'], $aweekago, $todayforw));
$results = $sth->fetchAll();

?>

Upvotes: 1

GKlesczewski
GKlesczewski

Reputation: 302

Take a closer look at your query:

  SELECT * FROM earningslog WHERE user_id = '".$info['id']."' WHERE day >='".$aweekago."' AND day <'".$todayforw."'"

Your Where clause appears twice.

Two things to think about - when you are selecting data, try and stay away from select * - you may get unexpected results of the table is ever modified.

Second, try and create the query as a parameterized query, instead of injecting the parameters directly into the where clause. By directly injecting your criteria the way you have, you are opening yourself up to a SQL injection attack.

By turning it into a parameterized query, you get the side benefit of being able to debug the queries directly against the database, reducing the amount of effort needed to copy it from a query tool into your code.

Upvotes: 1

hsanders
hsanders

Reputation: 1898

Check your date format:

Should be:

YYYY-mm-dd HH:mm:ss

E.G. 2012-01-01 00:00:00 (January 1, 2012 at midnight local time)

Other date formats MAY work, but the best way to go about it is to use the same format that MySQL uses when they display the date, that's the only way I know that works every time.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Also your syntax is incorrect, you have two wheres, you should use AND.

Upvotes: 1

Related Questions