returning JSON object

Question

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

?>

Is there a reason you're doing 3 completely separate queries instead of joining tables? It's obvious that a user has items in a cart, an item in store has photos, and that photos has an itemID column to retrieve that relationship. It would be more efficient to join that relationship instead of perform two or even three unique queries. — skrilled, Apr 17 '14 at 22:40
am new to php ,how can i simplify?knowing that am querying data from 3 different rows. — user3476925, Apr 17 '14 at 23:00
don't use phps mysql, use mysqli instead: http://stackoverflow.com/a/14110189/1612852 mysqli_query > mysql_query — stepquick, Apr 17 '14 at 23:09

score 2 · Answer 1 · answered Apr 17 '14 at 22:43

2

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.

answered Apr 17 '14 at 22:43

ibtarek

795
4
16

why should i set a Content-Type ? i echoed JSON in another query without setting it. – user3476925 Apr 17 '14 at 23:03
It is always better to issue the right content-type header for the user agent to know how to exactly process the reponse. The response body won't change though. – ibtarek Apr 18 '14 at 02:45
He needs $row2 and $row3 in the same JSON structure – shadowfox Apr 22 '14 at 15:44

score 0 · Answer 2 · edited May 23 '17 at 10:32

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"
}
]

I added an example of what that might look like. You may not want to use inner joins though. — shadowfox, Apr 18 '14 at 18:33
thanks shadowfox,that really helped a lot,i really appreciate that. — user3476925, Apr 19 '14 at 21:22
shadowfox please bro 1 more question,how to retrieve description,price and quantity from store,and photo from photos? — user3476925, Apr 19 '14 at 23:07
You can learn about different SQL Joins here: http://www.w3schools.com/sql/sql_join.asp If I have time tomorrow I can look into it more for you — shadowfox, Apr 21 '14 at 22:53

returning JSON object

2 Answers2