3

I haven't found a solution to displaying multiple postmeta meta_key/meta_values by SQL query of a post. I am using the Advanced Custom Fields (ACF) plugin on another website and trying to pull the contents of the posts on a secondary website. Everything displays well except it is not able to display more meta_values.

Here is my code:

$mydb = new wpdb('database_name','database_user','database_pw','database_host');
    $rows = $mydb->get_results("
        SELECT P.ID, P.post_title, P.post_content, P.post_author, meta_value
        FROM wp_posts AS P
        LEFT JOIN wp_postmeta AS PM on PM.post_id = P.ID 
        WHERE P.post_type = 'post' and P.post_status = 'publish'

        and meta_key = 'job_title'
        and meta_key = 'company_email' //2nd meta_key I need to work

        ORDER BY P.post_date DESC
        ");

foreach ($rows as $row) :
    echo $row->meta_value;
endforeach;

As you can see, I am able to retrieve just one meta_key/meta_value but have multiple fields to display.

1
  • Have you considered using a REST API endpoint instead so that you can do it on the other site using APIs instead of a raw SQL query? This query is going to be very expensive
    – Tom J Nowell
    Commented Jan 10, 2017 at 16:22

1 Answer 1

3

change where clause with or

SELECT P.ID, P.post_title, P.post_content, P.post_author, meta_value
FROM wp_posts AS P
LEFT JOIN wp_postmeta AS PM on PM.post_id = P.ID
WHERE P.post_type = 'post' and P.post_status = 'publish' and ( meta_key = 'job_title' or meta_key = 'company_email' )
ORDER BY P.post_date DESC

but this sql will return the same post in 2 row for your meta key and value; and you will get something like

+-----+------------+--------------+-------------+----------+------------+
| ID  | post_title | post_content | post_author | meta_key | meta_value |
+-----+------------+--------------+-------------+----------+------------+
| 10  | Post Title | Post Content |           1 |   key1   |   value1   |
+-----+------------+--------------+-------------+----------+------------+
| 10  | Post Title | Post Content |           1 |   key2   |   value2   |
+-----+------------+--------------+-------------+----------+------------+

and if you need to get the meta key as column and meta value as value of the correspondence column you need to pivot the row to column

SELECT P.ID, P.post_title, P.post_content, P.post_author,
    MAX(IF(PM.meta_key = 'job_title', PM.meta_value, NULL)) AS job_title,
    MAX(IF(PM.meta_key = 'company_email', PM.meta_value, NULL)) AS company_email
FROM wp_posts AS P
LEFT JOIN wp_postmeta AS PM on PM.post_id = P.ID
WHERE P.post_type = 'post' and P.post_status = 'publish'
ORDER BY P.post_date DESC

this will return the result as a single row like this

+-----+------------+--------------+-------------+-----------+---------------+
| ID  | post_title | post_content | post_author | job_title | company_email |
+-----+------------+--------------+-------------+-----------+---------------+
| 10  | Post Title | Post Content |           1 | job title | company@email |
+-----+------------+--------------+-------------+-----------+---------------+
2
  • Thank you, your second method works well but it returns only a single row as mentioned. Is there a way to show all posts in P.post_type = 'post'?
    – Jnsn_
    Commented Jan 11, 2017 at 13:17
  • 1
    if you have more then 1 post in your db (check your where clause) this will return the rows even if those post doesn't have job_title or company_email meta. Commented Jan 12, 2017 at 3:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.