Two queries mysql in one object json

Question

I have two tables that I want to convert them to json like this:

[
   {
      "date":"2013-07-20",
      "id":"123456",
      "year":"2013",
      "people":[
         {
            "name":"First",
            "age":"60",
            "city":"1"
         },
         {
            "name":"second",
            "age":"40",
            "city":"2"
         },
         {
            "name":"third",
            "age":"36",
            "city":"1"
         }
      ]
   }
]

but the result of my code is this:

[
   {
      "date":"2013-07-20",
      "id":"123456",
      "year":"2013",}
      ,{
      "people":[
         {
            "name":"First",
            "age":"60",
            "city":"1"
         },
         {
            "name":"second",
            "age":"40",
            "city":"2"
         },
         {
            "name":"third",
            "age":"36",
            "city":"1"
         }
      ]
   }
]

the code creates a new object to the array "people" and I want that are in the same object

$result = mysql_query("SELECT * FROM data where id='123456'");
$fetch = mysql_query("SELECT name,age,city FROM people where id='123456'"); 

$json = array();
$json2['people'] = array();

  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $json[] = $row;
  }

  while ($row = mysql_fetch_assoc($fetch)){
    $row_temp["name"]=$row["name"];
    $row_temp["age"] = $row["age"];
    $row_temp["city"] = $row["city"];

   array_push($json2['people'],$row_temp);
   }

    array_push($json, $json2);

echo Json_encode($json);

How I can make the array is in the same object as the table "data"?

Many thanks

The Alpha · Accepted Answer · 2013-07-28T23:59:49.437

I think you may try this

$result = mysql_query("SELECT * FROM data where id='123456'");
$fetch = mysql_query("SELECT name,age,city FROM people where id='123456'"); 

// I think, you'll get a single row, so no need to loop
$json = mysql_fetch_array($result, MYSQL_ASSOC);

$json2 = array();
while ($row = mysql_fetch_assoc($fetch)){
    $json2[] = array( 
        'name' => $row["name"],
        'age' => $row["age"],
        'city' => $row["city"]
    );
}
$json['people'] = $json2;
echo json_encode($json);

Result of print_r($json) should be something like this

Array
(
    [date] => 2013-07-20
    [year] => 2013
    [id] => 123456
    [people] => Array
        (
            [0] => Array
                (
                    [name] => First
                    [age] => 60
                    [city] => 1
                )

            [1] => Array
                (
                    [name] => second
                    [age] => 40
                    [city] => 2
                )

        )

)

Result of echo json_encode($json) should be

{
    "date" : "2013-07-20",
    "year":"2013",
    "id":"123456",
    "people":
    [
        {
            "name" : "First",
            "age" : "60",
            "city" : "1"
        },
        {
            "name" : "second",
            "age" : "40",
            "city" : "2"
        }
    ]
}

If you do echo json_encode(array($json)) then you will get your whole json wrapped in an array, something like this

[
    {
        "date" : "2013-07-20",
        "year":"2013",
        "id":"123456",
        "people":
        [
            {
                "name" : "First",
                "age" : "60",
                "city" : "1"
            },
            {
                "name" : "second",
                "age" : "40",
                "city" : "2"
            }
        ]
    }
]

Thanks for your answer. I tried the three examples all have responded and returned the same code. { "0":{ "date":"2013-07-20", "id":"123456", "year":"2013" }, "people":[....] } I want it to be like this [ { "date":"2013-07-20", "id":"123456", "year":"2013", "people":[......] } ] You know how to correct it? Thank you! — pablogupi, Jul 28 '13 at 21:47
You mean the result of the query in the database or json encoded? — pablogupi, Jul 28 '13 at 23:05
`SELECT * FROM data where id='123456' date year id 2013-07-20 2013 123456 SELECT name,age,city FROM people where id='123456' name age city id First 60 1 123456 second 40 2 123456 city 36 1 123456 ` Sorry for the format — pablogupi, Jul 28 '13 at 23:34

score 0 · Answer 2 · answered Jul 28 '13 at 18:52

You were very close, but you want the People array to be a direct value of the outer array and you've wrapped it in an extra array.

Also, please note that the MySQL library you are using is deprecated. That means it will be removed from PHP in a future release. You should replace calls from the MySQL_* family of functions with either mysqli or pdo

$result = mysql_query("SELECT * FROM data where id='123456'");
$fetch = mysql_query("SELECT name,age,city FROM people where id='123456'"); 

$json = array();

  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $json[] = $row;
  }

$json['people'] = array();

  while ($row = mysql_fetch_assoc($fetch)){
    $row_temp["name"]=$row["name"];
    $row_temp["age"] = $row["age"];
    $row_temp["city"] = $row["city"];

   array_push($json['people'],$row_temp);
   }

echo Json_encode($json);

score 0 · Answer 3 · answered Jul 28 '13 at 19:13

You can make it work by waiting to use the key people until the very end when you join the two arrays. Up until then, just load the data into $json and $json2.

$json = array('date' => '2013', 'id' => '123456', 'year' => '2013');

$result = mysql_query("SELECT * FROM data where id='123456'");
$fetch = mysql_query("SELECT name,age,city FROM people where id='123456'"); 

$json = array();
$json2 = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $json[] = $row;
}

while ($row = mysql_fetch_assoc($fetch)){
    $row_temp["name"]=$row["name"];
    $row_temp["age"] = $row["age"];
    $row_temp["city"] = $row["city"];
    array_push($json2, $row_temp);
}

$json['people'] = $json2;

echo Json_encode($json);

Two queries mysql in one object json

3 Answers3

Linked