CODEWITHSUNDEEP
mysqljson
Liam Nugent
Liam Nugent

Reputation: 43

How to combine multiple mysql rows into a JSON array

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!

Upvotes: 0

Views: 235

Answers (1)

IVO GELOV
IVO GELOV

Reputation: 14289

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);

Upvotes: 1

Related Questions