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-28 23:59:49Z

3

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"
            }
        ]
    }
]

edited Jul 28, 2013 at 23:59

answered Jul 28, 2013 at 19:11

The Alpha

147k30 gold badges294 silver badges313 bronze badges

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

5 Comments

pablogupi Over a year ago

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!

The Alpha Over a year ago

Maybe, you should post your query results then.

pablogupi Over a year ago

You mean the result of the query in the database or json encoded?

The Alpha Over a year ago

Results of both querys, not json.

pablogupi Over a year ago

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

Nathaniel Granor · Accepted Answer · 2013-07-28 18:52:18Z

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

Expedito · Accepted Answer · 2013-07-28 19:13:16Z

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

Collectives™ on Stack Overflow

Two queries mysql in one object json

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related