using where clause with date not working

[Gilbert]

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

 

[Ingolme]

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.

[Gilbert]

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