[SOLVED] IF statement with multiple arguments in query

[markkanning]

Hello all,

Before I go cross-eyed Googling this query(pun-intended), I figured I'd let the "freaks" take a crack at it...

I know you can have an IF statement inside a MySQL query such as:

SELECT IF(var1 > var2, do this, or do this) WHERE var1 = 'puppies'

But can you have multiple arguments for that IF statement, like this:

SELECT IF(var1 > var2 AND var2='kitties', do this, or do this) WHERE var1 = 'puppies'

I can't find a reference to, or an example of, this anywhere!!! The future of the free world depends on it!!!! Okay...maybe just my job!!!!!

 

Mark

[Barand]

So long as the first argument results in a true or false value

[markkanning]

Certainly. Is that the correct syntax, however?

[Barand]

Looks fine.

 

As with WHERE conditions, if you are going to use a mix of AND and OR, use (...) to make the logic explicit.

 

eg

A AND B OR C

 

can be

 

A AND (B OR C)

 

or

 

(A AND B) OR C

[markkanning]

With that in mind, maybe you can tell me if my approach on this is correct...sorry if this gets a little wordy:The crux of the situation is I've got timeslots for an item(starttime - endtime) being pulled from the db, and I need to determine if "Now", determined by a php time() variable, falls inside a particular timeslot. The problem is that these times are "dumb" to what day it is, meaning they are timeslots that happen on a day-to-day basis, requiring only the 24-hour time data(like 03:00:00). As a result, timeslots going over midnight start getting goofy because I have to in some way attribute a day to them. I'm trying do this(and here's where the MySQL If statements come in...) by amending one of 3 dynamically created variables for "yesterday", "today" and "tomorrow" to either the start or end time depending on whether "Now" is in the AM or PM.

 

Yeah, I know. That's a lotta juggling.

 

The query looks, more or less, like this:

SELECT * FROM table 
-> WHERE  '".$now."' BETWEEN 
-> IF(EndTime < StartTime AND TIME('".$now."') >= '00:00:00' 
-> AND TIME('".$now."') <= '11:59:59', CONCAT_WS(' ', '".$yesterday."', StartTime), CONCAT_WS(' ', '".$today."', StartTime)) 
-> AND IF(EndTime < StartTime, CONCAT_WS(' ', '".$tomorrow."', EndTime), CONCAT_WS(' ', '".$today."', EndTime))

 

Does anything look syntactically unsound?

[fenway]

Not really... but you could just prepend the date piece, and have mysql do the work for you.

[markkanning]

True enough. The really goofy part, however, is that prepending the start/end time variables with the "yesterday/today/tomorrow" variable in PHP assumes that I know if the timeslot falls over midnight but I won't know that until I look for a timeslot in the database first. I should clarify, and forgive me if you had already figured this out, that I'm determining a "falls-over-midnight" status by first determining if the EndTime is smaller than the StartTime...hence, the day variable juggling. That can only be discovered by a query.

 

Am I missing something obvious you're trying to tell me? This would not be unlikely.

[Barand]

I don't know if this will help you but I do a lot of work with passenger timetables.

 

Suppose a train begins it's journey at 11pm and arrives at its destination at 4am. In the datebase I'd hold this as

 

2300 - 2800

 

This way the end time is greater than start time to stop things getting "goofy".

 

When output in public timetables, times over 2400 have 2400 subtracted, so the timetable shows

 

23:00 - 04:00

[fenway]

True enough. The really goofy part, however, is that prepending the start/end time variables with the "yesterday/today/tomorrow" variable in PHP assumes that I know if the timeslot falls over midnight but I won't know that until I look for a timeslot in the database first. I should clarify, and forgive me if you had already figured this out, that I'm determining a "falls-over-midnight" status by first determining if the EndTime is smaller than the StartTime...hence, the day variable juggling. That can only be discovered by a query.

 

Am I missing something obvious you're trying to tell me? This would not be unlikely.

 

If you assigned today's date to start always, and then adjust that time by adding  the difference from the end to the start, this will auto-correct itself -- if it's negative (i.e. ends after midnight), it will "subtract a negative" and add this number of hours, which mysql will notice and push the date for you.

 

[markkanning]

LOL - fenway, you just killed a couple of my brain cells! I ALMOST see what you're saying. Can you give me an example?

 

By the way, Barand, thanks for the suggestion. I wish I had done that to begin with. Unfortunately, we've got way too much data in the db to revert to that now.

[fenway]

LOL - fenway, you just killed a couple of my brain cells! I ALMOST see what you're saying. Can you give me an example?

 

Assume we're dealing with 23:00 - 04:00.

 

If you convert 23:00 into 2007-09-24 23:00, and subtract the time difference [ e.g. TIMEDIFF( end, start ) ] calculated in hours as the interval offset -- which would give -7 -- your expression would be "2007-09-24 23:00 - INTERVAL -7 HOUR", which mysql will happily report back as "2007-09-25 04:00".  Isnt' that what you want?

[markkanning]

Yes, YES!! The TIMEDIFF() function makes the freakin' difference...so to speak. Alright! I'm gonna give this a go, fenway. Thanks so much!