select query, join question

[Jakeman]

PHP Code:

// SELECT UPCOMING EVENTS FROM DATABASE
$msc_events = $DB_site->query("SELECT*
    FROM msc_events
    LEFT JOIN calendar_events ON (calendar_events.eventid = msc_events.eventid)
    WHERE (UNIX_TIMESTAMP('$msc_date_extended')) <= UNIX_TIMESTAMP(calendar_events.eventdate)
    AND UNIX_TIMESTAMP(calendar_events.eventdate) <= (UNIX_TIMESTAMP('$msc_month_extended'))
    AND calendar_events.public = 1
    ORDER BY calendar_events.eventdate ASC
    LIMIT 6
    "); 


Can this query be modified to select rows from calendar_events that both do and don't have an entry in msc_events? I think the LEFT JOIN is limiting the selection to events that join with the msc_events table.

[Link14716]

Wouldn't this work?
[sql]// SELECT UPCOMING EVENTS FROM DATABASE
$msc_events = $DB_site->query("SELECT*
FROM calander_events
LEFT JOIN msc_events ON (msc_events.eventid = calendar_events.eventid)
WHERE (UNIX_TIMESTAMP('$msc_date_extended')) <= UNIX_TIMESTAMP(calendar_events.eventdate)
AND UNIX_TIMESTAMP(calendar_events.eventdate) <= (UNIX_TIMESTAMP('$msc_month_extended'))
AND calendar_events.public = 1
ORDER BY calendar_events.eventdate ASC
LIMIT 6
");[/sql]

[Jakeman]

oh dude, it's selecting from the msc_events table in the first place. :tard:

[Jakeman]

I tried coming at it from the other direction...

Problem:
If a calendar_events record doesn't have a msc_events record, then $msc_event[eventid] has no value for that record.

PHP Code:

while ($msc_event=$DB_site->fetch_array($msc_events))

... 


[Jakeman]

Maybe some pictures of my tables would help illustrate the situation. Sometimes I confuse problems like this.

I added the msc_events table for my event signup system. An entry is put into the msc_events table when an event is "signupable." If the event creator doesn't want users to be able to signup for the event, then no entry for it is made in the msc_events table.

The calendar_events table is a vBulletin table. I haven't hacked that table at all.

I want to select all public events, linking the two tables by eventid even if the public event in the calendar_events table doesn't have a matching entry in the msc_events table (it isn't "signupable").

The problem I am having right now is that there appears to be no value for eventid when the selected event doesn't have a matching msc_events entry.

[Jakeman]

second pic

[Jakeman]

I ended up selecting from the calendar_events table and querying the msc_events table while going through the array of records from the calendar_events table. I am using a lot more queries this way, but it's the only way I could get it to work without modifying the calendar_events table.

[Dean C]

Just a heads up Jake. In my PHP book SAMS Learn PHP in 24 hours it has a kind of public events system for a club in the final 2 chapters where a user can sign up and add their club with unlimited events and if the user is guest they can browse through the directory. Are you trying to achieve anything like that?

- miSt

[Jakeman]

Maybe. I already have mine working though. I added on to vB's calendar so you can create events that people can signup for. I wanted to do this without modifying any vB tables, which lead to problems like what I posted about in this thread... but I got it working.