Relational Database query MySQL

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 sporteven开发者_C百科ts.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

---

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.

---

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