0

I have a problem getting certain data from my database by querying a date range. In my database i have a DATE type field within the format YYYY-MM-DD. I want to get all data within a date range of today + 2 weeks (Expiring).

I have wrote:

$format = 'Y-m-j'; 
$date = date ( $format ); 
$new = date ( $format, strtotime ( '+14 day' . $date ) );   
$start = date("Y-m-d", strtotime($new));        
$today = date('Y-m-d');
$q = "SELECT * FROM listing WHERE dd_end BETWEEN '".$today."' AND '".$start."'";
    while($row = mysql_fetch_assoc($q)){
      $listing_id = $row['listing_id'];
      echo "$listing_id";
}

So what I want to achieve is for the query to pull all the rows from now until 5th October. I've echo'd the variables and they show they're in the correct form (YYYY-MM-DD) to compare within the database but no results are returning.

Any help would be greatly appreciated. Many thanks in return.

Improve this question
4
  • 1
    We'll need to know (1) the datatype of column dd_end and (2) what the text of $q is before it gets sent to the database. You should also be using a parameterized query for safety purposes and doing would make it much, much easier to get this statement correct (since you wouldn't have to worry about date formatting). Commented Sep 21, 2011 at 19:43
  • Datatype of column dd_end is DATE. I echoed the $q variable and returns - SELECT * FROM listing WHERE dd_end BETWEEN '2011-09-21' AND '2011-10-05' . I'm unsure of parameterized queries unfortunately. Commented Sep 21, 2011 at 19:45
  • It should be noted that your query is potentially vulnerable to SQL injection, if future maintainence exposes parameters directly to outside input. If you haven't already, look up parameterized queries. Depending on how PHP deals with the mySQL dialect, this may also solve any data-type issues. Commented Sep 21, 2011 at 20:01
  • 1
    It should also be noted that the OP is NOT calling mysql_query() and is trying to fetch directly from the SQL statement string. Commented Sep 21, 2011 at 20:07

4 Answers 4

Reset to default
1

Well, assuming that the mysql database has the same date that your server, you could let the mysql database do all the date calculations.

A little something like this:

SELECT * 
FROM listing 
WHERE dd_end BETWEEN CURDATE() AND (CURDATE() + INTERVAL 14 DAY)

On the other hand, i think Paul S's answer may fix your problem.

Edit: You forgot to call mysql_query before the mysql_fetch_assoc() function.

$result = mysql_query($q);
while ($row = mysql_fetch_assoc($result))
{
   $listing_id = $row['listing_id'];
   echo "$listing_id";    
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I implemented both Paul S's and your answer on two separate occasions and it's still returning nothing with the query, surely the code above works perfectly fine, I dont understand why its returning none?
Oh i See. Can you do a mysql_query and then the mysql_fetch_assoc? "$result = mysql_query($q); while($row = mysql_fetch_assoc($result)) { print_r($row); }"
$q = mysql_query("SELECT * FROM listing WHERE dd_end BETWEEN CURDATE() AND (CURDATE() + INTERVAL 14 DAY"); while($row = mysql_fetch_assoc($q)){ $listing_id = $row['listing_id']; echo "$listing_id"; } Still returning 0 values?
I implemented your above example and nothing is returned, so frustrating!
You forgot to close the parenthesis inside the query string. ... mysql_query("SELECT * FROM listing WHERE dd_end BETWEEN CURDATE() AND (CURDATE() + INTERVAL 14 DAY ) ");...
|
1

If dd_end is a date you may want to read a certain section on the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

Improve this answer

Comments

0

May this is the right way ?

$start = date("Y-m-d", strtotime('+14 day' . $date));
Improve this answer

Comments

0

Read:

http://php.net/manual/en/function.strtotime.php

strtotime has a second argument.

 $format = 'Y-m-j'; 
 $date = date ( $format ); 
 $new = date ( $format, strtotime ( '+14 day' . $date ) );  
 $start = date("Y-m-d", strtotime($new));

Should be:

 $new = strtotime('+14 day', time());  
 $start = date("Y-m-d", $new);
 $today = date('Y-m-d');
 $q = mysql_query("SELECT * FROM listing WHERE dd_end BETWEEN '".$today."' AND '".$start."'");
 while($row = mysql_fetch_assoc($q)){
     $listing_id = $row['listing_id'];
     echo "$listing_id";
 }
Improve this answer

4 Comments

I've implemented this and it is still returning nothing from the query. Echoing the query returns - SELECT * FROM listing WHERE dd_end BETWEEN '2011-09-21' AND '2011-10-05' which seems perfectly fine?
Syntax looks correct, are you sure you have data between those dates? Try running the query with dates ranging in years (aka 2010-2011)
Absolutely positive, this is copied directly from the row it should be pulling - 2011-10-02 . So i'm 3 days into the query and quite rightly this should be pulled?
Edited to include mysql_query per Marc's observation. Try the code now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.