Let me explain what i want. I have a two table name : grocery_product and grocery_attribute: grocery_product fields are
id
pname
categoryid
subcatid
grocery_attribute fields are
id
product_id
price
discount
PHP CODE : This is my code for API
case "product_lists":
$query = "select grocery_product.id as pid,grocery_product.pname,grocery_product.categoryid,grocery_product.subcatid,grocery_attribute.id as atribid, grocery_attribute.product_id,grocery_attribute.price,grocery_attribute.discount,from grocery_product
right join grocery_attribute on grocery_attribute.product_id = grocery_product.id order by pid asc" ;
$result = $link->query($query) or die('Errant query: '.$query);
$FetchArray = array();
$num_rows = $result->num_rows;
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$newData['id'] = $row['pid']; // PID as product ID
$newData['pname'] = $row['pname'];
$newData['categoryid'] = $row['categoryid'];
$newData['subcatid'] = $row['subcatid'];
$newData['attrib_id'] = $row['atribid'];
$newData['product_id'] = $row['product_id'];
$newData['price'] = $row['price'];
$newData['discount'] = $row['discount'];
array_push($FetchArray,$newData);
}
$grocerryArray=array();
foreach($FetchArray as $detail){
$grocerryArray[$detail['id']]['id']=$detail['id'];
$grocerryArray[$detail['id']]['pname']=$detail['pname'];
$grocerryArray[$detail['id']]['categoryid']=$detail['categoryid'];
$grocerryArray[$detail['id']]['subcatid']=$detail['subcatid'];
// Get all grocerry_attribute data where grocery_attribute.product_id = grocery_product.id
$grocerryArray[$detail['id']]['details'][]=array(
'attrib_id'=>$detail['attrib_id'], // attrib_id is grocery_attribute_id
'product_id'=>$detail['product_id'],
'price'=>$detail['price'],
'discount'=>$detail['discount']
);
}
header('Content-type: application/json');
echo json_encode(array('Status'=>'Success', 'data'=>$grocerryArray, 'message'=>'You have successfully retrieved data'));break;
Current Output is
{
"Status":"Success",
"data":{
"1":
{
"id":"1",
"pname":"Royal Moong Dal Moong Dal",
"categoryid":"4",
"subcatid":"3",
"details":[
{"attrib_id":"1","product_id":"1","price":"500","discount":"15"},
{"attrib_id":"2","product_id":"1","price":"800","discount":"10"},
{"attrib_id":"3","product_id":"1","price":"1100","discount":"20"}
]
},
"2":
{
"id":"2",
"pname":"Royal Toor Dal/Arhar Dal / Tuver Dal",
"categoryid":"4",
"subcatid":"3",
"details":[
{"attrib_id":"4","product_id":"2","price":"500","discount":"10"},
{"attrib_id":"5","product_id":"2","price":"50","discount":"10"}
]
}
},
"message":"You have successfully retrieved data"
}
What I am looking for
[
{
"status":"success",
"data":[
{
"id":"1",
"pname":"Royal Moong Dal Moong Dal",
"categoryid":"4",
"subcatid":"3",
"details":[
{"attrib_id":"1","product_id":"1","price":"500","discount":"15"},
{"attrib_id":"2","product_id":"1","price":"800","discount":"10"},
{"attrib_id":"3","product_id":"1","price":"1100","discount":"20"}
]
}
{
"id":"2",
"pname":"Royal Toor Dal\/Arhar Dal\/Tuver Dal",
"categoryid":"4",
"subcatid":"3",
"details":[
{"attrib_id":"4","product_id":"2","price":"500","discount":"10"},
{"attrib_id":"5","product_id":"2","price":"50","discount":"10"}
]
}
],
"message":"You have successfully retrieved data"
}
]