How to add 2 columns with same id in 1 array in php?-CodePudding

I am newbie in php and mysql and I am trying to create a JSON API but I can't figure out something. My table is like that. What I am trying to do is to get only first_name and last_name values and add them to the same array. That's what I have tried:

<?php
require_once 'wp-connect.php';

$response = array();
if($conn) {
    $sql = "SELECT * FROM wp_users
    INNER JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post'
    INNER JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID
    ORDER BY wp_posts.ID";
    $result = mysqli_query($conn, $sql);
    if($result){
        header("Content-Type: application/json");
        $i = 0;
        while($row = mysqli_fetch_assoc($result)) {
            
            $response[$i]['post_id'] = $row['ID'];
            $response[$i]['user_id'] = $row['post_author'];
            $response[$i]['post'] = $row['post_content'];
            $response[$i]['firstName'] = $row['meta_value'] = 'first_name';
            $response[$i]['lastName'] = $row['meta_value'] = 'last_name';
            $response[$i]['username'] = $row['user_nicename'];
            $response[$i]['post_date'] = $row['post_date'];
            $response[$i]['post_type'] = $row['post_type'];
            $response[$i]['post_title'] = $row['post_title'];
            $response[$i]['post_name'] = $row['post_name'];
            $i  ;
        }
        echo json_encode($response, JSON_PRETTY_PRINT);
    }
}
?>

This is how I want my JSON to look like:

{
        "post_id": "597",
        "user_id": "1",
        "post": "Hello world!!!",
        "firstName": "Achisyg",
        "lastName": "", //This is empty because last_name inside the table is empty too
        "username": "achisyg",
        "post_date": "2021-12-20 20:05:17",
        "post_type": "post",
        "post_title": "1-1-1640030717",
        "post_name": "1-1-1640030717"
    }

So, how is it possible to do what I need? I appreciate any answer!

CodePudding user response：

Until there is a dbfiddle demo, I will not test my sql, but I would create a subquery on a pivoted table -- this will flatten the rows of meta data and serve up the targeted name values in the result set. My answer also includes some other miscellaneous refinements.

header("Content-Type: application/json");
require_once 'wp-connect.php';

$result = [];
$sql = <<<SQL
SELECT * 
FROM wp_users
INNER JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post'
INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name
    FROM wp_usermeta
    GROUP BY user_id
) pivoted ON pivoted.user_id = wp_users.ID
ORDER BY wp_posts.ID
SQL;
foreach (mysqli_query($conn, $sql) as $row) {
    $result[] = [
        'post_id' => $row['ID'],
        'user_id' => $row['post_author'],
        'post' => $row['post_content'],
        'firstName' => $row['first_name'],
        'lastName' => $row['last_name'],
        'username' => $row['user_nicename'],
        'post_date' => $row['post_date'],
        'post_type' => $row['post_type'],
        'post_title' => $row['post_title'],
        'post_name' => $row['post_name'],
    ];
}
echo json_encode($result, JSON_PRETTY_PRINT);