如何将MySQL多表/全表转换为JSON？附代码求指导

Hey there! Let's break down how to expand your current PHP code to handle multiple tables or even all tables in your MySQL database and convert them to JSON. Your existing single-table code is a solid starting point—we'll build on that with some straightforward extensions.

1. Convert Specific Multiple Tables #

If you know exactly which tables you want to include (like users and orders for example), you can create an array of table names, loop through each one, and collect their data into a structured JSON object where each key is the table name.

Here's the updated code with error handling (always good practice!):

<body>
<?php
// Database connection
$connect = mysqli_connect("localhost", "root", "", "martlink_db");

// Check connection
if (!$connect) {
    die("Connection failed: " . mysqli_connect_error());
}

// List of tables you want to convert
$targetTables = ['users', 'orders', 'products']; // Add your table names here
$allTablesData = [];

foreach ($targetTables as $table) {
    // Query the table (backticks prevent issues with special table names)
    $sql = "SELECT * FROM `$table`";
    $result = mysqli_query($connect, $sql);

    // Check for query errors
    if (!$result) {
        echo "Error fetching table $table: " . mysqli_error($connect) . "<br>";
        continue;
    }

    // Fetch rows into an array
    $tableData = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $tableData[] = $row;
    }

    // Add to the main data array with table name as key
    $allTablesData[$table] = $tableData;
}

// Close connection to free resources
mysqli_close($connect);

// Output formatted, human-readable JSON
echo '<pre>';
print_r(json_encode($allTablesData, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
echo '</pre>';
?>
</body>

2. Convert All Tables in the Database #

If you want to automatically pull every table in your martlink_db database, you can first fetch all table names using a SHOW TABLES query, then loop through them just like the example above.

Here's how to do that:

<body>
<?php
// Database connection
$connect = mysqli_connect("localhost", "root", "", "martlink_db");

// Check connection
if (!$connect) {
    die("Connection failed: " . mysqli_connect_error());
}

// Fetch all table names in the database
$tableQuery = "SHOW TABLES";
$tableResult = mysqli_query($connect, $tableQuery);

if (!$tableResult) {
    die("Error fetching tables: " . mysqli_error($connect));
}

// Collect table names into an array
$allTables = [];
while ($tableRow = mysqli_fetch_row($tableResult)) {
    $allTables[] = $tableRow[0];
}

// Now fetch data for each table
$allTablesData = [];
foreach ($allTables as $table) {
    $sql = "SELECT * FROM `$table`";
    $result = mysqli_query($connect, $sql);

    if (!$result) {
        echo "Error fetching table $table: " . mysqli_error($connect) . "<br>";
        continue;
    }

    $tableData = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $tableData[] = $row;
    }

    $allTablesData[$table] = $tableData;
}

// Close connection
mysqli_close($connect);

// Output formatted JSON
echo '<pre>';
print_r(json_encode($allTablesData, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
echo '</pre>';
?>
</body>

Key Notes & Tips #

• Error Handling: I added checks for connection and query errors to make debugging easier—this is missing in your original code and will save you headaches later.
• JSON Formatting: Using JSON_PRETTY_PRINT makes the output human-readable, and JSON_UNESCAPED_UNICODE ensures non-ASCII characters (like accents or special symbols) display correctly.
• Performance: If your database has large tables, fetching all rows at once might use a lot of memory. For big datasets, consider paginating results or limiting the rows you fetch.
• Table Name Safety: Wrapping table names in backticks ($table) prevents issues if your table names include special characters or MySQL reserved words.

内容的提问来源于stack exchange，提问作者Fazil Nusrat