Gilbert Posted March 7, 2018 Share Posted March 7, 2018 Hi – I’m having a lot of trouble with a where clause with a date in my sql query. It seems I have tried everything and read many articles & blogs, but I can’t get it working. I understand the data type is very important to match up and the format also. Anyway I’ll tell you what I’ve got and see if anyone can help me. I’m joining 3 tables in my query – everything works fine and I get the results I want UNTIL I add the where clause. Unless there is something I need to change because of the where clause, I shouldn’t need advice on that. Tables Fields Kiosks – kioskID, workDate, locationID, workerID, bills, coins - workDate is type Date Locations - locationID, storeName, branchName, locationAbbrev Workers - workerID, firstName, lastName, phoneNum $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID”; This part works fine and I get a long list showing the Date, Location, Worker, and Amount using PHP. BUT,(everybody’s got a big BUTT) when I tack on the Where clause – kapoof!! When I try different formats I get either 0 results or unable to process messages. Here are a couple of things I’ve tried – hopefully someone can spot the error in my ways. Thanks $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID WHERE Kiosks.workDate = '2017-12-05'"; Also tried without quotes like = 2017-12-05 $sql = "SELECT Kiosks.kioskID, Kiosks.workDate, Locations.locationAbbrev, CONCAT(Workers.firstName, ' ', Workers.lastName) AS workerName, Kiosks.bills + Kiosks.coins AS amount FROM Locations INNER JOIN Kiosks ON Locations.locationID = Kiosks.locationID INNER JOIN Workers ON Kiosks.workerID = Workers.workerID WHERE year(Kiosks.workDate) = 2017 AND month(Kiosks.workDate) = 12 AND day(Kiosks.workDate) = 5"; Link to comment Share on other sites More sharing options...
Ingolme Posted March 7, 2018 Share Posted March 7, 2018 What happens when you run the query? Do you get zero rows or is there an error occurring? My first guess is that there are no rows with workDate = '2017-12-05' To begin debugging this, make sure that PHP is catching the errors and printing them, then check the database for yourself to see if there are actually rows that match your search criteria. Link to comment Share on other sites More sharing options...
Gilbert Posted March 12, 2018 Author Share Posted March 12, 2018 Hi Ingolme, I'm sorry I didn't get an alert in my email when you answered. I definitely have rows in the table that match the date - it looks just like the '2017-12-05' - and it is of type 'date' as I stated. The query returns the whole list with all dates when I run it without the WHERE clause. I was wondering if there was another syntax of 'workdate that I should be using. AHHHH! I think I just figured it out - I think I have the Joins in the wrong order because I'm trying to get the date which is in the 'Kiosks' table and I'm starting with 'Locations'. I'm going to try it and let you know.....Hooray! Hoorah! It's alive!!! I hope I helped someone else who might look at this. Thanx, Gil Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now