How to properly loop and create json using database records in php

Question

Am Trying to create a JSON array by leveraging info three database tables.

After leveraging info and solution found here source
I came to realize that i have to use foreign keys for references and also use join queries.

The three tables is created as follows each with their reference keys

Create table categorys(id int primary key auto_increment, category_name varchar(30), buyer_userid int(30));

Create table product(id int primary key auto_increment, product_name varchar(30), buyer_userid int(30),category_id int,
foreign key (category_id) references categorys(id));


Create table sales(items_id int primary key auto_increment, item_name varchar(30),quantity varchar(30), buyer_userid int(30),
product_id int, foreign key (product_id) references product(id));

Here is my expected JSON Format...

[
{"id":"1","cat_name":"Provision","catbuy_userid":"100",
"products_info":[{"productid":"2","product_name":"Malt","buyer_userid":"100",
"sales_Info":[{"items_id":"1","item_name":"malt","buyer_userid":"100","quantity":"72"}]
}]},
{"id":"2","cat_name":"Cosmetics","catbuy_userid":"200",
"products_info":[{"productid":"3","product_name":"soapy","buyer_userid":"200",
"sales_Info":[{"items_id":"2","item_name":"cream","buyer_userid":"200","quantity":"83"}]
}]}
]

Here is the my coding so far

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "mydb"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

    $res_arr = array();
$query = '
    SELECT 
c.id,
c.category_name,
c.buyer_userid,
p.id,
p.product_name,
p.buyer_userid,
p.category_id,
s.items_id,
s.item_name,
s.quantity
    FROM `categorys` c
        INNER JOIN `product` p ON c.id=p.category_id
        INNER JOIN `sales` s ON p.id=s.product_id
    ORDER BY c.id';

    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result)){    
       
        $res_arr[] = $row;
}
    echo json_encode($res_arr);
    exit;

?>

Here is my json output

[{"0":"1","id":"1","1":"Provisions","category_name":"Provisions","2":"100","buyer_userid":"100","3":"4","4":"Malt","product_name":"Malt","5":"100","6":"1","category_id":"1","7":"4","items_id":"4","8":"malt","item_name":"malt","9":"93","quantity":"93"},
{"0":"2","id":"2","1":"Cosmetics","category_name":"Cosmetics","2":"200","buyer_userid":"200","3":"3","4":"soapy","product_name":"soapy","5":"200","6":"2","category_id":"2","7":"3","items_id":"3","8":"cream","item_name":"cream","9":"83","quantity":"83"}
]

Please how do I loop to get result as per json format above.

Updated Section

Error warning Sample They are so many error warnings when i run the code. below is just few of them

Warning: Use of undefined constant products_info - assumed 'products_info' (this will throw an Error in a future version of PHP) in C:\xampp\htdocs\api\test.php on line 51

Warning: Use of undefined constant product_name - assumed 'product_name' (this will throw an Error in a future version of PHP) in C:\xampp\htdocs\api\test.php on line 51

Sir Martin Left Join is given the same json output. I just want the result to be in the expected format above. between thanks — Nancy Moore
– Nancy Moore, Commented Mar 30, 2019 at 21:37
my answer does not match the output exactly... but the whole nesting is not required. it would only be required, eg. if there was more than 1 item per category (which won't ever happen, with a single query). — Martin Zeitler
– Martin Zeitler, Commented Mar 30, 2019 at 22:33

SachinPatil4991 · Accepted Answer · 2019-03-31 09:28:16Z

0

I have midified your php code so you can get your desired output.

Instead of assignind rows returned from mysql query create assosiative array as per your requirement and then convert to json


<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "mydb"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

    $res_arr = array();
$query = '
    SELECT 
c.id,
c.category_name,
c.buyer_userid,
p.id,
p.product_name,
p.buyer_userid,
p.category_id,
s.items_id,
s.item_name,
s.quantity
    FROM `categorys` c
        INNER JOIN `product` p ON c.id=p.category_id
        INNER JOIN `sales` s ON p.id=s.product_id
    ORDER BY c.id';

    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result)){    

        $res['id'] = $row[0];
        $res['cat_name'] = $row[1];
        $res['catbuy_userid'] = $row[2];
        $res['products_info']['id'] = $row[3];

        $res['products_info']['productid'] = $row[4];
        $res['products_info']['product_name'] = $row[5];
        $res['products_info']['buyer_userid'] = $row[6];

        $res['products_info']['sales_info']['items_id'] = $row[7];
        $res['products_info']['sales_info']['items_name'] = $row[8];
        $res['products_info']['sales_info']['buyer_userid'] = $row[6];
        $res['products_info']['sales_info']['quantity'] = $row[9];

        $res_arr[] = $res;
  }

}
  echo json_encode($res_arr);

?>

edited Mar 31, 2019 at 9:28

answered Mar 30, 2019 at 22:09

SachinPatil4991

7746 silver badges13 bronze badges

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

2 Comments

Nancy Moore Over a year ago

Thanks @Sachin, Your solution is working though it does not actually gives the desire output properly. Am concerned about the error it shows. Please can you see updated section of my post for those error warnings. Thanks

SachinPatil4991 Over a year ago

Enclose all keys in single quote or double quote. I forgot to enclose them

Nats · Accepted Answer · 2019-03-30 21:38:29Z

0

When you say get results do you mean you want to access those values? If so, assign your json data to a variable say data.

Then: $json = json_decode($data);

foreach($json[0] as $values1) {
    var_dump($values1);
}

foreach($json[1] as $values2) {
    var_dump($values2);
}

To get product name for example from the first object you would then do $values1->product_name. And so on for the other properties.

edited Mar 30, 2019 at 21:38

answered Mar 30, 2019 at 21:29

Nats

1705 silver badges20 bronze badges

1 Comment

Nats Over a year ago

Edited to show PHP version. You would need to decode it first then loop

Martin Zeitler · Accepted Answer · 2019-03-30 22:45:59Z

0

This be accomplished by casting from (array) $row to (object) $row:

$res_arr = array();
while($row = $result->fetch_assoc()){    
    array_push($res_arr, (object) $row);
}
header("Content-type: application/json; charset=utf-8");
die(json_encode($res_arr));

I'd use fetch_assoc() to prevent these duplicate numbered fields, which just bloat the payload... the whole nesting is basically useless and it only produces rubbish code quality, while there nowhere is more than a single nested item (and this is subsequently more complicated than necessary to access on the client-side). Nesting would make sense, when eg. first fetching all categories and then nesting all the products for that category, before proceeding with the next one category.

edited Mar 30, 2019 at 22:45

answered Mar 30, 2019 at 21:44

Martin Zeitler

79.6k20 gold badges169 silver badges243 bronze badges

Collectives™ on Stack Overflow

How to properly loop and create json using database records in php

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related