CODEWITHSUNDEEP
phpmysqlsql
d.abyss
d.abyss

Reputation: 212

PHP SQL Query between 2 dates

I'm new to PHP and currently coding a system where staff members can record shifts, account dept can print the amounts to be paid etc

I need my table which is displayed to members of the account dept to be query-able with a date range. My code seems to work however it is only able to retrieve single dates, not the date range.

Here is the code:

$shiftdata = mysqli_query($connection, "SELECT * FROM tablename_shift INNER JOIN 
tablename_staff ON tablename_shift.uniqueid = tablename_staff.uniqueid WHERE
shift_date BETWEEN '".$_POST['to_date']."' AND '".$_POST['from_date']."'
ORDER by shiftid ASC")

As the code is working for single-date ranges, my initial thought was that the error might lie in how I'm storing the dates. I originally was storing them as VARCHAR(30) in a format of DD/MM/YY, and have since changed it to DATE in a format of YYYY/MM/DD to be compatible with how MySQL stores dates.

Any help would be appreciated.

Upvotes: 0

Views: 5067

Answers (3)

M I
M I

Reputation: 3682

you can try this as well.

$shiftdata = mysqli_query($connection, "SELECT * FROM tablename_shift INNER JOIN 
tablename_staff ON tablename_shift.uniqueid = tablename_staff.uniqueid WHERE
shift_date >= '".$_POST['to_date']."' AND  shift_date <='".$_POST['from_date']."'
ORDER by shiftid ASC")

Upvotes: 0

The Humble Rat
The Humble Rat

Reputation: 4696

Presumably the staff select a human readable date ie 07-01-2014, then you need to quiz the database using this date but in the mysql date format.

You will need to change the dates before doing the query, like so.

$startDate = date("Y-m-d", strtotime($_POST['from_date']));
$endDate = date("Y-m-d", strtotime($_POST['to_date']));

$shiftdata = mysqli_query($connection, "SELECT * FROM tablename_shift INNER JOIN
tablename_staff ON tablename_shift.uniqueid = tablename_staff.uniqueid WHERE 
shift_date BETWEEN '".$startDate."' AND '".$endDate."'
ORDER by shiftid ASC")

Your from date needs to be first in the query though!

Also beware of the dreaded mysql injections!

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Mysql between expects the first date to be smaller i.e. from - to, so try this 

$shiftdata = mysqli_query($connection, "SELECT * FROM tablename_shift INNER JOIN 
tablename_staff ON tablename_shift.uniqueid = tablename_staff.uniqueid WHERE
shift_date BETWEEN '".$_POST['from_date']."' AND '".$_POST['to_date']."'
ORDER by shiftid ASC")

Upvotes: 1

Related Questions