How to combine multiple mysql rows into a JSON array

Question

I am having a slight problem, currently, I have a table with the following structure.

CREATE TABLE `product_data` (
`uid` INT NOT NULL COMMENT 'User Identifier',
`id` INT NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
`sex` enum('m','f','o') NOT NULL COMMENT 'Gender',
`fid` INT NOT NULL COMMENT 'Father Identifier',
`mid` INT NOT NULL COMMENT 'Mother Identifier',
`firstname` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'First Name',
`lastname` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Last Name',
`description` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Description',
`image` VARCHAR(2083) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Image URL',
PRIMARY KEY (`id`));

And the table when connected to my PHP page is outputting the following :

[{"uid":"0","id":"1","sex":"m","fid":null,"mid":null,"firstname":"Liam","lastname":"Nugent","description":"Weird Dog","image":null}]

Now the part I am getting stuck at is that I have no clue how to restructure my data to the following.

[{"uid":"0","id":"1","sex":"m","parents": [1, 2],"firstname":"Liam","lastname":"Nugent","description":"Weird Dog","image":null}]

Essentially I would like to combine mid and fid into an array and give it a new name of parents but I have no clue where to start. I would like this so I can use the BasicPrimitives library. Could you please give me an idea of the terminology or what I should look into to learn how to restructure JSON data?

Thank you all so much!

You can fetch the rows one by one in associative array - and then simply unset the fid and mid keys while also setting the parents key to the new array with [fid, mid] — IVO GELOV
– IVO GELOV, Commented Aug 26, 2021 at 11:34
This is currently how I am fetching my data from MySQL table : include_once "connection.php"; $queryall = mysqli_query($conn, "SELECT * FROM product_data"); if ($conn) { echo "connection success<br>"; }else{ echo "connection unsuccess<br>"; } $rows = array(); while($r = mysqli_fetch_assoc($queryall)) { $rows[] = $r; } $mysqloutput = json_encode($rows); Is it possible to link me to a resource where I could understand the setting and unsettling of keys, please? — Liam Nugent
– Liam Nugent, Commented Aug 26, 2021 at 11:41

IVO GELOV · Accepted Answer · 2021-08-27 12:10:20Z

1

include_once "connection.php"; 
$queryall = mysqli_query($conn, "SELECT * FROM product_data"); 
if ($conn) { echo "connection success<br>"; }
else{ echo "connection unsuccess<br>"; } 
$rows = array(); 
while($r = mysqli_fetch_assoc($queryall)) 
{ 
  $r['parents'] = Array($r['fid'], $r['mid']);
  unset($r['fid']);
  unset($r['mid']);
  $rows[] = $r; 
} 
$mysqloutput = json_encode($rows);

answered Aug 27, 2021 at 12:10

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

How to combine multiple mysql rows into a JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related