[SOLVED] Help with query...

[jmcall10]

I have 3 tables:

 

tbl_people (pid, name);

tbl_events (eid, date);

tbl_people_events (peid, pid, eid);

 

so,

tbl_people stores peoples names;

tbl_events stores event dates;

tbl_people_events stores who attended what event;

 

 

I want to display a grid like effect with a 'Y' if someone has attended the event and a 'N' if they havnt

(pls see attachement)

Does this make sense?

 

 

[attachment deleted by admin]

[bluejay002]

with this, since it is very much dependent on dates, what i usually do is that i create a derived table which will compose of the dates i want, then from there, base tbl_people_events (use left join to check those present and not present) and joins with the other tables.

 

to arrange the display, use group by then order by.

 

 

[Maq]

Is this homework...?  Anyway this is simple, you first have to join all the tables.  Then all you do is display the data in a table, probably by using HTML.  Make sense?

[jmcall10]

lol, no this is not homework (im 26)

 

I want to track who has attended events and how many in total they have attended.

 

Do you know the query that would do this?

[Maq]

$results = SELECT * FROM (tbl_people LEFT JOIN tbl_people ON tbl_people_events.pid = tbl_people.pid)
LEFT JOIN tbl_events ON tbl_people_events.eid = tbl_events.eid;
while ($data = mysql_fetch_array($results)) { 
//Display your table using $data['name'] etc...

 

You would also need another field for the dates.  I haven't tested this code it probably doesn't work but it should give you an idea.

[jmcall10]

Hmm I cant get this to work

 

Im so struggling lol

[jmcall10]

Can anyone help me get the corect query?

 

Thanks in advance

[fenway]

What do you have so far?

[jmcall10]

well to be perfectly honest I am really rotten at sql so when it comes to joining tables I am completely lost.

 

So I guess I have nothing. I tried using the above qwuery and couldnt get it to execute. I dont have enough knowledge or experience to edit it.

 

Could you please help me on this one?

[fenway]

It's a bit tricky... do you have an integers table? Because you'll need to generate "rows" for each of the dates in the date range you want before you'll be able to make the grid.

[jmcall10]

I am really struggling lol

 

I use php, I have created a table and the first thing I have done is made a row with all the dates.

 

Now I need to generate the grid

[fenway]

I meant dynamically....

[jmcall10]

yea I have dynamically created a table like:

 

<table border="1" align="center">

<tr>
<td></td>
<?PHP
$events = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError");
while (
$qry1 = mysql_fetch_array($events)) 
{
echo "<td>$qry1[date]</td>"; 
}
?>
</tr>

[fenway]

I meant created the rows dynamically for any arbitrary date range whatsoever -- you've hard-coded the dates, I presume.

[jmcall10]

hmm your on the verge of losing me I think

 

These dates are in a table called tbl_events.

 

So the above code loops through and echo's each date in that table.

 

Only dates within this table are dates that an event will be on

[fenway]

Only dates within this table are dates that an event will be on

OK, I suppose that's ok.  Next step is to join all 3 table stogether.

[jmcall10]

lol and that is where I am lost

[jmcall10]

Ok I think I have sorted this out.

 

I have posted a new topic in the php forum to see if it is the most efficient way.

 

For those that were tracking this thread you can find the above mentioned post at:

 

http://www.phpfreaks.com/forums/index.php/topic,216877.0.html

 

Thanks for all your help guys