Left Join Issue

[perrij3]

I am having an issue with my left join SQL statement.

 

Here is what I have so far:

$sql = "SELECT pt.TimeID, pt.Time, pp.FK_UserID, pp.FK_TimeID, pp.PrayerDate, pu.UserID, pu.UserName  
  FROM time AS pt
  LEFT JOIN prayer AS pp 
  ON pt.TimeID = pp.FK_TimeID
  LEFT JOIN user AS pu
  ON pp.FK_UserID = pu.UserID";

 

The issue that I am having is I need to just retrieve information just for a certain date. Right now it will just displays the same information for each day, I need it to change depending on what day the user selects.

 

I'm just not sure where to insert the following code (if this is how one is suppose to do it). 

 

WHERE pp.PrayerTime = $date

 

I have tried placing it at the end of the above statement as well as right after 'FROM time AS pt'.

 

Could someone please help me figure out how to just display one date worth of information?

[perrij3]

OK, I got it to work somewhat when I added

WHERE (pp.PrayerDate = '$date'

to the bottom of the SQL statement.

 

Now, this has recreated another issue. I really don't know how to solve this problem. The have 3 tables in this database (time, user, prayer). Time has all the times one can pray during. User is just data about a user. Prayer is where I am storing information about when a person is going to pray.

 

What I am trying to do is when a person is signed up for a spot, to display their username. If nobody is signed up for this spot, I want it to say available. What it looks like is something like this:

 

12:00am  Available

12:20am  User1

12:40am  User2

1:00am    Available

and so on for the day.

 

With the SQL I have right now, it will only display where users have signed up for, but it will not show the times that are available. If I take out the WHERE statement, I will get all my time slots, but the same user will be displayed no matter what day I select. So the above example will display on every day, not just on the day that person is praying on.

 

Could someone help point me in the right direction on how to solve this problem?

 

Thanks for your time and help. 

[perrij3]

I forgot to add that the version of MySQL I am using is MySQL 5.0.81.

[fenway]

You might have to move that where clause to the join condition.

[perrij3]

Thanks Fenway for the help. What I needed to do was not use a WHERE statement, but instead use an AND statement. You were correct that I needed to place it in a different location than were I was using the WHERE statement.