Select custom post field (ACF) from wordpress posts

I created a custom post type : stock

In stock, I have add a lot of custom field with ACF.

I created maybe 20 stock.

Now, I’m trying to get the values of the different custom field (in workbench) for each post.

Here some field : prix (price), ville (city), nombre de chambres (number of rooms)

The result I want :

post_title | prix | ville | nombre de chambres
Title 1    | 20   | paris | 3
Title 2    | 40   | marseille| 4
Title 3    | 30   | royan| 2
etc...

The code I tried :

SELECT post_title,
(select meta_value from mod803_postmeta where meta_key = 'prix')
FROM mod803_posts
WHERE post_type='stock' AND post_status='publish'

I get the title and a column named : “select meta_value from mod803_postmeta where meta_key = ‘prix’” but the column is ‘NULL’.

Thank you

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

So, here the solution for get WP POST with CUSTOM POST TYPE AND ACF FIELDS :

SELECT ID, p.post_title as 'Post Title', t.name as 'Category', p.post_date as 
"Post Date", p.post_name as "Post Name" , m2.meta_value AS "Prix", m4.meta_value 
AS "Surface"
FROM mod803_posts p, mod803_terms t, mod803_term_relationships tr, 
mod803_term_taxonomy tx, mod803_postmeta m2, mod803_postmeta m4
WHERE p.post_type = 'stock'
AND p.post_status = 'publish'
AND tx.taxonomy = 'typedestock'
AND p.ID = tr.object_id
AND tr.term_taxonomy_id = t.term_id
AND tx.term_id = t.term_id
AND m2.post_id = p.id AND m2.meta_key = 'Prix de vente'
AND m2.post_id = p.id AND m4.meta_key = 'Surface'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0