wordpress custom mysql query

Question

In wordpress How can we fetch records in specific order within meta field value for eg I need to fetch all records where zip_code(meta key name) is all fields with 60007 first than all 60143 than 60191

Table drrr_posts

Id  | post_name |   content
2
3
4
22
32
43

Table drrr_postmeta

id | meta_key   | meta_value  |     post_id
1  |  zip_code  | 60143       |    2
2  |  zip_code  | 60007       |    3
3  |  zip_code  | 60191       |    4
4  |  zip_code  | 60143       |    22
5  |  zip_code  | 60007       |    32
6  |  zip_code  | 60143       |    43

I think issue is I am passing ORDER BY FIELD( drrr_postmeta.meta_value not zip_code which is meta_key In mysql I can easily pass the values in ORDERBY like

ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

here is what I have now My Query is

SELECT SQL_CALC_FOUND_ROWS drrr_posts.ID
FROM drrr_posts
INNER JOIN drrr_term_relationships ON ( drrr_posts.ID = drrr_term_relationships.object_id )
INNER JOIN drrr_postmeta ON ( drrr_posts.ID = drrr_postmeta.post_id )
INNER JOIN drrr_postmeta AS mt1 ON ( drrr_posts.ID = mt1.post_id )
WHERE 1 =1
AND drrr_posts.ID NOT
IN ( 91, 89, 87, 66 )
AND (
drrr_term_relationships.term_taxonomy_id
IN ( 11 )
)
AND drrr_posts.post_type = 'doctors'
AND (
(
drrr_posts.post_status = 'publish'
)
)
AND (
drrr_postmeta.meta_key = 'zip_code'
AND (
mt1.meta_key = 'zip_code'
AND CAST( mt1.meta_value AS SIGNED )
IN (
'60007', '60143', '60191', '60005', '60106', '60157', '60173', '60008', '60056', '60172'
)
)
)
GROUP BY drrr_posts.ID
ORDER BY FIELD( drrr_postmeta.meta_value, '60007', '60143', '60191', '60005', '60106', '60157', '60173', '60008', '60056', '60172' )
LIMIT 0 , 8

M Khalid Junaid · Accepted Answer · 2014-09-01 19:04:44Z

1

You can order your results by meta_key by adding one more conditional order by ,also your group by statement doesn't make any sense in absence of aggregate function ,for now you can do this

ORDER BY 
drrr_postmeta.meta_key ='zip_code' desc,
FIELD( drrr_postmeta.meta_value, '60007', '60143', '60191', '60005', '60106', '60157', '60173', '60008', '60056', '60172' )

Using drrr_postmeta.meta_key ='zip_code' will result as a boolean and returns true if both matches so it will order results first by zip_code matching meta_key and then you field() function will order results accordingly

edited Sep 1, 2014 at 19:04

answered Sep 1, 2014 at 18:21

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

5 Comments

M Khalid Junaid Over a year ago

@June try now my bad missed comma after desc

June Over a year ago

Result is still the same is there any means I can send you the database tables?

June Over a year ago

What I get is puu.sh/bhh2m/c6b423f9fc.png I wan to fetch all 60007 first than all 60143 and so on, it seems to be only looking in first 8 results?

M Khalid Junaid Over a year ago

@June yes you can provide your tables with sample data @ sql fiddle

M Khalid Junaid Over a year ago

@June in your image 60007 is at first place you are getting right results

Collectives™ on Stack Overflow

wordpress custom mysql query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related