CODEWITHSUNDEEP
phpmysqlsql
user860511
user860511

Reputation:

MYSQL Date range query not working correctly

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.

Upvotes: 0

Views: 2383

Answers (4)

mpratt
mpratt

Reputation: 1608

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";    
}

Upvotes: 1

Paul S.
Paul S.

Reputation: 1537

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";
 }

Upvotes: 0

user898741
user898741

Reputation:

May this is the right way ?

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

Upvotes: 0

smp7d
smp7d

Reputation: 5032

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.

Upvotes: 1

Related Questions