Relational Database query MySQL

Question

I have modified the names of my Tables. The sportevents table is the main table and it should get its data from the tables: event_date, events, results, and members. Is there a way to do this. Please not i need to keep this structure.

sportevents (link table)
• id
• event_id
• date_id
• result_id

event_date
• id
• date

events
• id
• eventname

results
• id
• result

members
• id (the ID number of a person)

userlogin
• id
• username
• password

I have managed to get it right without joins. The following:

$query = "SELECT * FROM members, sportevents, dates, results, event, userlogin ". 
         "WHERE userlogin.username = '$un' " . 
         "AND sportevents.id = members.id " . 
         "AND sportevents.event_id = event.id " .
         "AND sportevents.date_id = dates.id " .
         "AND sportevents.result_id = results.id";

$results = mysql_query($query)
    or die(mysql_error());
    while ($row = mysql_fetch_array($results)) {
    echo $row['eventname'];
    echo " - ";
    echo $row['year'];
    echo " - ";
    echo $row['result'];

    }

Gives me this:

Karoo Cycle - 2008 - 1h14mins

Should the tables `members` and `login` tables not be one table? — pb149, Sep 26 '11 at 08:32
the members table contain many fields. there is a field named "id" in members and in main_events(which is the link table) there is a field "id" which would be main_events.id = members = id — SebastianOpperman, Sep 26 '11 at 08:38
How is result linked to the other tables? You can also have an events table with `event_id, eventname, year, result` assuming each event can have only 1 event name, time, and result — Jan S, Sep 26 '11 at 08:38
Yes Pete171, but the my subordinate wants to keep the user log in separate from the members where all the details are stored. It is frustrating i agree. — SebastianOpperman, Sep 26 '11 at 08:40
It needs to be dynamic. Basically there are many events added to the list. Each event occurs yearly so there are different result. a Participant may have done the 2007 Cycle race, the 2008 and 2009 Road race. Thats why i cant have them all in the same table — SebastianOpperman, Sep 26 '11 at 08:43
so the colum id in members and main_event is the member-id, and the column id in event, event_date and result is the event-id? To me it is not clear what you mean by "query" - are you looking for some php-code, or for an sql-query? — miherrma, Sep 26 '11 at 08:50
Basicaly i want to use a $query = " " to link all the bottom table's respective fields to the main_events table fields. The main_events table has a foreign key to members, event_date, result and events. But i cannot link all four tables to the main_events table — SebastianOpperman, Sep 26 '11 at 08:55

score 1 · Accepted Answer · answered Sep 26 '11 at 08:48

I presume that you have member's data stored in to $_SESSION['member'] or smth. And i dont know exactly why you have separated tabes on event, event_date (there should be one in my perspective) and I guess that main_events is something like event's group/category.

and you are missing MEMBER - EVENT link. add field 'member_id' to the events table.

If that's what it is then it's something like that.

$q = 'SELECT e.*, ed.year, r.result FROM events As e 
LEFT JOIN event_date As ed ON ed.id = e.id
LEFT JOIN result As r ON r.id = e.id
WHERE e.member_id = ' . $_SESSION['member']['id'];

from that you get

event id, event name, event year, result. "JohnSmith" you can get from $_SESSION['member'].

If you decide not to use separate tables for event, event_date, result and use only one table with more fields u can do this without any LEFT JOINS just very simple SELECT query.

score 0 · Answer 2 · answered Sep 26 '11 at 08:48

0

First, you need to link event with a result. It depends on your domain model, but I'll assume you have only one result per event, so I'll add result_id to the events table. Also, you need to link members with events somehow, I'll use

events_members
member_id
main_event_id

table. With this vcersion you'll be able to have multiple members participate in multiple events.

And finally, the query would be something like the following:

select m.username, e.eventname, y.year, r.result
from members m
join events_members em on em.member_id = m.id
join main_events me on em.main_event_id = me.id
join event e on e.id = me.event_id
join year y on y.id = me.year_id
join result r on r.id = e.result_id
where m.username = $username

answered Sep 26 '11 at 08:48

J0HN

26,063
5
54
85

I see you use Aliases. Is it necessary to use them? Does SQl pick them up automaticaly? – SebastianOpperman Sep 26 '11 at 09:09
It's not necessary, but otherwise you'll have to write full names in join conditions, i.e. `e.id=me.event_id` becomes `event.id = main_event.event_id`. Don't get what do you mean by "pick up automatically", but if you meant that they are automatically assigned - no. They are assigned with `result r`. The more verbose way is `result as r`, so, it's `table_name AS alias`, but the `as` keyword is optional. – J0HN Sep 26 '11 at 09:13
Sorry for the dumb q, i'm just getting errors along the way. with every answer. i cant seem to get this working... – SebastianOpperman Sep 26 '11 at 09:24
I am not getting right. I keep getting these error: Unknown column 'login.username' in 'field list' – SebastianOpperman Sep 26 '11 at 09:45
Are you sure you have `login` table? – J0HN Sep 26 '11 at 09:53
i have changed it to userlogin. Is there a way to link them by WHERE AND clauses. I can only link two then it gives unknown column errors – SebastianOpperman Sep 26 '11 at 10:13

Relational Database query MySQL

2 Answers2