CODEWITHSUNDEEP
phpmysqljson
KookyLibra
KookyLibra

Reputation: 7

How to create JSON API service from MySQL database in PHP

I have following MySQL database table

id | majorFoodName | cropName | foodType  | foodName  | quantity | form 
1  | Recipe1       | Rice     | Breakfast | Foodname1 | 500+60   | 2000 
4  | Recipe2       | Rice     | Breakfast | Foodname2 | 500      | 1000
6  | Recipe1       | Wheat    | Breakfast | Foodname2 | 518      | 1000

I have written following PHP code to give JSON API output

$sql = "SELECT * FROM food WHERE cropName = 'Rice' AND foodName = 
'Foodname1' ";
$result = mysqli_query($connect, $sql);
$num_rows = mysqli_num_rows($result);
if ($num_rows > 0) {
    $jsonData = array();
    while ($array = mysqli_fetch_row($result)) {
    $jsonData[] = $array;
  }
}
class Emp {
  public $majorFoods = "";    
   }
   $e = new Emp();
   $e->majorFoods = $jsonData;
   header('Content-type: application/json');
   echo json_encode($e);

I am getting following JSON output

{
    "majorFoods": [
        [
        "1",
        "Recipe1",
        "Rice",
        "Breakfast",
        "Foodname1",
        "500+60",
        "2000"
    ]
  ]
}

I need to give following API JSON format for all cropName and all foodName

{
  "Rice": [
               {
                 "foodName1": {
                        "majorFoodName": "Receipe1",
                        "quantity": "500+60",
                        "form": "2000" }
                 "foodName2": {
                        "majorFoodName": "Receipe2",
                        "quantity": "500",
                        "form": "1000" } 
                 ]
  "Wheat": [
               {
                 "foodName2": {
                        "majorFoodName": "Receipe1",
                        "quantity": "518",
                        "form": "1000" }
                 ]
}

Kindly help in improving the PHP code to get desired API JSON response.

Upvotes: 0

Views: 2550

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57131

When your building up your array of data, use the crop type as the main index and create sub arrays of the extra data you need to store.

 while ($array = mysqli_fetch_assoc($result)) {
    $cropData = array ( $array['foodName'] => 
                array( 'majorFoodName' => $array['majorFoodName'],
                       'quantity' => $array['quantity'],
                       'form' => $array['form'] ));
    $jsonData[$array['cropName']][] = $cropData;
 }

Note that I use mysqli_fetch_assoc so that I can refer to the result fields with the column names.

The line

$jsonData[$array['cropName']][] = $cropData;

Accumulates all of the data for a particular crop name together, adding the new data to the end of the array (using []).

Upvotes: 1

Related Questions