PHP SQL Query between 2 dates

Question

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.

You're code is extremely vulnerable. Regardless of that, that should work as long as your post variables (which again I'm pointing out should not be put directly in the query without sanitation) are in the correct format. — Ryan
– Ryan, Commented Jan 7, 2014 at 13:37
How is your table structure now...are you now storing them in a date type field instead of a varchar...are they storing just fine now...and the old values, did you convert them to the new format? — Hackerman
– Hackerman, Commented Jan 7, 2014 at 13:38
If you know how I can improve the vulnerability of my code, please elaborate. — d.abyss
– d.abyss, Commented Jan 7, 2014 at 13:43
That is a widely discussed topic. See prepared statements and sql injection — Strawberry
– Strawberry, Commented Jan 7, 2014 at 13:44

Abhik Chakraborty · Accepted Answer · 2014-01-07 13:38:47Z

1

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")

answered Jan 7, 2014 at 13:38

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

1 Comment

d.abyss Over a year ago

Thank you, this worked. I had it working with WHERE shift_date > '".$_POST['from_date']."' AND shift_date < '".$_POST['to_date'] but I prefer this way.

The Humble Rat · Accepted Answer · 2014-01-07 13:39:35Z

0

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!

answered Jan 7, 2014 at 13:39

The Humble Rat

4,7468 gold badges44 silver badges74 bronze badges

Comments

M I · Accepted Answer · 2014-01-07 13:41:13Z

0

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")

answered Jan 7, 2014 at 13:41

M I

3,6822 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

PHP SQL Query between 2 dates

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related