sql statement or php array?

[vampke]

Hi guys,

 

I'm working on this script where I want to get data from 2 MySQL tables:

 

table teams:

team_id, team_name

 

table fixtures:

team1_id, team2_id, team1_score, team2_score

 

The query

SELECT * FROM fixtures ORDER BY team1_id, team2_id

works like I want it to, but unfortunately I want to sort the data not on team_id, but alphabetically on team_name.

 

I don't know how to get the tables linked when the team_id is present twice in the linked table.

I'm stuck at:

SELECT f.team1_id, f.team2_id, f.team1_score, f.team2_score, t.team_name FROM fixtures f, teams t WHERE t.team_id=s.team1_id ORDER BY t.team_name";

 

This query actually returns an empty result set, I don't understand why

Probably important: the team id is an integer in the team table but it's a varchar in the fixtures table (i'm trying to adapt an existing script). This cannot be changed since there are already multiple years of data present in the database.

 

I've considered putting everything in arrays and sort them on team name, but that does not solve the team2 order.

 

What would be the easiest solution here?

[ILMV]

Have you considered using joins? As this would be a far better way of achieving your desired result.

 

If like me you are new to joins, take a look at this fantastic example...

 

http://www.codinghorror.com/blog/archives/000976.html

 

 

Regards, ILMV

[sasa]

FROM fixtures f, teams t WHERE t.team_id=s.team1_id

change to

FROM fixtures f, teams t WHERE t.team_id=f.team1_id

[vampke]

FROM fixtures f, teams t WHERE t.team_id=s.team1_id

change to

FROM fixtures f, teams t WHERE t.team_id=f.team1_id

 

hey, thanks for your trouble, this was not the problem.

I did not manage to get a proper query, so I did it with 2 seperate queries and with an array where I checked to see which team name corresponded with the team id in the array.

it works for now, but I think there might be a more elegant solution.

[kickstart]

Hi

 

Joining the tables is elegent, but made a bit less so by having the key fields stored in 2 different formats.

 

SELECT f.team1_id, f.team2_id, f.team1_score, f.team2_score, t.team_name 
FROM fixtures f JOIN teams t ON t.team_id = CONVERT(f.team1_id,signed) 
ORDER BY t.team_name";

 

All the best

 

Keith