views:

51

answers:

2

I have a table in my MySQL database that looks like the following

banner
--------
 id  : bigint(20)
name : varchar(75)
type : set('news','event')
link : bigint(20)
when : int(10)

What I want to do is I want to be able to recall all the rows with the type of "news"
but I only want to recall all of the rows with the type of "event" where 'when > time()'
or basically any event that takes place in the future

RECAP:

  • recall all NEWS items
  • recall only EVENTS that happen in the future

I'm running the SQL query using PHP as the scripting language.

Thanks all

+1  A: 
SELECT * 
FROM banner 
WHERE type = "news" 
    OR (type = "event" AND when > NOW())
Lukasz Lysik
i tried that but its still outputing the old events
DBunting
Would you send some data from the table?
Lukasz Lysik
If the condition '`when > NOW()`' is valid, then the query should work. However, your `when` column is storing a 10-digit integer, but NOW() returns a DATETIME value. I suspect that the trouble is that the comparison is not working as you expect - you should change 'when' to a date/time type (maybe DATETIME, maybe TIMESTAMP).
Jonathan Leffler
i have tried using when as a Timestamp but for whatever reason when i do that it is stored as a formated date instead of a raw timestamp. I dont know if that is something with the MySQL build im using or what
DBunting
A: 

i had tried something akin to what Lukasz Lysik suggested but was still getting past events.

i think the following solved it

SELECT * 
FROM `banner` 
WHERE IF(`type` = 'event', `when` > NOW(), 1) 
ORDER BY `when` DESC
DBunting
im hoping im understanding this usage rightWHERE IF( *CONDITION* , *USE IF TRUE* , *USE IF FALSE* )
DBunting
That has to be some of the least comprehensible notation imaginable. The OR version above is far clearer.
Jonathan Leffler
im more concerned with proper functionality then clean code. If it doesnt do what i need it to i couldnt care less how clean it is
DBunting