How to get data from Mysql between two specific dates

Question

I want to get data from Mysql With Php between 2 dates. I am unable to get this.. Here are my Codes. These Lines are for Date Interval...

date_default_timezone_set("EST");
$today = date('d-m-Y');
$date_to = $today;
$date_to = strtotime("-7 days", strtotime($date_to)); //-7 days for last week. -30 for last week
$date_to = date("d-m-Y", $date_to);

Now Here are the Queries Which i am running.. I have two conditions. 1 The Data should be between $today and $date_to dates. And assigned should be equal to 1. Assigned is a column in Database table 'requests'.

$get_req = "SELECT * FROM `requests` WHERE `req_date` between '$today' AND '$date_to' AND `assigned`='1'";
$result = mysqli_query($dbc, $get_req);
while ($res = mysqli_fetch_assoc($result)){
    $driver_id = $res['assigned_driver_id'];
    $req_id = $res['req_id'];
    $req_title = $res['request_title'];
    $req_price = $res['price'];
    $req_time = $res['request_time'];
    $req_date = $res['req_date'];
    $driver = $res['driver_name'];
}

Here i am unable to get my required result. Problem is Only in Date query. I mean in Between Date A to Date B. req_date` between '$today' AND '$date_to' It works Perfect.

Please advise me what i should do to fix it. I want to get the Records of Past 7 Days only.

Is req_date in d-m-Y format? I'd think it would be a date or datetime column in YYYY-MM-DD (mysql date format, not PHP date codes). — chris85
– chris85, Commented Jul 11, 2015 at 18:06
What is the column type? dev.mysql.com/doc/refman/5.6/en/datetime.html If it is date/datetime you could use some predefined functions to calculate out week, month etc. stackoverflow.com/questions/7391718/… — chris85
– chris85, Commented Jul 11, 2015 at 18:13
So that is why between isn't working. As it currently stands mysql just thinks it is text. It should be a date or datetime column (you will need to update existing data). Maybe this would be useful, stackoverflow.com/questions/15396058/… — chris85
– chris85, Commented Jul 11, 2015 at 18:24

Mindastic · Accepted Answer · 2015-07-11 18:14:54Z

2

I don't know what's your DB structure, but you could change your code and do something like:

Your PHP Code

date_default_timezone_set("EST");
$today = time();
$date_to = strtotime("-7 days"); //-7 days for last week. -30 for last week

And your query:

$get_req = "SELECT * FROM `requests` WHERE UNIX_TIMESTAMP(`req_date`) between '$today' AND '$date_to' AND `assigned`='1'";

answered Jul 11, 2015 at 18:14

Mindastic

4,1413 gold badges21 silver badges20 bronze badges

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

2 Comments

Ali Obeid Over a year ago

$date_to is displayed in what structure? ex: yyyy-MM-dd hh:mm:ss ?

Mindastic Over a year ago

strtotime returns a unix timestamp. Doc here: php.net/manual/en/function.strtotime.php

Pratik Soni · Accepted Answer · 2015-07-11 18:19:28Z

0

Here is your solution. You need to change the query like this.

STR_TO_DATE(yourdatefield, '%d-%m-%Y') > CURDATE() - INTERVAL 7 DAYS

answered Jul 11, 2015 at 18:19

Pratik Soni

2,57618 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to get data from Mysql between two specific dates

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related