Select next event from 2 arrays

Question

I have 2 tables

table 'events'
id      event_name    date           
1       buy milk      1319074920 // 10-20-2011
2       lunch         1321753320 // 11-20-2011

table 'people'
id      user       birthday
1       Jack       16508520 // 7-11-1970
2       Bill       180409320 // 9-20-1975

What I am trying to do is:

[1] create an array with the event closest to current date/time >> SOLVED

My query generates an array like this

Array ( [0] => 
    Array ( 
         [date]       => 1319074920
         [event_name] => buy milk
    ) 
)

[2] create an array with the birthday closest to current date/time

Not sure how to do this. I could query the database and retrieve users and birthdays and loop through the results, but would need to change the birthday year to the current year, keeping month and day unchanged.

[3] compare the 2 arrays and pick one entry that is closest to current date/time: birthday or event.

Any suggestions?

QUERY CODE

    $query = $this->db->query("
    SELECT *
    FROM   events
    WHERE  UTC_1 >= UNIX_TIMESTAMP()
    ORDER  BY UTC_1
    LIMIT  1
    ;");

How is it that the query you used to solve [1] will not work exactly the same for [2]? Your table structure and desired filter are identical in both of those problems, isn't it? — Kiley Naro, Sep 30 '11 at 02:05
yes, but there some differences: [1] all birthdays (dates of birth) are in the PAST, [2] I need to convert for example 7-11-1970 to 7-11-2011 and so on to verify if the birthday is happening before an event from the 'event' table — pepe, Sep 30 '11 at 02:09
Found a partial answer here http://stackoverflow.com/questions/5192007/how-to-convert-birth-date-to-current-years-birthday-from-a-field-timestamp — pepe, Sep 30 '11 at 03:16

score 2 · Accepted Answer · answered Sep 30 '11 at 02:06

2

If you've got a query that gives you the closest event, then just use the same query for birthdays, since your tables are the same structure. Once you have the closest event and closest birthday, you can do a simple compare in php to find which one to display.

if (abs(bday[0]['date'] - time()) < abs(event[0]['date'] - time())) {
    // Show birthday
} else {
    // Show event
}

answered Sep 30 '11 at 02:06

xthexder

1,555
10
22

i added the query to OP - it only gets the next FUTURE event, and I think that even if I use `ABS` in the query that will not work, because it will get the closest PAST birthday, not the next FUTURE birthday – pepe Sep 30 '11 at 02:13
The way the abs are set up, it will find the closest event for both the past and future. If your query only finds the closest future event, then you don't need the abs at all. – xthexder Sep 30 '11 at 03:00

Select next event from 2 arrays

1 Answers1