Help with query structure, upcoming events and birthdays (two separate tables)

[eluser]JamieBarton[/eluser]
Hi folks,

Question time!

I'm working on a block on my dashboard that is to highlight the <B>5</B> upcoming events and birthdays.

The events are stored in a table called 'events' and the birthdays are stored in the user profile, in the field called 'birth_date'.

I want to query the database and select all events and birthdays that are upcoming in the 7 days (limiting displaying by 5 remember if there are more than 5) and to show the events in order of the date.

When looping through them I want to be able to show the following: http://grab.by/2zhL

As you can see the event name is just shown along with an event icon and a birthday icon along with the users name.

Can anybody help pushing me in the right lines for this?

So far, which I think will need scrapping is the following which just gets events (family_id is another WHERE i need to have in the getting events and users):

Code:

function get_upcoming_events($family_id, $limit=5, $daystoadd=7)
    {
        $now = date("Y-m-d H:i");
        $this_week = date("Y-m-d H:i", mktime(date("H"),date("i"),0,date("m"),date("d")+$daystoadd,date("Y")));
        
        $this->db->limit($limit);
        $this->db->where('family_id', $family_id)
                 ->where('end_time <=',$this_week)
                 ->where('start_time >=', $now);

        return $this->db->get('events');
    }

Any help would be greatly appreciated.

[eluser]slowgary[/eluser]
Is there anything specific that you're having problems with? Asking a specific question usually garners more answers since, as it stands, I'm not really sure what to help with.

One helpful function would be PHP's strtotime(), which will let you replace this:

Code:

$this_week = date("Y-m-d H:i", mktime(date("H"),date("i"),0,date("m"),date("d")+$daystoadd,date("Y")));

with this:

Code:

$this_week = date('Y-m-d H:i', strtotime('+1 week'));

[eluser]JamieBarton[/eluser]
im wanting to know how I am to do this whole thing? My post clearly states that really..

[eluser]woony[/eluser]
[quote author="Jamie B" date="1266786371"]im wanting to know how I am to do this whole thing? My post clearly states that really..[/quote]

Just do a union select, of the 2 tables, just for the date and name.
like this.

select eventname as name , eventdate as date
from events
union
select username as name, birthday as date
from userprofiles

and use that result as a single table and just make your list.
ofc just add order by date and limit 5 and where date > now.

Hope you get the point now, can't really go and write all the code.

[eluser]JamieBarton[/eluser]
How would I then be able to grab the user id and the event id, and of course do an if/else statement on whether or not it is an event or a birthday and have different div classes etc?

Thanks again

[eluser]woony[/eluser]
also put your event and user id in your select under a same name.
and to know wheter its a birthday or event add a string in your sql.

select eventname as name , eventdate as date, eventid as id, 'event' as type
from events
union
select username as name, birthday as date, userid as id, 'birthday' as type
from userprofiles

like this.

[eluser]JamieBarton[/eluser]
Ignore

[eluser]JamieBarton[/eluser]
Fixed it up a bit, to

Code:

$query = $this->db->query("(SELECT id as id, start_time as date, title as name, 'event' as type FROM events WHERE family_id = $family_id) UNION (SELECT user_id as id, birth_date as date, first_name as name, 'birthday' as type FROM users WHERE family_id = $family_id) ORDER BY date LIMIT $limit");
        
        return $query->result();

However I don't know where to put the WHERE date is greater than today and less than 1 week from now. Can anyone assist with that?

Also, what if I want to grab the users last_name too? How can I add that into the second query without getting an error of having unequal columns?

[eluser]omick[/eluser]
(SELECT id as id, start_time as date, title as name, '' as extra_name, 'event' as type FROM events WHERE family_id = $family_id AND start_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK))
UNION
(SELECT user_id as id, birth_date as date, first_name as name, last_name as extra_name, 'birthday' as type FROM users WHERE family_id = $family_id AND birth_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 WEEK))
ORDER BY date LIMIT $limit

...just modify to suit your desired range

if you want to include the last_name column on your second query, just include it and give it an alias and then just place a '' on the first query and use the same alias or you can use concat_ws(' ', first_name, last_name) as name

[eluser]JamieBarton[/eluser]
Hi,

Thanks for that.

However I notice a problem with the birthday. As the year, month and date is stored, it will just be looking for that whole date.. Is it possible to just make it look for the month/day?

Thanks again, much appreciated