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
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.