PHP MySQL ORDER BY无法正常工作

Question

I have a query:

$team_member_result = mysql_query("SELECT * FROM teams 
             WHERE team_creator LIKE '%,$player_main_id,%' 
               OR team_owner LIKE '%,$player_main_id,%' 
               OR team_leaders LIKE '%,$player_main_id,%' 
               OR team_captains LIKE '%,$player_main_id,%' 
               OR team_members LIKE '%,$player_main_id,%' 
             ORDER BY team_creator ASC, team_owner ASC, 
                      team_leaders ASC, team_captains ASC, 
                      team_members ASC");

It works at the start, but if the member is not a creator it starts to sort unordered, so basically if they are a member it will show before they are a leader.

Not sure what to do,

Thanks!

Answer 1

The main problem is that your tables are not in the Third normal form. Because of that you try to develop an SQL query which ultimately doesn't work, specially the ORDER BY part (besides the already mentioned LIKE filter with commas).

Bring your tables in the 3NF, then you can write an ORDER BY statement which makes sense.

Btw.: Don't use the mysql_*() functions anymore, they are deprecated. Use PDO instead.