how to generate JSON array for entire database tables from MySQL database using PHP

Question

I want this kind of JSON array, database1 all tables

{
    {"table1":"[{"key11":"val11"},{"key12":"val12"},{"key13":"val13"}]"},
    {"table2":"[{"key21":"val21"},{"key22":"val22"},{"key23":"val23"}]"},
    {"table3":"[{"key31":"val31"},{"key32":"val32"},{"key33":"val33"}]"},
    {"table4":"[{"key41":"val41"},{"key42":"val42"},{"key43":"val43"}]"},
    {"table5":"[{"key51":"val51"},{"key52":"val52"},{"key53":"val53"}]"},
}

This bellow code is convert a single database table to JSON array, But i want entire database tables to JSON array

    $return_arr = array();
    $fetch = mysql_query("SELECT * FROM table1");   
    while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
        $row_array['val2'] = $row['val1'];
        $row_array['val2'] = $row['val2'];
        $row_array['val3'] = $row['val3'];      
        array_push($return_arr,$row_array);
    }   
    echo json_encode($return_arr);

anyone help me please.

Answer 1

Iterate over all tables, you can use that query to get all tables.

select * from information_schema.tables

You still need to select all results inside the tables.

Answer 2

something like this:

$db='recipes';
$tables=$db->query('SHOW TABLES IN '.$db, O_ARRAY);
foreach($tables as $n=>$v){
    $table=$v['Tables_in_'.$db];
    $array=$db->query("SELECT * FROM $table");
    $data[$table]=$array;
}
echo '<pre>';
echo json_encode($data);

that's going to be pretty memory intensive on large data! You might add some filters for unneeded fields, or database-specific fields that need to be skipped or the value translated to another place.

Answer 3

This is the answer for above question change database name only all tables and table records are given in JSON array format.

    mysql_connect('localhost','root','');
    mysql_select_db('database');
    $db='database'; 

    $return_arr = array();
    $return_arr1 = array();
    $return_arr2 = array(); 
    $return_arr3 = array();
    $result = mysql_query("SHOW TABLES IN ".$db); 
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        array_push($return_arr,$row);
    }
    foreach($return_arr as $key => $value)
    {
        $table=$value['Tables_in_'.$db];
        $array1=mysql_query("SELECT * FROM $table");        
        while ($row1 = mysql_fetch_array($array1, MYSQL_ASSOC)) 
        {
            array_push($return_arr3,$row1);
        }       
            $return_arr1[$table]=$return_arr3;
            unset($return_arr3);
            $return_arr3 =array();
    }
    echo '<pre>';
    print_r($return_arr1);