Nested Select Statement

[AlexanderGrim]

Hi guys,

I am working on a feature for my website (MyRoladex.com) where i have a script run through the database once per hour, look to see who has events scheduled that are within the next hour, then look to see what their email/sms/text message preferences are, then email/text them a notification that it is close to time for their scheduled event.

Under the Accounts table, i have 5 notable fields: uid, email, mobile, timezone, messagetype

Under the DatebookEvents table, the important field is, eventdate (besides 'uid', obviously), which is is YYYY-MM-DD HH-mm-SS format.

I did not want to calculate the time zone difference for each user, and then compare it to each of the event dates, and then to the system time, just to see if a notification should be sent, as this would be an immense waste of cpu and bandwidth. The next best thing that i though of was to STORE the event date PLUS or MINUS the time zone difference, whenever the user creates a new event, and then, whenever the user displays their events, it would ADD or SUBTRACT the time zone to the event date, so that it will display correctly for them, AND when the cron job runs once every hour, it will ONLY have to compare the eventdate field with the system time. This is better because the cron job will run once per hour, and have to work with, perhaps, thousands of records, whereas a user may only display his records once or twice per day, thus saving cpu and bandwidth overhead

When i was writing the query for the displaying of event details, where i take the event date for the entry they are viewing and ADD or SUBTRACT their time zone to it so that it displays properly according to their time, i came across this problem:

Select eid,(eventdate + hour(select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) from DatebookScheduler where uid='666'

Returns:

#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 'select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) ' at line 1

Any help is appreciated. Thanx in advance!

 

[IanWaterman]

YOu need to use the DATE_ADD function.

Standard syntax is

SELECT DATE_ADD('2013-01-01', INTERVAL 1 HOUR)

So your code will probably be

select date_add(eventdate, INTERVAL hour(select Accounts.timezone from Accounts where Accounts.uid='666') HOUR)

Ian

 

[AlexanderGrim]

Actually, i already tried that here:

http://forums.devshed.com/mysql-help-4/nested-select-statement-559263.html

But i finally figured out how this has to be done. Unfortunately it could not be done via a query, so i had to use 2 queries and some php to do the trick. I know that there probably is SOME WAY to do everything with one query, but i'm just not that advanced yet...

Thanx!

http://MyRoladex.com

http://LinuxIntro.com

http://Ionisis.com/flash

http://WretchedPlayground.com