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
– skrilled, Commented Apr 17, 2014 at 22:40
am new to php ,how can i simplify?knowing that am querying data from 3 different rows. — user3476925
– user3476925, Commented Apr 17, 2014 at 23:00
don't use phps mysql, use mysqli instead: stackoverflow.com/a/14110189/1612852 mysqli_query > mysql_query — stepquick
– stepquick, Commented Apr 17, 2014 at 23:09

ibtarek · Accepted Answer · 2014-04-17 22:43:03Z

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, 2014 at 22:43

ibtarek

8055 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3476925 Over a year ago

why should i set a Content-Type ? i echoed JSON in another query without setting it.

ibtarek Over a year ago

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.

shadowfox Over a year ago

He needs $row2 and $row3 in the same JSON structure

Community · Accepted Answer · 2017-05-23 10:32:26Z

0

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

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Apr 17, 2014 at 22:44

shadowfox

5054 silver badges7 bronze badges

6 Comments

user3476925 Over a year ago

can u please help me more with that?

shadowfox Over a year ago

I added an example of what that might look like. You may not want to use inner joins though.

user3476925 Over a year ago

thanks shadowfox,that really helped a lot,i really appreciate that.

user3476925 Over a year ago

shadowfox please bro 1 more question,how to retrieve description,price and quantity from store,and photo from photos?

shadowfox Over a year ago

You can learn about different SQL Joins here: w3schools.com/sql/sql_join.asp If I have time tomorrow I can look into it more for you

|

Collectives™ on Stack Overflow

returning JSON object

2 Answers 2

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related