php parse mysql data into multi dimensional json array

Question

I am new to php webservices. I wish to get sql data in following format:

SQL query that am using is:

    $result = mysql_query("select r.id,r.Name, o.`Title`, o.id, o.`Description`, o.`OfferLocation`
from `restaurants` r left join `offers` o on r.id = o.`restaurant_id`");

With this query I get redundant restaurant names for each unique offer which is fine. But how do I format this data as given in the image as a multidimensional array?

Following is the code I have tried so far:

if(mysql_num_rows($result) > 0){
        $response["restaurant"] = array();$cnt=0;
        $item_rest=array();
            while($row=mysql_fetch_array($result)){
                $item_rest["RestaurantID"]=$row["id"];
            }
            $response_offer["offers"]=array();
            while($row=$result->fetch_array($result)){

                $item = array();

                $item["Offer"]= $row["Title"];
                $item["OfferId"]= $row["id"];
                $item["Description"]= $row["Description"];
                $item["OfferLocation"]= $row["OfferLocation"];

                array_push($response_offer["offers"],$item);
            }
            array_push($response["restaurant"],$response_offer);
            $response["success"]=1;
        }

How do I do this?

Answer 1

I think you are trying to use two APIs at the same time, try changing $result->fetch_array($result) (mysqli) into mysql_fetch_array($result) (mysql).

Consider to stop using mysql_ functions as they're deprecated, more here