Jump to content

using where clause with date not working


Recommended Posts

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...