To convert mysql data to json in required format using php

Question

I have a table with values

| CategoryName             | ClientName | Phone Number |
|--------------------------|------------|--------------|
| Mobile repair            | XYZ        | 90000000     |
| Mobile repair            | ABC        | 91111111     |
| Car service              | AZC        | 89999999     |
| TV repair                |  MNB       | 88888888     |
| Car service              | LLL        | 99999999     |

I want JSON in format

{ 
"Mobile Repair" : {"XYZ":"90000000","ABC":"91111111"},
"Car Service" :{ "AZC" : "89999999","LLL":"99999999"},
"TV Repair" :{"MNB","88888888"}
}

But I am getting in the format

{ 
"Mobile Repair" : {"ABC":"91111111"},
"Car Service" :{ "LLL":"99999999"},
"TV Repair" :{"MNB","88888888"}
}

My sql query is

$query = "select S.SpecificCategoryName,A.ClientName,A.PhoneNumber from specificcategories S,clientstable A where A.SpecificCategoryId=S.SpecificCategoryId and A.LocationCode=(Select LocationCode from areas where LocationName='".$location."')";

And I am formatting the values as

for($row = 0; $row < count($result); $row++)
{
    $values[$result[$row]['SpecificCategoryName']] = array($result[$row['ClientName']=>$result[$row]['PhoneNumber']);
}

Kindly help in encoding the JSON in the above format.

Syscall · Accepted Answer · 2018-02-14 07:26:31Z

2

You have to append your values into the array for each columns as keys :

for($row = 0; $row < count($result); $row++)
{
    $cat = $result[$row]['SpecificCategoryName'];
    $name = $result[$row]['ClientName'] ;
    $phone = $result[$row]['PhoneNumber'] ;

    $values[$cat][$name] = $phone;
}

Or with foreach:

foreach ($results as $row)
{
    $cat = $row['SpecificCategoryName'];
    $name = $row['ClientName'] ;
    $phone = $row['PhoneNumber'] ;

    $values[$cat][$name] = $phone;
}

answered Feb 14, 2018 at 7:26

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

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

Comments

4EACH · Accepted Answer · 2018-02-14 07:31:16Z

0

Just add [] brackets and your data wont overwritten.

    for($row = 0; $row < count($result); $row++)
    {
        $category = $result[$row]['SpecificCategoryName'];
    $name = $result[$row]['ClientName'] ;
    $phone = $result[$row]['PhoneNumber'] ;

    $values[$category][$name] = $phone;
    }
echo json_encode($values);

edited Feb 14, 2018 at 7:31

answered Feb 14, 2018 at 7:22

4EACH

2,1974 gold badges20 silver badges31 bronze badges

2 Comments

samz22 Over a year ago

Thanks for the above,now data is not getting overwritten but i m getting in the format { "Mobile Repair" :[ {"XYZ":"90000000","ABC":"91111111"}], "Car Service" :[{ "AZC" : "89999999","LLL":"99999999"}], "TV Repair" :[{"MNB","88888888"}] } whereas I want in { "Mobile Repair" : {"XYZ":"90000000","ABC":"91111111"}, "Car Service" :{ "AZC" : "89999999","LLL":"99999999"}, "TV Repair" :{"MNB","88888888"} }

4EACH Over a year ago

Ok, so now it will what you expect for

Meow Kim · Accepted Answer · 2018-02-14 07:34:44Z

0

for($row=0; $row<count($result); $row++) {
    $values[$result[$row]['SpecificCategoryName']][$result[$row]['ClientName']] = $result[$row]['PhoneNumber'];
}

answered Feb 14, 2018 at 7:34

Meow Kim

4845 silver badges14 bronze badges

1 Comment

rollstuhlfahrer Over a year ago

Please add some context to the post

Collectives™ on Stack Overflow

To convert mysql data to json in required format using php

3 Answers 3

Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Related