1

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.

  • 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 Jan 10 '17 at 16:22
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 |
+-----+------------+--------------+-------------+-----------+---------------+
| improve this answer | |
  • 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_ Jan 11 '17 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. – Kudratullah Jan 12 '17 at 3:46

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