difficult query

Hi all,

I have events containing attendees (events has many attendees). The
attendee table tells whether a user will attend the event or not. I
want to build a query that returns all the different events to a user
and if he will attend the event or not (or hasn't filled it in yet)

the returned result could be something like:

event.id attendees.user_id attendee.present
1 1 0
2 1
3 1 1

Please note that attendee.present can be null if the user didn't yet
tell if he would come to the event.

Can this be done?

thanks
Stijn

Re: difficult query

"Tarscher" wrote in message
news:b9b89855-c327-46ab-9868-78e71993b63f@i29g2000prf.google groups.com...
> Hi all,
>
> I have events containing attendees (events has many attendees). The
> attendee table tells whether a user will attend the event or not. I
> want to build a query that returns all the different events to a user
> and if he will attend the event or not (or hasn't filled it in yet)
>
> the returned result could be something like:
>
> event.id attendees.user_id attendee.present
> 1 1 0
> 2 1
> 3 1 1
>
> Please note that attendee.present can be null if the user didn't yet
> tell if he would come to the event.
>
> Can this be done?
>
> thanks
> Stijn

SELECT * FROM Tablename WHERE user_id = 'TargetId'
The above will return all fields for a user id.

SELECT present FROM Tablename WHERE user_id = 'TargetId'
The above will return all "present" for a user id.

However, I imagine there is another table that contains the names of the
persons?
HTH
Vince

Re: difficult query

On 21 Dec, 08:43, Tarscher wrote:
> Hi all,
>
> I have events containing attendees (events has many attendees). The
> attendee table tells whether a user will attend the event or not. I
> want to build a query that returns all the different events to a user
> and if he will attend the event or not (or hasn't filled it in yet)
>
> the returned result could be something like:
>
> event.id attendees.user_id attendee.present
> 1 1 0
> 2 1
> 3 1 1
>
> Please note that attendee.present can be null if the user didn't yet
> tell if he would come to the event.
>
> Can this be done?
>
> thanks
> Stijn

And this has what to do with php?

You would be better to ask this in a database group.

However some questions:
If a user is querying the database to find if he will be attending the
event, why does his own ID need to be present in the output?
How does the attendee's id get into the table against an event in the
first place?

Re: difficult query

On 21 dec, 11:13, Captain Paralytic wrote:
> On 21 Dec, 08:43,Tarscher wrote:
>
>
>
> > Hi all,
>
> > I have events containing attendees (events has many attendees). The
> > attendee table tells whether a user will attend the event or not. I
> > want to build a query that returns all the different events to a user
> > and if he will attend the event or not (or hasn't filled it in yet)
>
> > the returned result could be something like:
>
> > event.id attendees.user_id attendee.present
> > 1 1 0
> > 2 1
> > 3 1 1
>
> > Please note that attendee.present can be null if the user didn't yet
> > tell if he would come to the event.
>
> > Can this be done?
>
> > thanks
> > Stijn
>
> And this has what to do with php?
>
> You would be better to ask this in a database group.
>
> However some questions:
> If a user is querying the database to find if he will be attending the
> event, why does his own ID need to be present in the output?
> How does the attendee's id get into the table against an event in the
> first place?

I indeed don't need the user_id since it is stored in the session. It
was just to clarify that the query need to return 1 user.

Via the session the user_id stored in the session.

Regards

Re: difficult query

On 21 Dec, 10:36, Tarscher wrote:
> On 21 dec, 11:13, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 08:43,Tarscher wrote:
>
> > > Hi all,
>
> > > I have events containing attendees (events has many attendees). The
> > > attendee table tells whether a user will attend the event or not. I
> > > want to build a query that returns all the different events to a user
> > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > the returned result could be something like:
>
> > > event.id attendees.user_id attendee.present
> > > 1 1 0
> > > 2 1
> > > 3 1 1
>
> > > Please note that attendee.present can be null if the user didn't yet
> > > tell if he would come to the event.
>
> > > Can this be done?
>
> > > thanks
> > > Stijn
>
> > And this has what to do with php?
>
> > You would be better to ask this in a database group.
>
> > However some questions:
> > If a user is querying the database to find if he will be attending the
> > event, why does his own ID need to be present in the output?
> > How does the attendee's id get into the table against an event in the
> > first place?
>
> I indeed don't need the user_id since it is stored in the session. It
> was just to clarify that the query need to return 1 user.
>
> Via the session the user_id stored in the session.
>
> Regards

I don't understand how
"Via the session the user_id stored in the session."

answers the question
"How does the attendee's id get into the table against an event in the
first place?"

Re: difficult query

On 21 dec, 11:45, Captain Paralytic wrote:
> On 21 Dec, 10:36, Tarscher wrote:
>
>
>
> > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > Hi all,
>
> > > > I have events containing attendees (events has many attendees). The
> > > > attendee table tells whether a user will attend the event or not. I
> > > > want to build a query that returns all the different events to a user
> > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > the returned result could be something like:
>
> > > > event.id attendees.user_id attendee.present
> > > > 1 1 0
> > > > 2 1
> > > > 3 1 1
>
> > > > Please note that attendee.present can be null if the user didn't yet
> > > > tell if he would come to the event.
>
> > > > Can this be done?
>
> > > > thanks
> > > > Stijn
>
> > > And this has what to do with php?
>
> > > You would be better to ask this in a database group.
>
> > > However some questions:
> > > If a user is querying the database to find if he will be attending the
> > > event, why does his own ID need to be present in the output?
> > > How does the attendee's id get into the table against an event in the
> > > first place?
>
> > I indeed don't need the user_id since it is stored in the session. It
> > was just to clarify that the query need to return 1 user.
>
> > Via the session the user_id stored in the session.
>
> > Regards
>
> I don't understand how
> "Via the session the user_id stored in the session."
>
> answers the question
> "How does the attendee's id get into the table against an event in the
> first place?"

sorry, a typo

INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
session['user_id'])

I get the event_id via the url since the user does this per event.
eg
event1: 'will attend' 'will not attend'
event2: 'will attend' 'will not attend'

The 'will attend' and 'will not attend' links point to the sql query
inserting in attendee

I hope this helps

Re: difficult query

On 21 Dec, 10:52, Tarscher wrote:
> On 21 dec, 11:45, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 10:36, Tarscher wrote:
>
> > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > Hi all,
>
> > > > > I have events containing attendees (events has many attendees). The
> > > > > attendee table tells whether a user will attend the event or not. I
> > > > > want to build a query that returns all the different events to a user
> > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > the returned result could be something like:
>
> > > > > event.id attendees.user_id attendee.present
> > > > > 1 1 0
> > > > > 2 1
> > > > > 3 1 1
>
> > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > tell if he would come to the event.
>
> > > > > Can this be done?
>
> > > > > thanks
> > > > > Stijn
>
> > > > And this has what to do with php?
>
> > > > You would be better to ask this in a database group.
>
> > > > However some questions:
> > > > If a user is querying the database to find if he will be attending the
> > > > event, why does his own ID need to be present in the output?
> > > > How does the attendee's id get into the table against an event in the
> > > > first place?
>
> > > I indeed don't need the user_id since it is stored in the session. It
> > > was just to clarify that the query need to return 1 user.
>
> > > Via the session the user_id stored in the session.
>
> > > Regards
>
> > I don't understand how
> > "Via the session the user_id stored in the session."
>
> > answers the question
> > "How does the attendee's id get into the table against an event in the
> > first place?"
>
> sorry, a typo
>
> INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> session['user_id'])
>
> I get the event_id via the url since the user does this per event.
> eg
> event1: 'will attend' 'will not attend'
> event2: 'will attend' 'will not attend'
>
> The 'will attend' and 'will not attend' links point to the sql query
> inserting in attendee
>
> I hope this helps

No, that is not what I mean.

You have a table attendee which contains events. Personally I would
have an events table to contain the events.

Now you tell us that the attendee table has events and attendees and
it is possible for an attendee to say that they will not attend the
event. I have to say that someone who will not attend an event will by
definition not be an attendee!

My question is, if for some reason you have all your events listed in
the attendee table and the attendee has not put in there a record
saying that he will or will not attend the event, how did the record
with the event id and attendee id get in the table in the first place?

Re: difficult query

On 21 dec, 12:12, Captain Paralytic wrote:
> On 21 Dec, 10:52,Tarscher wrote:
>
>
>
> > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > Hi all,
>
> > > > > > I have events containing attendees (events has many attendees). The
> > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > want to build a query that returns all the different events to a user
> > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > the returned result could be something like:
>
> > > > > > event.id attendees.user_id attendee.present
> > > > > > 1 1 0
> > > > > > 2 1
> > > > > > 3 1 1
>
> > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > tell if he would come to the event.
>
> > > > > > Can this be done?
>
> > > > > > thanks
> > > > > > Stijn
>
> > > > > And this has what to do with php?
>
> > > > > You would be better to ask this in a database group.
>
> > > > > However some questions:
> > > > > If a user is querying the database to find if he will be attending the
> > > > > event, why does his own ID need to be present in the output?
> > > > > How does the attendee's id get into the table against an event in the
> > > > > first place?
>
> > > > I indeed don't need the user_id since it is stored in the session. It
> > > > was just to clarify that the query need to return 1 user.
>
> > > > Via the session the user_id stored in the session.
>
> > > > Regards
>
> > > I don't understand how
> > > "Via the session the user_id stored in the session."
>
> > > answers the question
> > > "How does the attendee's id get into the table against an event in the
> > > first place?"
>
> > sorry, a typo
>
> > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > session['user_id'])
>
> > I get the event_id via the url since the user does this per event.
> > eg
> > event1: 'will attend' 'will not attend'
> > event2: 'will attend' 'will not attend'
>
> > The 'will attend' and 'will not attend' links point to the sql query
> > inserting in attendee
>
> > I hope this helps
>
> No, that is not what I mean.
>
> You have a table attendee which contains events. Personally I would
> have an events table to contain the events.
>
> Now you tell us that the attendee table has events and attendees and
> it is possible for an attendee to say that they will not attend the
> event. I have to say that someone who will not attend an event will by
> definition not be an attendee!
>
> My question is, if for some reason you have all your events listed in
> the attendee table and the attendee has not put in there a record
> saying that he will or will not attend the event, how did the record
> with the event id and attendee id get in the table in the first place?

I have an event table and attendee table (events has many attendees).
The event_id in attendee points to the key of the event table.
event table: id, name, time
attendee table, id, user_id, event_id, present

Re: difficult query

On 21 Dec, 11:18, Tarscher wrote:
> On 21 dec, 12:12, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 10:52,Tarscher wrote:
>
> > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > Hi all,
>
> > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > want to build a query that returns all the different events to a user
> > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > the returned result could be something like:
>
> > > > > > > event.id attendees.user_id attendee.present
> > > > > > > 1 1 0
> > > > > > > 2 1
> > > > > > > 3 1 1
>
> > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > tell if he would come to the event.
>
> > > > > > > Can this be done?
>
> > > > > > > thanks
> > > > > > > Stijn
>
> > > > > > And this has what to do with php?
>
> > > > > > You would be better to ask this in a database group.
>
> > > > > > However some questions:
> > > > > > If a user is querying the database to find if he will be attending the
> > > > > > event, why does his own ID need to be present in the output?
> > > > > > How does the attendee's id get into the table against an event in the
> > > > > > first place?
>
> > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > was just to clarify that the query need to return 1 user.
>
> > > > > Via the session the user_id stored in the session.
>
> > > > > Regards
>
> > > > I don't understand how
> > > > "Via the session the user_id stored in the session."
>
> > > > answers the question
> > > > "How does the attendee's id get into the table against an event in the
> > > > first place?"
>
> > > sorry, a typo
>
> > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > session['user_id'])
>
> > > I get the event_id via the url since the user does this per event.
> > > eg
> > > event1: 'will attend' 'will not attend'
> > > event2: 'will attend' 'will not attend'
>
> > > The 'will attend' and 'will not attend' links point to the sql query
> > > inserting in attendee
>
> > > I hope this helps
>
> > No, that is not what I mean.
>
> > You have a table attendee which contains events. Personally I would
> > have an events table to contain the events.
>
> > Now you tell us that the attendee table has events and attendees and
> > it is possible for an attendee to say that they will not attend the
> > event. I have to say that someone who will not attend an event will by
> > definition not be an attendee!
>
> > My question is, if for some reason you have all your events listed in
> > the attendee table and the attendee has not put in there a record
> > saying that he will or will not attend the event, how did the record
> > with the event id and attendee id get in the table in the first place?
>
> I have an event table and attendee table (events has many attendees).
> The event_id in attendee points to the key of the event table.
> event table: id, name, time
> attendee table, id, user_id, event_id, present

Things aren't as stated in your first post.

However you STILL haven't answered my question.

How does an attendeed get an entry in the attendee table against a
particular event? You have suggested that the entry can be there
before the prospective attendee has looked at the table, so HOW did it
getthere?

Re: difficult query

On 21 dec, 12:24, Captain Paralytic wrote:
> On 21 Dec, 11:18,Tarscher wrote:
>
>
>
> > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > Hi all,
>
> > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > the returned result could be something like:
>
> > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > 1 1 0
> > > > > > > > 2 1
> > > > > > > > 3 1 1
>
> > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > tell if he would come to the event.
>
> > > > > > > > Can this be done?
>
> > > > > > > > thanks
> > > > > > > > Stijn
>
> > > > > > > And this has what to do with php?
>
> > > > > > > You would be better to ask this in a database group.
>
> > > > > > > However some questions:
> > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > first place?
>
> > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > Via the session the user_id stored in the session.
>
> > > > > > Regards
>
> > > > > I don't understand how
> > > > > "Via the session the user_id stored in the session."
>
> > > > > answers the question
> > > > > "How does the attendee's id get into the table against an event in the
> > > > > first place?"
>
> > > > sorry, a typo
>
> > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > session['user_id'])
>
> > > > I get the event_id via the url since the user does this per event.
> > > > eg
> > > > event1: 'will attend' 'will not attend'
> > > > event2: 'will attend' 'will not attend'
>
> > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > inserting in attendee
>
> > > > I hope this helps
>
> > > No, that is not what I mean.
>
> > > You have a table attendee which contains events. Personally I would
> > > have an events table to contain the events.
>
> > > Now you tell us that the attendee table has events and attendees and
> > > it is possible for an attendee to say that they will not attend the
> > > event. I have to say that someone who will not attend an event will by
> > > definition not be an attendee!
>
> > > My question is, if for some reason you have all your events listed in
> > > the attendee table and the attendee has not put in there a record
> > > saying that he will or will not attend the event, how did the record
> > > with the event id and attendee id get in the table in the first place?
>
> > I have an event table and attendee table (events has many attendees).
> > The event_id in attendee points to the key of the event table.
> > event table: id, name, time
> > attendee table, id, user_id, event_id, present
>
> Things aren't as stated in your first post.
>
> However you STILL haven't answered my question.
>
> How does an attendeed get an entry in the attendee table against a
> particular event? You have suggested that the entry can be there
> before the prospective attendee has looked at the table, so HOW did it
> getthere?

Sorry, I have a hard time understanding your question.

The attendee can enter because he sees all available events (with
attend and not attend link behind): the one he attends, the one he
will not attend and the one he hasn't yet voted on (in this case there
is no attendee record for that user for that event.)
we have 3 cases:
- on the list of events the user clicks an event he previously said he
would attend to not attend. The attendees present record changes to 0
- on the list of events the user clicks an event he previously said he
would not attend to attend. The attendees present record changes to 1
- on the list of events the user clicks an event he hasn't 'voted' on
yet. An attendees record is created in the attendee table

i hope this helps

Re: difficult query

On Fri, 21 Dec 2007 03:12:18 -0800 (PST), in comp.lang.php Captain
Paralytic

wrote:

>| On 21 Dec, 10:52, Tarscher wrote:
>| > On 21 dec, 11:45, Captain Paralytic wrote:
>| >
>| >
>| >
>| > > On 21 Dec, 10:36, Tarscher wrote:
>| >
>| > > > On 21 dec, 11:13, Captain Paralytic wrote:
>| >
>| > > > > On 21 Dec, 08:43,Tarscher wrote:
>| >
>| > > > > > Hi all,
>| >
>| > > > > > I have events containing attendees (events has many attendees). The
>| > > > > > attendee table tells whether a user will attend the event or not. I
>| > > > > > want to build a query that returns all the different events to a user
>| > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>| >
>| > > > > > the returned result could be something like:
>| >
>| > > > > > event.id attendees.user_id attendee.present
>| > > > > > 1 1 0
>| > > > > > 2 1
>| > > > > > 3 1 1
>| >
>| > > > > > Please note that attendee.present can be null if the user didn't yet
>| > > > > > tell if he would come to the event.
>| >
>| > > > > > Can this be done?
>| >
>| > > > > > thanks
>| > > > > > Stijn
>| >
>| > > > > And this has what to do with php?
>| >
>| > > > > You would be better to ask this in a database group.
>| >
>| > > > > However some questions:
>| > > > > If a user is querying the database to find if he will be attending the
>| > > > > event, why does his own ID need to be present in the output?
>| > > > > How does the attendee's id get into the table against an event in the
>| > > > > first place?
>| >
>| > > > I indeed don't need the user_id since it is stored in the session. It
>| > > > was just to clarify that the query need to return 1 user.
>| >
>| > > > Via the session the user_id stored in the session.
>| >
>| > > > Regards
>| >
>| > > I don't understand how
>| > > "Via the session the user_id stored in the session."
>| >
>| > > answers the question
>| > > "How does the attendee's id get into the table against an event in the
>| > > first place?"
>| >
>| > sorry, a typo
>| >
>| > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
>| > session['user_id'])
>| >
>| > I get the event_id via the url since the user does this per event.
>| > eg
>| > event1: 'will attend' 'will not attend'
>| > event2: 'will attend' 'will not attend'
>| >
>| > The 'will attend' and 'will not attend' links point to the sql query
>| > inserting in attendee
>| >
>| > I hope this helps
>|
>| No, that is not what I mean.
>|
>| You have a table attendee which contains events. Personally I would
>| have an events table to contain the events.

The 'attendee' table only contains 0, 1 or null. I think you mean the
attendeeS table.

Tarscher does have that structure. Ever heard of link tables? The id's
in this case would be foreign keys thus perfectly valid.

>| Now you tell us that the attendee table has events and attendees and
>| it is possible for an attendee to say that they will not attend the
>| event. I have to say that someone who will not attend an event will by
>| definition not be an attendee!

Don't jump to conclusions. The information may be needed for other
purposes i.e. if a user hasn't responded to the last 10 invites then
remove them from the list or if the user has declined so many invites
then send an email to see if they want to stay on the list etc etc
etc. We haven't been given this information.

>| My question is, if for some reason you have all your events listed in
>| the attendee table and the attendee has not put in there a record
>| saying that he will or will not attend the event, how did the record
>| with the event id and attendee id get in the table in the first place?

You'd jumped to the wrong conclusion again. The attendeeS table
contain foreign keys thus the information is correct.

Although I would admit that there is really no need for the attendee
table because the additional information could be stored within the
attendeeS table, unless there was other relevant data that has not
been shown.
-- ------------------------------------------------------------ -
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
-- ------------------------------------------------------------ -

Re: difficult query

On 21 Dec, 11:43, Tarscher wrote:
> On 21 dec, 12:24, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 11:18,Tarscher wrote:
>
> > > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > > Hi all,
>
> > > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > > the returned result could be something like:
>
> > > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > > 1 1 0
> > > > > > > > > 2 1
> > > > > > > > > 3 1 1
>
> > > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > > tell if he would come to the event.
>
> > > > > > > > > Can this be done?
>
> > > > > > > > > thanks
> > > > > > > > > Stijn
>
> > > > > > > > And this has what to do with php?
>
> > > > > > > > You would be better to ask this in a database group.
>
> > > > > > > > However some questions:
> > > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > > first place?
>
> > > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > > Via the session the user_id stored in the session.
>
> > > > > > > Regards
>
> > > > > > I don't understand how
> > > > > > "Via the session the user_id stored in the session."
>
> > > > > > answers the question
> > > > > > "How does the attendee's id get into the table against an event in the
> > > > > > first place?"
>
> > > > > sorry, a typo
>
> > > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > > session['user_id'])
>
> > > > > I get the event_id via the url since the user does this per event.
> > > > > eg
> > > > > event1: 'will attend' 'will not attend'
> > > > > event2: 'will attend' 'will not attend'
>
> > > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > > inserting in attendee
>
> > > > > I hope this helps
>
> > > > No, that is not what I mean.
>
> > > > You have a table attendee which contains events. Personally I would
> > > > have an events table to contain the events.
>
> > > > Now you tell us that the attendee table has events and attendees and
> > > > it is possible for an attendee to say that they will not attend the
> > > > event. I have to say that someone who will not attend an event will by
> > > > definition not be an attendee!
>
> > > > My question is, if for some reason you have all your events listed in
> > > > the attendee table and the attendee has not put in there a record
> > > > saying that he will or will not attend the event, how did the record
> > > > with the event id and attendee id get in the table in the first place?
>
> > > I have an event table and attendee table (events has many attendees).
> > > The event_id in attendee points to the key of the event table.
> > > event table: id, name, time
> > > attendee table, id, user_id, event_id, present
>
> > Things aren't as stated in your first post.
>
> > However you STILL haven't answered my question.
>
> > How does an attendeed get an entry in the attendee table against a
> > particular event? You have suggested that the entry can be there
> > before the prospective attendee has looked at the table, so HOW did it
> > getthere?
>
> Sorry, I have a hard time understanding your question.
>
> The attendee can enter because he sees all available events (with
> attend and not attend link behind): the one he attends, the one he
> will not attend and the one he hasn't yet voted on (in this case there
> is no attendee record for that user for that event.)
> we have 3 cases:
> - on the list of events the user clicks an event he previously said he
> would attend to not attend. The attendees present record changes to 0
> - on the list of events the user clicks an event he previously said he
> would not attend to attend. The attendees present record changes to 1
> - on the list of events the user clicks an event he hasn't 'voted' on
> yet. An attendees record is created in the attendee table
>
> i hope this helps

This doesn't make much sense to me.

Why have a record in the attendee table if the person will not be an
attendee?
Why put a record in the attendee table just because a person has
looked at the detals of an event from the event table?

Why not just list the events from the event table, LEFT JOINED to the
attendee table. If there is no entry in the attendee table, the person
is not yet an attendee. If you really want to have a record in the
attendees table for people who will certainly not be attendees (this
seems crazy) then I still don't see why you would want to put an entry
in there when all the person has done is looked at the event.

Re: difficult query

On 21 dec, 12:52, Captain Paralytic wrote:
> On 21 Dec, 11:43, Tarscher wrote:
>
>
>
> > On 21 dec, 12:24, Captain Paralytic wrote:
>
> > > On 21 Dec, 11:18,Tarscher wrote:
>
> > > > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > > > Hi all,
>
> > > > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > > > the returned result could be something like:
>
> > > > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > > > 1 1 0
> > > > > > > > > > 2 1
> > > > > > > > > > 3 1 1
>
> > > > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > > > tell if he would come to the event.
>
> > > > > > > > > > Can this be done?
>
> > > > > > > > > > thanks
> > > > > > > > > > Stijn
>
> > > > > > > > > And this has what to do with php?
>
> > > > > > > > > You would be better to ask this in a database group.
>
> > > > > > > > > However some questions:
> > > > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > > > first place?
>
> > > > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > > > Via the session the user_id stored in the session.
>
> > > > > > > > Regards
>
> > > > > > > I don't understand how
> > > > > > > "Via the session the user_id stored in the session."
>
> > > > > > > answers the question
> > > > > > > "How does the attendee's id get into the table against an event in the
> > > > > > > first place?"
>
> > > > > > sorry, a typo
>
> > > > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > > > session['user_id'])
>
> > > > > > I get the event_id via the url since the user does this per event.
> > > > > > eg
> > > > > > event1: 'will attend' 'will not attend'
> > > > > > event2: 'will attend' 'will not attend'
>
> > > > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > > > inserting in attendee
>
> > > > > > I hope this helps
>
> > > > > No, that is not what I mean.
>
> > > > > You have a table attendee which contains events. Personally I would
> > > > > have an events table to contain the events.
>
> > > > > Now you tell us that the attendee table has events and attendees and
> > > > > it is possible for an attendee to say that they will not attend the
> > > > > event. I have to say that someone who will not attend an event will by
> > > > > definition not be an attendee!
>
> > > > > My question is, if for some reason you have all your events listed in
> > > > > the attendee table and the attendee has not put in there a record
> > > > > saying that he will or will not attend the event, how did the record
> > > > > with the event id and attendee id get in the table in the first place?
>
> > > > I have an event table and attendee table (events has many attendees).
> > > > The event_id in attendee points to the key of the event table.
> > > > event table: id, name, time
> > > > attendee table, id, user_id, event_id, present
>
> > > Things aren't as stated in your first post.
>
> > > However you STILL haven't answered my question.
>
> > > How does an attendeed get an entry in the attendee table against a
> > > particular event? You have suggested that the entry can be there
> > > before the prospective attendee has looked at the table, so HOW did it
> > > getthere?
>
> > Sorry, I have a hard time understanding your question.
>
> > The attendee can enter because he sees all available events (with
> > attend and not attend link behind): the one he attends, the one he
> > will not attend and the one he hasn't yet voted on (in this case there
> > is no attendee record for that user for that event.)
> > we have 3 cases:
> > - on the list of events the user clicks an event he previously said he
> > would attend to not attend. The attendees present record changes to 0
> > - on the list of events the user clicks an event he previously said he
> > would not attend to attend. The attendees present record changes to 1
> > - on the list of events the user clicks an event he hasn't 'voted' on
> > yet. An attendees record is created in the attendee table
>
> > i hope this helps
>
> This doesn't make much sense to me.
>
> Why have a record in the attendee table if the person will not be an
> attendee?
> Why put a record in the attendee table just because a person has
> looked at the detals of an event from the event table?
>
> Why not just list the events from the event table, LEFT JOINED to the
> attendee table. If there is no entry in the attendee table, the person
> is not yet an attendee. If you really want to have a record in the
> attendees table for people who will certainly not be attendees (this
> seems crazy) then I still don't see why you would want to put an entry
> in there when all the person has done is looked at the event.

Cos there is a clear difference between saying you don't attend or
haven't said yet you will or will not attend. This way I can show the
user whether he already entered his attendence for this event. (which
he has to do) It works as a reminder

> Why put a record in the attendee table just because a person has
> looked at the detals of an event from the event table?

This doesn't happen unless the user himself votes his presense (via
the link)

Re: difficult query

On 21 Dec, 12:06, Tarscher wrote:
> On 21 dec, 12:52, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 11:43, Tarscher wrote:
>
> > > On 21 dec, 12:24, Captain Paralytic wrote:
>
> > > > On 21 Dec, 11:18,Tarscher wrote:
>
> > > > > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > > > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > > > > Hi all,
>
> > > > > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > > > > the returned result could be something like:
>
> > > > > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > > > > 1 1 0
> > > > > > > > > > > 2 1
> > > > > > > > > > > 3 1 1
>
> > > > > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > > > > tell if he would come to the event.
>
> > > > > > > > > > > Can this be done?
>
> > > > > > > > > > > thanks
> > > > > > > > > > > Stijn
>
> > > > > > > > > > And this has what to do with php?
>
> > > > > > > > > > You would be better to ask this in a database group.
>
> > > > > > > > > > However some questions:
> > > > > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > > > > first place?
>
> > > > > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > > > > Via the session the user_id stored in the session.
>
> > > > > > > > > Regards
>
> > > > > > > > I don't understand how
> > > > > > > > "Via the session the user_id stored in the session."
>
> > > > > > > > answers the question
> > > > > > > > "How does the attendee's id get into the table against an event in the
> > > > > > > > first place?"
>
> > > > > > > sorry, a typo
>
> > > > > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > > > > session['user_id'])
>
> > > > > > > I get the event_id via the url since the user does this per event.
> > > > > > > eg
> > > > > > > event1: 'will attend' 'will not attend'
> > > > > > > event2: 'will attend' 'will not attend'
>
> > > > > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > > > > inserting in attendee
>
> > > > > > > I hope this helps
>
> > > > > > No, that is not what I mean.
>
> > > > > > You have a table attendee which contains events. Personally I would
> > > > > > have an events table to contain the events.
>
> > > > > > Now you tell us that the attendee table has events and attendees and
> > > > > > it is possible for an attendee to say that they will not attend the
> > > > > > event. I have to say that someone who will not attend an event will by
> > > > > > definition not be an attendee!
>
> > > > > > My question is, if for some reason you have all your events listed in
> > > > > > the attendee table and the attendee has not put in there a record
> > > > > > saying that he will or will not attend the event, how did the record
> > > > > > with the event id and attendee id get in the table in the first place?
>
> > > > > I have an event table and attendee table (events has many attendees).
> > > > > The event_id in attendee points to the key of the event table.
> > > > > event table: id, name, time
> > > > > attendee table, id, user_id, event_id, present
>
> > > > Things aren't as stated in your first post.
>
> > > > However you STILL haven't answered my question.
>
> > > > How does an attendeed get an entry in the attendee table against a
> > > > particular event? You have suggested that the entry can be there
> > > > before the prospective attendee has looked at the table, so HOW did it
> > > > getthere?
>
> > > Sorry, I have a hard time understanding your question.
>
> > > The attendee can enter because he sees all available events (with
> > > attend and not attend link behind): the one he attends, the one he
> > > will not attend and the one he hasn't yet voted on (in this case there
> > > is no attendee record for that user for that event.)
> > > we have 3 cases:
> > > - on the list of events the user clicks an event he previously said he
> > > would attend to not attend. The attendees present record changes to 0
> > > - on the list of events the user clicks an event he previously said he
> > > would not attend to attend. The attendees present record changes to 1
> > > - on the list of events the user clicks an event he hasn't 'voted' on
> > > yet. An attendees record is created in the attendee table
>
> > > i hope this helps
>
> > This doesn't make much sense to me.
>
> > Why have a record in the attendee table if the person will not be an
> > attendee?
> > Why put a record in the attendee table just because a person has
> > looked at the detals of an event from the event table?
>
> > Why not just list the events from the event table, LEFT JOINED to the
> > attendee table. If there is no entry in the attendee table, the person
> > is not yet an attendee. If you really want to have a record in the
> > attendees table for people who will certainly not be attendees (this
> > seems crazy) then I still don't see why you would want to put an entry
> > in there when all the person has done is looked at the event.
>
> Cos there is a clear difference between saying you don't attend or
> haven't said yet you will or will not attend. This way I can show the
> user whether he already entered his attendence for this event. (which
> he has to do) It works as a reminder
A table should not be called attendee, if it contains records of non-
attendees. This is confusing to future maintainers.

> > Why put a record in the attendee table just because a person has
> > looked at the detals of an event from the event table?
>
> This doesn't happen unless the user himself votes his presense (via
> the link)
If no record is put into this table UNLESS the user votes his presence
or absence, how can you have "attendee.present can be null if the user
didn't yet tell if he would come to the event."

I have been asking and asking how this null record gets into the
table!

Re: difficult query

On 21 Dec, 11:49, Jeff North wrote:
> On Fri, 21 Dec 2007 03:12:18 -0800 (PST), in comp.lang.php Captain
> Paralytic
>
> wrote:
>
>
>
> >| On 21 Dec, 10:52, Tarscher wrote:
> >| > On 21 dec, 11:45, Captain Paralytic wrote:
> >| >
> >| >
> >| >
> >| > > On 21 Dec, 10:36, Tarscher wrote:
> >| >
> >| > > > On 21 dec, 11:13, Captain Paralytic wrote:
> >| >
> >| > > > > On 21 Dec, 08:43,Tarscher wrote:
> >| >
> >| > > > > > Hi all,
> >| >
> >| > > > > > I have events containing attendees (events has many attendees). The
> >| > > > > > attendee table tells whether a user will attend the event or not. I
> >| > > > > > want to build a query that returns all the different events to a user
> >| > > > > > and if he will attend the event or not (or hasn't filled it in yet)
> >| >
> >| > > > > > the returned result could be something like:
> >| >
> >| > > > > > event.id attendees.user_id attendee.present
> >| > > > > > 1 1 0
> >| > > > > > 2 1
> >| > > > > > 3 1 1
> >| >
> >| > > > > > Please note that attendee.present can be null if the user didn't yet
> >| > > > > > tell if he would come to the event.
> >| >
> >| > > > > > Can this be done?
> >| >
> >| > > > > > thanks
> >| > > > > > Stijn
> >| >
> >| > > > > And this has what to do with php?
> >| >
> >| > > > > You would be better to ask this in a database group.
> >| >
> >| > > > > However some questions:
> >| > > > > If a user is querying the database to find if he will be attending the
> >| > > > > event, why does his own ID need to be present in the output?
> >| > > > > How does the attendee's id get into the table against an event in the
> >| > > > > first place?
> >| >
> >| > > > I indeed don't need the user_id since it is stored in the session. It
> >| > > > was just to clarify that the query need to return 1 user.
> >| >
> >| > > > Via the session the user_id stored in the session.
> >| >
> >| > > > Regards
> >| >
> >| > > I don't understand how
> >| > > "Via the session the user_id stored in the session."
> >| >
> >| > > answers the question
> >| > > "How does the attendee's id get into the table against an event in the
> >| > > first place?"
> >| >
> >| > sorry, a typo
> >| >
> >| > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> >| > session['user_id'])
> >| >
> >| > I get the event_id via the url since the user does this per event.
> >| > eg
> >| > event1: 'will attend' 'will not attend'
> >| > event2: 'will attend' 'will not attend'
> >| >
> >| > The 'will attend' and 'will not attend' links point to the sql query
> >| > inserting in attendee
> >| >
> >| > I hope this helps
> >|
> >| No, that is not what I mean.
> >|
> >| You have a table attendee which contains events. Personally I would
> >| have an events table to contain the events.
>
> The 'attendee' table only contains 0, 1 or null. I think you mean the
> attendeeS table.
>
> Tarscher does have that structure. Ever heard of link tables? The id's
> in this case would be foreign keys thus perfectly valid.
>
> >| Now you tell us that the attendee table has events and attendees and
> >| it is possible for an attendee to say that they will not attend the
> >| event. I have to say that someone who will not attend an event will by
> >| definition not be an attendee!
>
> Don't jump to conclusions. The information may be needed for other
> purposes i.e. if a user hasn't responded to the last 10 invites then
> remove them from the list or if the user has declined so many invites
> then send an email to see if they want to stay on the list etc etc
> etc. We haven't been given this information.
>
> >| My question is, if for some reason you have all your events listed in
> >| the attendee table and the attendee has not put in there a record
> >| saying that he will or will not attend the event, how did the record
> >| with the event id and attendee id get in the table in the first place?
>
> You'd jumped to the wrong conclusion again. The attendeeS table
> contain foreign keys thus the information is correct.
>
> Although I would admit that there is really no need for the attendee
> table because the additional information could be stored within the
> attendeeS table, unless there was other relevant data that has not
> been shown.
> -- ------------------------------------------------------------ -
> jnort...@yourpantsyahoo.com.au : Remove your pants to reply
> -- ------------------------------------------------------------ -

I haven't jumped to any conclusions. I am trying to understand what
the proposed design is supposed to be. One should not name a table
attendee is if contains records of people who are not attendees. I
have no problem with data being held for any purpose whatsoever, but
something in the design must give a clue as to what is supposed to be
happening.

Re: difficult query

On 21 dec, 14:02, Captain Paralytic wrote:
> On 21 Dec, 12:06, Tarscher wrote:
>
> > On 21 dec, 12:52, Captain Paralytic wrote:
>
> > > On 21 Dec, 11:43, Tarscher wrote:
>
> > > > On 21 dec, 12:24, Captain Paralytic wrote:
>
> > > > > On 21 Dec, 11:18,Tarscher wrote:
>
> > > > > > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > > > > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > > > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > > > > > Hi all,
>
> > > > > > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > > > > > the returned result could be something like:
>
> > > > > > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > > > > > 1 1 0
> > > > > > > > > > > > 2 1
> > > > > > > > > > > > 3 1 1
>
> > > > > > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > > > > > tell if he would come to the event.
>
> > > > > > > > > > > > Can this be done?
>
> > > > > > > > > > > > thanks
> > > > > > > > > > > > Stijn
>
> > > > > > > > > > > And this has what to do with php?
>
> > > > > > > > > > > You would be better to ask this in a database group.
>
> > > > > > > > > > > However some questions:
> > > > > > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > > > > > first place?
>
> > > > > > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > > > > > Via the session the user_id stored in the session.
>
> > > > > > > > > > Regards
>
> > > > > > > > > I don't understand how
> > > > > > > > > "Via the session the user_id stored in the session."
>
> > > > > > > > > answers the question
> > > > > > > > > "How does the attendee's id get into the table against an event in the
> > > > > > > > > first place?"
>
> > > > > > > > sorry, a typo
>
> > > > > > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > > > > > session['user_id'])
>
> > > > > > > > I get the event_id via the url since the user does this per event.
> > > > > > > > eg
> > > > > > > > event1: 'will attend' 'will not attend'
> > > > > > > > event2: 'will attend' 'will not attend'
>
> > > > > > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > > > > > inserting in attendee
>
> > > > > > > > I hope this helps
>
> > > > > > > No, that is not what I mean.
>
> > > > > > > You have a table attendee which contains events. Personally I would
> > > > > > > have an events table to contain the events.
>
> > > > > > > Now you tell us that the attendee table has events and attendees and
> > > > > > > it is possible for an attendee to say that they will not attend the
> > > > > > > event. I have to say that someone who will not attend an event will by
> > > > > > > definition not be an attendee!
>
> > > > > > > My question is, if for some reason you have all your events listed in
> > > > > > > the attendee table and the attendee has not put in there a record
> > > > > > > saying that he will or will not attend the event, how did the record
> > > > > > > with the event id and attendee id get in the table in the first place?
>
> > > > > > I have an event table and attendee table (events has many attendees).
> > > > > > The event_id in attendee points to the key of the event table.
> > > > > > event table: id, name, time
> > > > > > attendee table, id, user_id, event_id, present
>
> > > > > Things aren't as stated in your first post.
>
> > > > > However you STILL haven't answered my question.
>
> > > > > How does an attendeed get an entry in the attendee table against a
> > > > > particular event? You have suggested that the entry can be there
> > > > > before the prospective attendee has looked at the table, so HOW did it
> > > > > getthere?
>
> > > > Sorry, I have a hard time understanding your question.
>
> > > > The attendee can enter because he sees all available events (with
> > > > attend and not attend link behind): the one he attends, the one he
> > > > will not attend and the one he hasn't yet voted on (in this case there
> > > > is no attendee record for that user for that event.)
> > > > we have 3 cases:
> > > > - on the list of events the user clicks an event he previously said he
> > > > would attend to not attend. The attendees present record changes to 0
> > > > - on the list of events the user clicks an event he previously said he
> > > > would not attend to attend. The attendees present record changes to 1
> > > > - on the list of events the user clicks an event he hasn't 'voted' on
> > > > yet. An attendees record is created in the attendee table
>
> > > > i hope this helps
>
> > > This doesn't make much sense to me.
>
> > > Why have a record in the attendee table if the person will not be an
> > > attendee?
> > > Why put a record in the attendee table just because a person has
> > > looked at the detals of an event from the event table?
>
> > > Why not just list the events from the event table, LEFT JOINED to the
> > > attendee table. If there is no entry in the attendee table, the person
> > > is not yet an attendee. If you really want to have a record in the
> > > attendees table for people who will certainly not be attendees (this
> > > seems crazy) then I still don't see why you would want to put an entry
> > > in there when all the person has done is looked at the event.
>
> > Cos there is a clear difference between saying you don't attend or
> > haven't said yet you will or will not attend. This way I can show the
> > user whether he already entered his attendence for this event. (which
> > he has to do) It works as a reminder
>
> A table should not be called attendee, if it contains records of non-
> attendees. This is confusing to future maintainers.
>
> > > Why put a record in the attendee table just because a person has
> > > looked at the detals of an event from the event table?
>
> > This doesn't happen unless the user himself votes his presense (via
> > the link)
>
> If no record is put into this table UNLESS the user votes his presence
> or absence, how can you have "attendee.present can be null if the user
> didn't yet tell if he would come to the event."
>
> I have been asking and asking how this null record gets into the
> table!

There is a difference between a null record, which means no record at
all, and a null cell which means a record where a cell (eg
attendee.present = null). I have always talked about the first case

Re: difficult query

On 21 Dec, 13:56, Tarscher wrote:
> On 21 dec, 14:02, Captain Paralytic wrote:
>
>
>
> > On 21 Dec, 12:06, Tarscher wrote:
>
> > > On 21 dec, 12:52, Captain Paralytic wrote:
>
> > > > On 21 Dec, 11:43, Tarscher wrote:
>
> > > > > On 21 dec, 12:24, Captain Paralytic wrote:
>
> > > > > > On 21 Dec, 11:18,Tarscher wrote:
>
> > > > > > > On 21 dec, 12:12, Captain Paralytic wrote:
>
> > > > > > > > On 21 Dec, 10:52,Tarscher wrote:
>
> > > > > > > > > On 21 dec, 11:45, Captain Paralytic wrote:
>
> > > > > > > > > > On 21 Dec, 10:36,Tarscher wrote:
>
> > > > > > > > > > > On 21 dec, 11:13, Captain Paralytic wrote:
>
> > > > > > > > > > > > On 21 Dec, 08:43,Tarscher wrote:
>
> > > > > > > > > > > > > Hi all,
>
> > > > > > > > > > > > > I have events containing attendees (events has many attendees). The
> > > > > > > > > > > > > attendee table tells whether a user will attend the event or not. I
> > > > > > > > > > > > > want to build a query that returns all the different events to a user
> > > > > > > > > > > > > and if he will attend the event or not (or hasn't filled it in yet)
>
> > > > > > > > > > > > > the returned result could be something like:
>
> > > > > > > > > > > > > event.id attendees.user_id attendee.present
> > > > > > > > > > > > > 1 1 0
> > > > > > > > > > > > > 2 1
> > > > > > > > > > > > > 3 1 1
>
> > > > > > > > > > > > > Please note that attendee.present can be null if the user didn't yet
> > > > > > > > > > > > > tell if he would come to the event.
>
> > > > > > > > > > > > > Can this be done?
>
> > > > > > > > > > > > > thanks
> > > > > > > > > > > > > Stijn
>
> > > > > > > > > > > > And this has what to do with php?
>
> > > > > > > > > > > > You would be better to ask this in a database group.
>
> > > > > > > > > > > > However some questions:
> > > > > > > > > > > > If a user is querying the database to find if he will be attending the
> > > > > > > > > > > > event, why does his own ID need to be present in the output?
> > > > > > > > > > > > How does the attendee's id get into the table against an event in the
> > > > > > > > > > > > first place?
>
> > > > > > > > > > > I indeed don't need the user_id since it is stored in the session. It
> > > > > > > > > > > was just to clarify that the query need to return 1 user.
>
> > > > > > > > > > > Via the session the user_id stored in the session.
>
> > > > > > > > > > > Regards
>
> > > > > > > > > > I don't understand how
> > > > > > > > > > "Via the session the user_id stored in the session."
>
> > > > > > > > > > answers the question
> > > > > > > > > > "How does the attendee's id get into the table against an event in the
> > > > > > > > > > first place?"
>
> > > > > > > > > sorry, a typo
>
> > > > > > > > > INSERT INTO attendee (event_id, user_id) VALUES ($event_id,
> > > > > > > > > session['user_id'])
>
> > > > > > > > > I get the event_id via the url since the user does this per event.
> > > > > > > > > eg
> > > > > > > > > event1: 'will attend' 'will not attend'
> > > > > > > > > event2: 'will attend' 'will not attend'
>
> > > > > > > > > The 'will attend' and 'will not attend' links point to the sql query
> > > > > > > > > inserting in attendee
>
> > > > > > > > > I hope this helps
>
> > > > > > > > No, that is not what I mean.
>
> > > > > > > > You have a table attendee which contains events. Personally I would
> > > > > > > > have an events table to contain the events.
>
> > > > > > > > Now you tell us that the attendee table has events and attendees and
> > > > > > > > it is possible for an attendee to say that they will not attend the
> > > > > > > > event. I have to say that someone who will not attend an event will by
> > > > > > > > definition not be an attendee!
>
> > > > > > > > My question is, if for some reason you have all your events listed in
> > > > > > > > the attendee table and the attendee has not put in there a record
> > > > > > > > saying that he will or will not attend the event, how did the record
> > > > > > > > with the event id and attendee id get in the table in the first place?
>
> > > > > > > I have an event table and attendee table (events has many attendees).
> > > > > > > The event_id in attendee points to the key of the event table.
> > > > > > > event table: id, name, time
> > > > > > > attendee table, id, user_id, event_id, present
>
> > > > > > Things aren't as stated in your first post.
>
> > > > > > However you STILL haven't answered my question.
>
> > > > > > How does an attendeed get an entry in the attendee table against a
> > > > > > particular event? You have suggested that the entry can be there
> > > > > > before the prospective attendee has looked at the table, so HOW did it
> > > > > > getthere?
>
> > > > > Sorry, I have a hard time understanding your question.
>
> > > > > The attendee can enter because he sees all available events (with
> > > > > attend and not attend link behind): the one he attends, the one he
> > > > > will not attend and the one he hasn't yet voted on (in this case there
> > > > > is no attendee record for that user for that event.)
> > > > > we have 3 cases:
> > > > > - on the list of events the user clicks an event he previously said he
> > > > > would attend to not attend. The attendees present record changes to 0
> > > > > - on the list of events the user clicks an event he previously said he
> > > > > would not attend to attend. The attendees present record changes to 1
> > > > > - on the list of events the user clicks an event he hasn't 'voted' on
> > > > > yet. An attendees record is created in the attendee table
>
> > > > > i hope this helps
>
> > > > This doesn't make much sense to me.
>
> > > > Why have a record in the attendee table if the person will not be an
> > > > attendee?
> > > > Why put a record in the attendee table just because a person has
> > > > looked at the detals of an event from the event table?
>
> > > > Why not just list the events from the event table, LEFT JOINED to the
> > > > attendee table. If there is no entry in the attendee table, the person
> > > > is not yet an attendee. If you really want to have a record in the
> > > > attendees table for people who will certainly not be attendees (this
> > > > seems crazy) then I still don't see why you would want to put an entry
> > > > in there when all the person has done is looked at the event.
>
> > > Cos there is a clear difference between saying you don't attend or
> > > haven't said yet you will or will not attend. This way I can show the
> > > user whether he already entered his attendence for this event. (which
> > > he has to do) It works as a reminder
>
> > A table should not be called attendee, if it contains records of non-
> > attendees. This is confusing to future maintainers.
>
> > > > Why put a record in the attendee table just because a person has
> > > > looked at the detals of an event from the event table?
>
> > > This doesn't happen unless the user himself votes his presense (via
> > > the link)
>
> > If no record is put into this table UNLESS the user votes his presence
> > or absence, how can you have "attendee.present can be null if the user
> > didn't yet tell if he would come to the event."
>
> > I have been asking and asking how this null record gets into the
> > table!
>
> There is a difference between a null record, which means no record at
> all, and a null cell which means a record where a cell (eg
> attendee.present = null). I have always talked about the first case

No record at all is not a NULL record, otherwise there would always be
an infinite number of records in any table, because they weren't there
at all!

You really haven't exlained very well at all what you are doing.

Jeff has told me not to jump to conclusions, but you simply have not
given us the full story!

I think you just need to do a LEFT join between 2 tables, but you
really need to lay out what tables you have and what they hold.

Re: difficult query

On 21 Dec, 13:56, Tarscher wrote:
> On 21 dec, 14:02, Captain Paralytic wrote:
>

So here is my guess.
Assuming that you have a table events and a table people, I would have
a link table events_people. I would not call it attendee as it ontains
both attendees and non-attendees.

The query would be something like:

SELECT
e.event,
ep.status
FROM events e
LEFT JOIN events_people ep USING(event_id)
WHERE ep.person_id = {my_person_id}

Re: difficult query

On 21 dec, 15:22, Captain Paralytic wrote:
> On 21 Dec, 13:56,Tarscher wrote:
>
> > On 21 dec, 14:02, Captain Paralytic wrote:
>
> So here is my guess.
> Assuming that you have a table events and a table people, I would have
> a link table events_people. I would not call it attendee as it ontains
> both attendees and non-attendees.
>
> The query would be something like:
>
> SELECT
> e.event,
> ep.status
> FROM events e
> LEFT JOIN events_people ep USING(event_id)
> WHERE ep.person_id = {my_person_id}

I think your aproach is indeed better than having an attendee table
(which name is indeed not that well choisen)

thanks

Re: difficult query

On Dec 21, 10:43=A0am, Tarscher wrote:
> Hi all,
>
> I have events containing attendees (events has many attendees). The
> attendee table tells whether a user will attend the event or not. I
> want to build a query that returns all the different events to a user
> and if he will attend the event or not (or hasn't filled it in yet)
>
> the returned result could be something like:
>
> event.id =A0 =A0 attendees.user_id =A0 =A0 attendee.present
> =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00
> =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01
> =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A01
>
> Please note that attendee.present can be null if the user didn't yet
> tell if he would come to the event.
>
> Can this be done?
>
> thanks
> Stijn

There is no need to define events table, define attended events in
users table in arrays;

$events_attended=3Darray("1","5","3"); and goes so on..

Re: difficult query

On Dec 21, 7:09=A0pm, Betikci Boris wrote:
> On Dec 21, 10:43=A0am, Tarscher wrote:
>
>
>
>
>
> > Hi all,
>
> > I have events containing attendees (events has many attendees). The
> > attendee table tells whether a user will attend the event or not. I
> > want to build a query that returns all the different events to a user
> > and if he will attend the event or not (or hasn't filled it in yet)
>
> > the returned result could be something like:
>
> > event.id =A0 =A0 attendees.user_id =A0 =A0 attendee.present
> > =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A00
> > =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01
> > =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A01
>
> > Please note that attendee.present can be null if the user didn't yet
> > tell if he would come to the event.
>
> > Can this be done?
>
> > thanks
> > Stijn
>
> There is no need to define events table, define attended events in
> users table in arrays;
>
> $events_attended=3Darray("1","5","3"); and goes so on..- Hide quoted text =
-
>
> - Show quoted text -

Addition: You may want to store the date store date as the key... or
use more complex multi-dimentional array to store all -date -event -
result, etc..

Re: difficult query

Betikci Boris wrote:
> On Dec 21, 10:43 am, Tarscher wrote:
>> Hi all,
>>
>> I have events containing attendees (events has many attendees). The
>> attendee table tells whether a user will attend the event or not. I
>> want to build a query that returns all the different events to a user
>> and if he will attend the event or not (or hasn't filled it in yet)
>>
>> the returned result could be something like:
>>
>> event.id attendees.user_id attendee.present
>> 1 1 0
>> 2 1
>> 3 1 1
>>
>> Please note that attendee.present can be null if the user didn't yet
>> tell if he would come to the event.
>>
>> Can this be done?
>>
>> thanks
>> Stijn
>
> There is no need to define events table, define attended events in
> users table in arrays;
>
> $events_attended=array("1","5","3"); and goes so on..

Do you know what you are talking about?