如何从MySQL数据库实时/动态生成JSON

Question

So I want to export a JSON file from a MySQL database table, a php script that runs weekly and exports JSON file from a specific table.

This is sort of the thing I want to achieve:

  <?php

$json_file_name = "File_export.json";
$json_file_name = str_replace(" ", "_", $json_file_name);

$con = mysqli_connect("", "", "", "");

if (mysqli_connect_errno($con)) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$date_range = array(
    "start" => date("Y-m-d H:i:s", strtotime("-7 days")),
    "end" => date("Y-m-d H:i:s", strtotime("now")),
);

and so on

if(!empty($json_data) && count($json_data) > 1)
{
$json_file_data = "";
$fp = fopen($json_file_name, 'w');
foreach($json_data as $row)
{
    $json_file_data .= implode(",", $row) . "
";
}
fwrite($fp, $json_file_data);
fclose($fp);

What is the best way to achieve the same.

Thank you :)

Answer 1

If your database table not too large, you can fetch all rows into a single array, then turn that array into JSON automatically without looping. This will generate JSON with column values as a list:

// $con is connection, $json_filename is name of filename to write
$query = "select * from MyTable";

// Fetch all rows into $json_data array
$result    = mysqli_query($con, $query);
$json_data = mysqli_fetch_all($result);
mysqli_close($con);

// Turn data into JSON and write to file
$json = json_encode($json_data);
file_put_contents($json_filename, $json);

Example output:

[["name1","address1"],["name2","address2"]]

If your database table is a little bigger, it is better to write each line as it is generated. The code below will create a JSON object for each row.

$query  = "select * from MyTable";
$result = mysqli_query($con, $query);

// Open output file
$fp = fopen($json_file_name, 'w');

// Write JSON list start    
fwrite($fp, '[');

// Write each object as a row
$isFirstRow = true;
while ($row = mysqli_fetch_assoc($result)) {
    if (!$isFirstRow) {
        fwrite($fp, ',');
    } else {
        $isFirstRow = false;
    }
    fwrite($fp, json_encode($row));
}

// Write JSON list end
fwrite($fp, ']');

// Close file and MySQL connection
fclose($fp);
mysqli_close($con);

Example output:

[{"name": "name1", "address": "address1"},{"name": "name2", "address": "address2"}]