php - 来自 WordPress $wpdb->get_results 数组的自定义 JSON 数组

Question

目前我有一个 JSON 对象数组，如下所示:

{
    "userid" : "3",
    "name" : "UP DISPLAYNAME",
    "usermeta" : {
        "nickname" : "UP"
    }
}, {
    "userid" : "3",
    "name" : "UP DISPLAYNAME",
    "usermeta" : {
        "first_name" : "Nicky"
    }
}, {
    "userid" : "3",
    "name" : "UP DISPLAYNAME",
    "usermeta" : {
        "last_name" : "PH"
    }
}

}

正如您所看到的，它多次循环同一用户，这并不理想。我希望得到一个如下所示的输出:

“用户ID”:3，“名称”:用户名，“用户元”:{ “昵称”:“UP”， “名字”:“尼基” “姓氏”:“PH”}

但我对如何做到这一点有点困惑 - 我目前得到的是以下内容:

// select only columns you need
$sql = "SELECT wptest_users.ID, wptest_users.display_name, wptest_pmpro_memberships_users.user_id, wptest_usermeta.user_id, wptest_usermeta.meta_key, wptest_usermeta.meta_value FROM wptest_users, wptest_pmpro_memberships_users, wptest_usermeta WHERE wptest_pmpro_memberships_users.membership_id = 1 AND wptest_pmpro_memberships_users.status = 'active' AND wptest_pmpro_memberships_users.user_id = wptest_users.ID AND wptest_usermeta.user_id = wptest_users.ID";

$count = 0; // this is for $preJSON[] index

foreach( $wpdb->get_results( $sql ) as $key => $row ) {

    // each column in your row will now be accessible like this: 
    // $my_column = $row->column_name;
    // now we can do:

    $userid = $row->ID;
    $displayname = $row->display_name;
    $user_meta = $row->meta_value;
    $metadata = array(
        $row->meta_key => $user_meta
    );

    // now we can build a row of this information in our master array
    $preJSON[$count] = array(
        "userid" => $userid,
        "name" => $displayname,
        "usermeta" => $metadata
    );

    // increment the index
    ++$count;
}

// after foreach
// send the whole array to json
return wp_send_json( $preJSON );

Answer 1

一些事情:

1) 不需要与 *_usermeta 表进行另一次连接，因为 WP 具有可以立即为您处理此操作的函数(例如 get_userdata )。我冒昧地将其从 QUERY 中删除

2)我注释掉了你的代码，但它应该足够接近:

<?php

// select only columns you need
$sql = $wpdb->prepare("SELECT
      wptest_users.ID,
      wptest_users.display_name,
      wptest_pmpro_memberships_users.user_id,

      FROM
      wptest_users,
      wptest_pmpro_memberships_users,
     WHERE
      wptest_pmpro_memberships_users.membership_id = 1
      AND wptest_pmpro_memberships_users.status = 'active'
      AND wptest_pmpro_memberships_users.user_id = wptest_users.ID");


$count = 0; // this is for $preJSON[] index
$user_object = array();
foreach( $wpdb->get_results( $sql ) as $key => $row ) {
  // "userid" : 3, "name" : THENAMEOFUSER, "usermeta": { "nickname" : "UP", "first_name" : "Nicky" "last_name" : "PH" }
    // each column in your row will now be accessible like this:
    // $my_column = $row->column_name;
    // now we can do:

    $userid = $row->ID;
    // $displayname = $row->display_name;
    // $user_meta = $row->meta_value;
    // $metadata = array(
    //     $row->meta_key => $user_meta
    // );
    $user_info = get_userdata($userid);
    $firstname = $user_info->first_name;
    $lastname = $user_info->last_name;
    $display_name = $user_info->display_name;


    // now we can build a row of this information in our master array
    // $preJSON[$count] = array(
    //     "userid" => $userid,
    //     // "name" => $displayname,
    //     // "usermeta" => $metadata
    // );

    $user_object[$count]= array(
        "userid"=>$userid,
        "name"=>$display_name,
        "usermeta"=>array(
          "nickname" : $display_name,
          "first_name" : $firstname,
          "last_name" : $lastname
        )
      );

    // increment the index
    ++$count;
}

// after foreach
// send the whole array to json
return wp_send_json( $user_object );
?>

注意:对于昵称，我使用 $display_name 但如果是其他名称，您可以根据需要调整变量!