php mysql 返回数组类型,PHP + MySQL的从数据库中读取结果并返回类似的对象作为JSON数组的数组...

User has items.

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

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

Here is my 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);

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)

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?

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

解决方案

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);

}

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