PHP + MySQL的从数据库中读取结果并返回类似的对象作为JSON数组的数组 [英] Php+MySQL read results from a database and return an array of similar objects as JSON Array

问题描述

用户拥有的物品。
每个项目都有很多属性（价格，当天买入，条件，名称）

User has items. Each Item has lots of properties (price, date bought, condition, name)

我想回到所有项目及其属性的JSON数组

I want to return a json array of all items and their properties

下面是我的code：

$user_id = $_GET['user_id'];


$query_user_items        = "SELECT product_id FROM user_products WHERE user_id = :user_id";

$item_ids_array = array();
$success = false;

try {
    $sth = $connection->prepare($query_user_items);
    $sth->execute(array(':user_id' => $user_id));   
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);

    $success = true;

} catch (PDOException $ex) {

    $response["success"] = $http_response_server_error;
    $response["message"] = $http_message_server_error . " " . $ex;
    die(json_encode($response));
    $connection = null;
}

if ($success) {

    foreach($result as $key=>$value){

        $query_get_item_details = "SELECT * FROM products WHERE product_id = :product_id";

        $sth = $connection->prepare($qerty_get_item_details);
        $sth->execute(array(':product_id'=> $value));
        $record = $sth->fetch(PDO::FETCH_ASSOC);

        $item_id                = $record['product_id'];
        $item_name              = $record['product_name'];
        $item_time_added        = $record['product_time_added'];
        $item_description       = $record['product_description'];
        $item_brand             = $record['product_brand'];
        $item_price_aquired     = $record['product_price_aquired'];
        $item_bought_from_place = $record['product_bought_from_place'];

    }

    /*
    $response["success"]                        = $http_response_success;
    $response["item_ids_array"]                 = $new_array;

    echo json_encode($response);

我不是很好用PHP，我不知道是怎么回事在第二次迭代的情况发生（我不知道林权迭代以及）

Im not very good with php and I dont know what is going to happen at the second iteration (I dont know if Im iterating right as well)

我想$ ITEM_NAME将与第二个项目名称被覆盖，那么第三，在最后我将只只有一个对象吗？如何创建对象的数组和它们的相关信息，然后json_en code呢？

I suppose $item_name will be overwritten with the second item's name, then the third and at the end I will only have only one object? How can I create an array of objects and their infos and then json_encode that?

另外，我应该声明的foreach以外的这些变量？

Also, should I be declaring these variables outside of the foreach?

推荐答案

您code可以缩短如下（这样，你不使用任何循环）：

Your code could be shortened as follow (this way, you don't use any loop):

$query = "SELECT p.* FROM user_products u LEFT JOIN products p ON (p.product_id = u.product_id) WHERE u.user_id = :user_id";

try 
{
    $sth = $connection->prepare($query);
    $sth->execute(array(':user_id' => $_GET['user_id']));   
    $response = $sth->fetchAll(PDO::FETCH_ASSOC);
} 
catch (PDOException $ex) 
{
    $response["success"] = $http_response_server_error;
    $response["message"] = $http_message_server_error . " " . $ex;
}
finally
{
    $connection = null;
    echo json_encode($response);
}

然后，在你的Ajax成功处理，你可以，如果成功或邮件设置检查（如果是这样的话，那么发生错误）。否则，结果是一个数组，你会通过它循环

Then, in your ajax success handler, you can check if success or message is set (if that's the case, then an error occured). Otherwise, it is an array of results and you will loop through it

这篇关于PHP + MySQL的从数据库中读取结果并返回类似的对象作为JSON数组的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！