AS expression contains an IF statement - not working

[dmirsch]

I am a beginner to PHP/MySQL and have just reformatted our website to this on a UNIX platform. Usually when I use an ACCESS database to create a SQL statement I can figure out how to fix a syntax error in MySQL. This time I am stumped. Here is my ACCESS sql statement:

SELECT DISTINCT venues.VenueCode, venues.VenueName, IIf([startDateTime]>=Now()-1,True,False) AS ShowVenue
FROM venues LEFT JOIN Performance ON venues.VenueCode=Performance.VenueCode
WHERE (((venues.VenueCode)<>"VV") AND ((IIf([startDateTime]>=Now()-1,True,False))=True))
ORDER BY venues.VenueName;

 

When I look up how to format IF statements in MySQL, I realize that that is a portion of the query I will have to change, but I'm not exactly sure how to do so and still retain term "ShowVenue." Also I know how to clean up my WHERE clause and have ended up with the following:

SELECT DISTINCT venues.VenueCode, venues.VenueName, IF(([Performance.startDateTime]>=Now() - INTERVAL 1 DAY),"True","False") AS ShowVenue
FROM venues LEFT JOIN Performance ON venues.VenueCode = Performance.VenueCode
WHERE venues.VenueCode!="VV" AND ShowVenue == "True"
ORDER BY venues.VenueName

 

Please help a struggling beginner 

[dmirsch]

For more information, I am using MySQL 5.0, and the following is the error message I receive:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Performance.startDateTime]>=Now() - INTERVAL 1 DAY),"True","False") AS ShowVenu' at line 1

[Pandemikk]

The problem lies here:

IF(([Performance.startDateTime]>=Now() - INTERVAL 1 DAY),"True","False")

 

It's converting > and " to html entities which makes me think there's a problem with how you're having that condition used in the actual query. I'm confused about some of the stuff you're using in the query as well. "[Performance.startDateTime]>=Now() - INTERVAL 1 DAY". It looks like you're trying to use .asp in a query? That's not going to work out.

 

IF(([Performance.startDateTime]>=Now() - INTERVAL 1 DAY),"True","False")

[dmirsch]

Basically what I have is two different tables within my database (Performance) and (venues). One venueName could have multiple Performance.startDateTime. What I would like is to create a dropdown menu where IF there is at least ONE Performance.startDateTime in a venueName, then list the venueName in my dropdown menu. Currently, my dropdown menu contains a venueName for EVERY Performance.startDateTime --> hence multiple listings of the same venueName.

 

You can see this in my work-in-progress webpage at http://www.myalaskacenter.com/calendars/calendarSample.php. This is not a final document, so not much works at this time.

 

Anyway, when I did it in ACCESS it allowed me to make an expression called ShowVenue that was a TRUE/FALSE value and then in my WHERE clause I could display all the cases where ShowVenue was TRUE. Since it was a DISTINCT selection, then I had a clean listing for my dropdown menu.

 

If you or someone else could lead me in the right direction to accomplish this in MySQL, I would be very grateful. I have a feeling that when the answer is posted I will be hitting my forehead.

[Pikachu2000]

Also, get rid of the square brackets in the query string.

[dmirsch]

OK, so now I have

SELECT DISTINCT venues.VenueCode, venues.VenueName, IF(Performance.startDateTime >= Now()),'True','False') AS ShowVenue
FROM venues LEFT JOIN Performance ON venues.VenueCode = Performance.VenueCode
WHERE venues.VenueCode!="VV" AND ShowVenue == 'True'
ORDER BY venues.VenueName

and it still does not work.

 

Any ideas?

[fenway]

Are there errors now?

[dmirsch]

yes, it's the same error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),'True','False') AS ShowVenue FROM venues LEFT JOIN Performance ON venues.Venu' at line 1

[Pandemikk]

You really need to pay attention to what you're doing. You have an unnecessary parenthesis which is terminating your if statement prematurely.

 

IF(Performance.startDateTime >= Now(), 'True', 'False')

[dmirsch]

I actually did notice that and had removed the parenthesis, but I am still getting the same error message.

[DavidAM]

You don't really need the calculation in the SELECT clause. And you don't need the LEFT Join (as that will return Venues that have NO performance records -- although the WHERE clause is going to remove them). I think the query below will work, I don't have a system to test it on here.

 

SELECT DISTINCT venues.VenueCode, venues.VenueName
FROM venues JOIN Performance ON venues.VenueCode = Performance.VenueCode
WHERE venues.VenueCode!="VV" 
AND Performance.startDateTime >= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY venues.VenueName

 

Be aware that NOW() returns a DateTime, so the result may be different at different times of the day. You might consider using CURRENT_DATE() instead.

[dmirsch]

OK, that seems to work better, but an error still occurs...

#1046 - No database selected

[Pandemikk]

How do you expect us to solve that error? Why don't you read what it says and connect to a database.

[dmirsch]

OK, Sorry that did work! Thanks so much for the help!!!

I was typing the SQL statement directly into PHP/MyAdmin and didn't realize that I had not clicked on the database...my bad.