Page 1 of 1

id v id

Posted: Wed Oct 27, 2004 8:16 pm
by phase
hello there,

im relatively new to php but im undertaking a task which is starting to baffle me...

firstly im creating a league which has teams, fixtures, pm systemetc etc

i have created a basic one but since im so new my design was not good enough and am looking to redo the whole thing.

ok onto my current problem,

each team in the league is assigned an id of course and this all works fine, got the teams up the leagues and all that jazz, but when it comes to fixtures i have a problem,

i want team_id v team_id but i couldnt find a way of doing this succesfully so i resorted to making a fixture table with team1_id v team2_id this then means to do a search for a teams fixtures i have to do select blah from fixtures where team1_id=$team_id OR team2_id=$team_id

this of course works but is messy and generates probelms for me when adding to the scripts and doing table joins etc

as i said im quite new to programming so i dont know if i explained this well but it is causing me problems,

how can i make it so i can do a fixtures table with two fields of id, or am i looking at it from a completely wrong angle?

any help on this would be greatly appreciated

thanks

pure_phase

Posted: Thu Oct 28, 2004 10:51 am
by Draco_03
I could help if I understood what you said
Sorry but if you want to give us (or maybe i'm the only one lost here) more info.
Maybe it's because I don't understand well the word "fixture"
definition I found : Personal property that becomes attached to and is so closely associated with real property that it becomes a part of the real property.

give me an exemple of what you want to do, I'll try my best to help you

Posted: Thu Oct 28, 2004 6:34 pm
by phase
hello and thanks for the reply

basically as a best example i have a league, lets say a football league for examples sake

i have teams who have their own id and i want to create fixtures, a fixture is a date, time and details of each match one team versus another

so my problem is creating a fixture table for these games

if each team has an id i would want a fixture table with fields of id v id, on this date, with these rules etc etc

i cant create a table with two fields named id so i have had to name the team1_id and team2_id which then causes problems in my searching later on.

you have totally misunderstood the word fixture, a league fixture is a formaly organised game between two participants within the league.

i hope thi smakes it a bit clearer.

phase

Posted: Thu Oct 28, 2004 6:57 pm
by kettle_drum
You have the correct idea but not the naming. In a typical football game there would be a home and away team. So you can simply have a fixtures table with the following:

Code: Select all

home - holds the home teams id
away - holds the away teams id
date
info
Then you can search for the team via the id and find out who there are playing and when.

Code: Select all

//leeds = 41

SELECT * FROM fixtures WHERE home = '41' //selects all of leeds home games
SELECT * FROM fixtures WHERE home = '41' OR away = '41' //selects all of leeds games