ORDER BY FIELD() not working

[BuildMyWeb]

mysql v5.5.34-cll

 

this query works:

SELECT t_u.*, t_a.access_lvl, t_a.biz_name from uploads_rf AS t_u, user_accts AS t_a WHERE t_u.project_id = '8' AND t_u.pipeline_item = 'rf' AND t_a.email = t_u.author ORDER BY status

there are three status values.  'open', 'closed', 'rejected'.

 

when i try sorting by the status column according to value as in such, i get 0 results. 

query_docs=SELECT t_u.*, t_a.access_lvl, t_a.biz_name from uploads_rf AS t_u, user_accts AS t_a WHERE t_u.project_id = '8' AND t_u.pipeline_item = 'rf' AND t_a.email = t_u.author ORDER BY FIELD(status, 'open', 'rejected', 'closed')

is my mysql written correctly?  any thoughts?

[jazzman1]

Since I don't know the values of status field, I'm going to use the default 1,2,3 in a CASE.

 

Try,

SELECT t_u.*, t_a.access_lvl, t_a.biz_name,
CASE status 
     WHEN 'open' THEN 1
     WHEN 'rejected' THEN 2 
     WHEN 'closed' THEN 3
  END AS s_type
FROM uploads_rf AS t_u, user_accts AS t_a 
WHERE t_u.project_id = '8' AND t_u.pipeline_item = 'rf' AND t_a.email = t_u.author 
ORDER BY FIELD(status, 'open', 'rejected', 'closed')

Edited January 14, 2014 by jazzman1

[Barand]

works for me

mysql> SELECT * FROM sort_test;
+-------------+----------+
| idsort_test | status   |
+-------------+----------+
|           1 | open     |
|           2 | rejected |
|           3 | closed   |
|           4 | open     |
|           5 | rejected |
|           6 | closed   |
|           7 | open     |
|           8 | rejected |
|           9 | closed   |
+-------------+----------+

mysql> SELECT * FROM monty.sort_test ORDER BY FIELD(status,'open','rejected','closed');
+-------------+----------+
| idsort_test | status   |
+-------------+----------+
|           1 | open     |
|           4 | open     |
|           7 | open     |
|           2 | rejected |
|           5 | rejected |
|           8 | rejected |
|           3 | closed   |
|           6 | closed   |
|           9 | closed   |
+-------------+----------+

It shouldn't affect the sort but your query has no join condition on those tables

[BuildMyWeb]

thanks for the replies guys. 

 

jazzman: havent used case statements in mysql before but ill read up on them.  its an option but not required for the FIELD() function to work properly, correct?

 

barand, thx for the alert on join condition.  sounds like youre saying my statement should be working though.  i cant figure out why im getting 0 results with it.  if i use the same exact query but switch from ORDER BY FIELD() to ORDER BY, it works great.  i dont know where to go from here.

 

there shouldnt be a compatibility issue with the FIELD() function and my version of mysql, should there?

[Barand]

The FIELD() function in the manuals is unchanged from v3.23 through to v5.7.

 

There is a similar function FIND_IN_SET(). Any luck with that one? eg

mysql> SELECT * FROM sort_test
    -> ORDER BY FIND_IN_SET(status,'open,rejected,closed');
+-------------+----------+
| idsort_test | status   |
+-------------+----------+
|           1 | open     |
|           4 | open     |
|           7 | open     |
|           2 | rejected |
|           5 | rejected |
|           8 | rejected |
|           3 | closed   |
|           6 | closed   |
|           9 | closed   |
+-------------+----------+

How is "status" defined in your table?

[jazzman1]

 

its an option but not required for the FIELD() function to work properly, correct?

 

Why not? Show us the tables structure.

[BuildMyWeb]

Name Type Collation   Null Default

status char( 8 ) utf8_bin   No open

$num_rows = $result_docs->num_rows;

echoing $num_rows returns a NULL, not zero results. 

 

FIND_IN_SET() had same result

Edited January 14, 2014 by BuildMyWeb

[Barand]

As it is CHAR( 8 ) and not VARCHAR() I was wondering if the space padding would make a difference. I altered mine to be CHAR and it made no difference - still works without changing the query

[Psycho]

Are you certain you are legitimately getting 0 results and that the query is not failing? The code you posted had some extraneous content at the beginning of the query that would cause an error.

 

 

query_docs=SELECT t_u.*, . . . 

[mac_gyver]

echoing $num_rows returns a NULL, not zero results.

 

 

your query is failing due to an error (or you are using the wrong variable, overwriting the variable...) do you have any error checking logic in your code so that you would know when and why your query is failing?

[jazzman1]

In addition, why are using utf8_bin? You're comparing a string by the binary data(value) or what?

[BuildMyWeb]

a friend got the answer real quick.  was simple, now that i see it.  wasnt identifying the table for 'status'.  the following works:

SELECT t_u.*, t_a.access_lvl, t_a.biz_name from uploads_rf AS t_u, user_accts AS t_a WHERE t_u.project_id = '8' AND t_u.pipeline_item = 'rf' AND t_a.email = t_u.author ORDER BY FIELD(t_u.status, 'open', 'rejected', 'closed')

note: ORDER BY FIELD(t_u.status, ...

 

thanks for the help everyone

Edited January 14, 2014 by BuildMyWeb