PHP MySQL ORDER BY not working as intended

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!

two things in each LIKE you have starting and trailing , which i think not correct. second which one you want first that condition comes first in ORDER BY clause — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Apr 3, 2016 at 13:33
Sample data would help. It seems like you're storing more than one value in a field using a set datatype in which case special set functions (like, find_in_set) may help — xQbert
– xQbert, Commented Apr 3, 2016 at 13:52

Progman · Accepted Answer · 2016-04-03 17:19:54Z

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.

answered Apr 3, 2016 at 17:19

Progman

20.1k7 gold badges58 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL ORDER BY not working as intended

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related