How to display multiple Post meta_key/meta_values by SQL query

Question

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.

Answer 1

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 |
+-----+------------+--------------+-------------+-----------+---------------+