0

I'm facing a big problem in php while building a json object array. I'm trying to fetch records from mysql and then put them in the below format but it doesn't work.

This is how i want : 

{ "records":[ {"id": 1,"Name":"Alfreds Futterkiste","City":"Berlin","Country":"Germany"}, {"id": 2,"Name":"Ana Trujillo Emparedados y helados","City":"México D.F.","Country":"Mexico"}, {"id": 3,"Name":"Antonio Moreno Taquería","City":"México D.F.","Country":"Mexico"}, {"id": 4,"Name":"Around the Horn","City":"London","Country":"UK"}, {"Name":"B's Beverages","City":"London","Country":"UK"}, {"Name":"Berglunds snabbköp","City":"Luleå","Country":"Sweden"}, {"Name":"Blauer See Delikatessen","City":"Mannheim","Country":"Germany"}, {"Name":"Blondel père et fils","City":"Strasbourg","Country":"France"}, {"Name":"Bólido Comidas preparadas","City":"Madrid","Country":"Spain"}, {"Name":"Bon app'","City":"Marseille","Country":"France"}, {"Name":"Bottom-Dollar Marketse","City":"Tsawassen","Country":"Canada"}, {"Name":"Cactus Comidas para llevar","City":"Buenos Aires","Country":"Argentina"}, {"Name":"Centro comercial Moctezuma","City":"México D.F.","Country":"Mexico"}, {"Name":"Chop-suey Chinese","City":"Bern","Country":"Switzerland"}, {"Name":"Comércio Mineiro","City":"São Paulo","Country":"Brazil"} ] }

what i get

["records",{"name":"abc","email":"test2@test.com"},{"name":"def","email":"test2@gmail.com"},{"name":"ghi","email":"test3@hotmail.com"}]

notice the colon (:) after the "records".

My PHP CODE :

$rows[] = "records";


$a = mysql_query("", $connect);
while ($b = (mysql_fetch_array($a)) ){
extract($b);
$rows[] = array('name' => "$accountnumber", 'email' => "$email"); 
}

echo json_encode($rows);
sid
  • 365
  • 2
  • 11
  • Why dont you use json_encode() function as long as you can retrieve data as array from database? – Abdur Rahman Jan 05 '19 at 13:41
  • @NigelRen please check now..i've added my code – sid Jan 05 '19 at 13:42
  • use the flag `JSON_FORCE_OBJECT` in json_encode – Dharman Jan 05 '19 at 13:43
  • Your expected data and the data you get is different – Dharman Jan 05 '19 at 13:44
  • Because you are adding to an array not an object's 'record' property, ie `$data->record[]=...` where $data is an object like `new stdClass` – Patrick Evans Jan 05 '19 at 13:47
  • 1
    As a general note - you should start to move away from the old `mysql_` api - https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Nigel Ren Jan 05 '19 at 13:49
  • `mysql_*` "This means that as of 31 Dec 2018 it will not exist in any supported version of PHP. " Which PHP version are you still using? You should't start to move away, this should be your main concern. – Dharman Jan 05 '19 at 14:08

1 Answers1

3

You are adding the records level in the wrong place, you are just adding it as an element to the array. This adds each row as a child of this element ($rows["records"][])...

$rows= [];

$a = mysql_query("", $connect);
while ($b = (mysql_fetch_array($a)) ){
    $rows["records"][] = array('name' => $b["accountnumber"], 'email' => $b["email"]);
}

echo json_encode($rows);

Also - avoid using extract() if possible and add the values from the array you fetch instead.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • You are a genius!! Thanks Nigel Ren, your answer helped me to build a JSON response exactly how i wanted it. – sid Jan 06 '19 at 05:47