php - How to select data from multiple tables and display them in a json format? -

i have many of same tables. need data each of tables , bring them out in json format:

[   "table1": {      ["first colomn value", "second colomn value", ...],      ["first colomn value", "second colomn value", ...],      ["first colomn value", "second colomn value", ...],      ...   },   "table2": {      //values table2   },   ... ] 

is possible make 1 request mysql? how request , php code?

to in 1 request need stored procedure, iterate through result set , iterate through result. portability better off not using stored procedures, not portable.

heres code modified http://php.net/manual/en/pdostatement.nextrowset.php using pdo

<?php  // dont forget instantiate $conn $sql = 'call multiple_rowsets()'; $stmt = $conn->query($sql); $jsonarr = array(); {     $rowset = $stmt->fetchall(pdo::fetch_num);     if ($rowset) {         $jsonarr[] = tojson($rowset, $i);     } } while ($stmt->nextrowset());  $jsondata = "{"; $i = 0;  foreaeach ($jsonarr $json) {    if($i !== 0) $json .=',';    else $i = 1;    $jsondata .= $json; }      $jsondata .= "}";  echo $jsondata; // output  function tojson(&$rowset) {     $json = '{';     $p = 0;     foreach ($rowset $row) {         if($p !== 0) $json .=',';             else $p = 1;         $json .= '[';         $i = 0;         foreach ($row $col ) {             if($i !== 0) $json .=',';             else $i = 1;             $json .= $col         }         $json = "]";     }     $json.="}";     return $json; } ?> 

this untested should give idea of how it

now stored procedure should like

delimiter // create procedure multiple_rowsets()    begin    select *  table1;    select *  table2;    select *  table3;    end // delimiter ; 

but if ok going database more once, highly recommend running each select php, convert each result json, pop each result in array, iterate through create 1 big json