得到嵌套的json响应

Question

I have table store_profile like this

id Storename        Storetype        address
1  Samsung Store    Electronics      Delhi
2  Levi             Clothing         Mumbai
3  Soni             Electronics      Bangalore

and second table Offers

id Store_id         offer           price
1  Samsung Store    flat 30% off     20000
2  Levi             Exchange         5000
3  Samsung Store    Exchange offer   40000

I want to get all the store details from store_profile for particular type,say Electronics and for each store their offers in single row,not repeated as I am getting from this syntax in json mysql query is :-

SELECT * , Offers.Store_id
FROM Store_profile INNER JOIN
     Offers
     ON Store_profile.Storename = Offers.Store_id AND
        Store_profile.Storetype like '%Electronics%'

Here is my php code

$sql ="SELECT * , Offers.Store_id FROM Store_profile INNER JOIN Offers ON Store_profile.Storename = Offers.Store_id AND Store_profile.Storetype like '%Electronics%' LIMIT 0 , 300 ";
$r = mysqli_query($con,$sql);

$result = array();
while($row = mysqli_fetch_array($r)){
    array_push($result,array(
        'Store_Name'=>$row['Storename'],
        'Store_address'=>$row['address'],
        'Offer_Title'=>$row['offer'],
        'MRP_Price'=>$row['price'],
        )
    );
}
echo json_encode(array('result'=>$result));
mysqli_close($con);

Answer 1

... i want to get json like this result["Storename"-Samsung Store,Store_address - Delhi,Offer[Offer_Title-Flat 30% Off,MRP_Price20000,Offer_Title-Exchange offer,MRP_Price40000 ]]

Change your while() loop and the subsequent json_encode() function call in the following way,

$result = array();
while($row = mysqli_fetch_array($r)){
    if(!isset($result[$row['Store_id']])){
        $result[$row['Store_id']] = array('Store_Name'=>$row['Storename'], 'Store_address'=>$row['address']);
    }
    $result[$row['Store_id']]['offer'][] = array('Offer_Title'=>$row['offer'],'MRP_Price'=>$row['price']);
}

echo json_encode(array('result' => array_values($result)));

Update:

From your comment,

... i have one store in store_profile but this store doesnt have any offer in Offers table. I am not getting this store's data in my json

You have to use LEFT JOIN to include all the matched rows from the Store_profile table in the result set. Furthermore, you need to use WHERE instead of AND after the joining condition. So your query should be like this:

SELECT * , Offers.Store_id 
FROM Store_profile 
LEFT JOIN Offers 
ON Store_profile.Storename = Offers.Store_id 
WHERE Store_profile.Storetype LIKE '%Electronics%' 
LIMIT 0 , 300

Subsequently, change your while() loop and json_encode() function call in the following way,

$result = array();
while($row = mysqli_fetch_array($r)){
    if(!isset($result[$row['Storename']])){
        $result[$row['Storename']] = array('Store_Name'=>$row['Storename'], 'Store_address'=>$row['address']);
    }
    if(isset($row['offer']) && isset($row['price'])){
        $result[$row['Storename']]['offer'][] = array('Offer_Title'=>$row['offer'],'MRP_Price'=>$row['price']);
    }else{
        $result[$row['Storename']]['offer'] = array();
    }
}

echo json_encode(array('result' => array_values($result)));