the PHP code below works on querying data from wamp server and return a JSON object to be used in an XML page.
the JSON array should include description,price,quantity and an image for that item.
how to echo JSON containing data retrieved from $row2 and $row3 ?
thanks
<?php
session_start();
if(isset($_SESSION["userID"]))
{
//echo $_SESSION["userID"];
?>
<body>
<?php
include("connection_manager.php");
$query1="Select * from cart where userid='".$_SESSION["userID"]."'";
$result1=mysql_query($query1);
$row1=mysql_fetch_array($result1);
$query2="Select * from store where itemid='".$row1['itemid']."'";
$result2=mysql_query($query2);
$row2=mysql_fetch_array($result2);
$query3="Select * from photos where itemID='".$row1['itemid']."'";
$result3=mysql_query($query3);
$row3=mysql_fetch_array($result3);
?>
to output an associative array as JSON, use json_encode
to output a JSON describing $row2 :
echo json_encode($row2);
You also need to set the Content-Type header to application/json.
I would use table joins to simplify this into a single query that returns a single array, then use json_encode (see http://www.php.net/manual/en/function.json-encode.php) on that array
Perhaps something like the following:
$userId = $_SESSION['userID'];
$query = "SELECT store.*, photos.*
FROM cart
INNER JOIN store ON store.itemid = cart.itemid
INNER JOIN photos ON photos.itemid = cart.itemid
WHERE cart.userid='$userId'";
echo json_encode(mysql_fetch_array(mysql_query($query)));
That being said, see this post on why not to use mysql_ functions: Why shouldn't I use mysql_* functions in PHP?
--EDIT-- Here is an example using SQLite; you can change it to match whatever database you want to use:
$db = new SQLite3("db.db");
$query = "SELECT store.description, store.price, store.quantity, photos.photo_url
FROM cart
INNER JOIN store ON store.item_id = cart.item_id
INNER JOIN photos ON photos.item_id = cart.item_id
WHERE cart.user_id=1;";
$results = $db->query($query);
$full_array = array();
while ($row = $results->fetchArray())
{
$full_array[] = $row;
}
echo json_encode($full_array);
And the output:
[
{
"0": "a",
"1": 1,
"2": 1,
"3": "/images/photo_1.jpg",
"description": "a",
"price": 1,
"quantity": 1,
"photo_url": "/images/photo_1.jpg"
},
{
"0": "b",
"1": 2,
"2": 2,
"3": "/images/photo_2.jpg",
"description": "b",
"price": 2,
"quantity": 2,
"photo_url": "/images/photo_2.jpg"
},
{
"0": "c",
"1": 3,
"2": 3,
"3": "/images/photo_3.jpg",
"description": "c",
"price": 3,
"quantity": 3,
"photo_url": "/images/photo_3.jpg"
},
{
"0": "d",
"1": 4,
"2": 4,
"3": "/images/photo_4.jpg",
"description": "d",
"price": 4,
"quantity": 4,
"photo_url": "/images/photo_4.jpg"
},
{
"0": "e",
"1": 5,
"2": 5,
"3": "/images/photo_5.jpg",
"description": "e",
"price": 5,
"quantity": 5,
"photo_url": "/images/photo_5.jpg"
}
]