php select all bookings on and between certain dates

0
votes

I have a query that is supposed to select all bookings between dates

 SELECT t1.*, treatment.treatmentName, 
t2.userForename, t2.userSurname, 
t3.userForename as clientForename, t3.userSurname as clientSurname, t3.userPost as clientPost, t3.userAd1 as clientAd1,  
t4.userForename as createdForename, t4.userSurname as createdSurname 
            FROM booking t1 
            INNER JOIN treatment ON t1.idtreatment = treatment.idtreatment
            INNER JOIN user t2 ON t2.iduser = t1.idstylist
            INNER JOIN user t3 ON t3.iduser = t1.iduser
            INNER JOIN user t4 ON t4.iduser = t1.bookingCreatedBy
             WHERE 
             t1.bookingDate>=%s AND t1.bookingDate<=%s)  
             ORDER BY t1.bookingDate ASC, t1.bookingSTime ASC, t1.idtreatment ASC

If I pass the query a start and end date like so....

 SELECT t1.*, treatment.treatmentName, 
t2.userForename, t2.userSurname,
 t3.userForename as clientForename, t3.userSurname as clientSurname, t3.userPost as clientPost, t3.userAd1 as clientAd1, 
t4.userForename as createdForename, t4.userSurname as createdSurname 
 FROM booking t1 
INNER JOIN treatment ON t1.idtreatment = treatment.idtreatment 
INNER JOIN user t2 ON t2.iduser = t1.idstylist 
INNER JOIN user t3 ON t3.iduser = t1.iduser 
INNER JOIN user t4 ON t4.iduser = t1.bookingCreatedBy 
WHERE t1.bookingDate>='2017-01-01' AND t1.bookingDate<='2017-01-07' 
ORDER BY t1.bookingDate ASC, t1.bookingSTime ASC, t1.idtreatment ASC

it seems to be returning results outside of that range if I echo the dates of each row returned. for example I'm getting in my result set...

07/09/2016, 07/09/2016, 26/09/2016, 07/09/2016, 07/09/2016, 05/01/2017, 07/09/2016, 20/09/2016, 07/09/2016

as you can see theres only actually one thats right in my array thats returned.

3
phpmysqldateinner-join
Is bookingDate a DATE or a DATETIME column? – RiggsFolly
@RiggsFolly it is a date column – Daniel Robinson
Ok so which date column are you displaying when you show these dates – RiggsFolly
@RiggsFolly you've had just solved the problem. I am displaying the booking created date not the actual booking date. Thank you. I've been going over this for about 1 hour!!!!! please submit your answer so I can accept. – Daniel Robinson
Ok, its not really my best ever answer but there you go – RiggsFolly

3 Answers

1
votes

Just a thought, what date column are you displaying when you say the date are wrong?

Make sure it is t1.bookingDate

0
votes

This is too long for a comment. Your "template" query looks has this:

        WHERE t1.bookingDate>=%s AND t1.bookingDate<=%s) 
-------------------------------------------------------^

The closing paren is a syntax error. So, you shouldn't be getting any results.

The query that you claim to be running is:

WHERE t1.bookingDate>='2017-01-01' AND t1.bookingDate<='2017-01-07'

The closing paren has mysteriously disappeared.

For this type of problem, the first assumption is the datatypes. However, you say that the date is really a date. A second possibility is more clauses in the WHERE. If you have an OR, then you will get unexpected results. So, enclose this fully in parentheses:

        WHERE (t1.bookingDate >= %s AND t1.bookingDate <= %s)
-1
votes

You can try to replace WHERE t1.bookingDate>=%s AND t1.bookingDate<=%s to WHERE t1.bookingDate between %s and %s