如何通过PHP/JSON API将MySQL数据转为指定列的HTML表格？

Hey there! Let's walk through exactly how to convert your API's JSON response into that clean HTML table with id, name, and date columns. I'll cover two common approaches—one that fetches your existing API data to build the table, and another that adds table output directly to your existing api.php for flexibility.

Approach 1: Fetch API Data & Generate Table in a Separate PHP File #

This is great if you want to keep your API focused on returning JSON, and have a dedicated page for the table view.

Here's the full code you can save as something like product-table.php:

<?php
// Step 1: Fetch JSON data from your API
$apiUrl = 'https://api.mystem.tk/product';
$jsonResponse = file_get_contents($apiUrl);

// Handle API request failures
if ($jsonResponse === false) {
    die('Oops, failed to pull data from the API. Check your API URL or connection.');
}

// Step 2: Parse JSON into a PHP array
$products = json_decode($jsonResponse, true);

// Validate JSON parsing
if (json_last_error() !== JSON_ERROR_NONE) {
    die('Invalid JSON from API: ' . json_last_error_msg());
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Product List</title>
    <style>
        table { border-collapse: collapse; width: 80%; margin: 2rem auto; }
        th, td { border: 1px solid #ddd; padding: 0.8rem; text-align: left; }
        th { background-color: #f5f5f5; font-weight: bold; }
        tr:hover { background-color: #f9f9f9; }
    </style>
</head>
<body>
    <h2 style="text-align: center;">Product Inventory</h2>
    
    <?php if (!empty($products)): ?>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Date</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($products as $product): ?>
                    <tr>
                        <!-- Use htmlspecialchars to prevent XSS attacks -->
                        <td><?php echo htmlspecialchars($product['id'] ?? 'N/A'); ?></td>
                        <td><?php echo htmlspecialchars($product['name'] ?? 'N/A'); ?></td>
                        <td><?php echo htmlspecialchars($product['date'] ?? 'N/A'); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php else: ?>
        <p style="text-align: center; color: #666;">No products found in the database.</p>
    <?php endif; ?>
</body>
</html>

Key Notes for This Approach:

• htmlspecialchars() is critical here—it prevents cross-site scripting (XSS) attacks by escaping special characters in your product data.
• The ?? 'N/A' fallback ensures your table doesn't break if a product is missing an id, name, or date field.
• If your API requires authentication (like a bearer token), swap file_get_contents() with cURL for more control:

  $ch = curl_init($apiUrl);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
  // Uncomment and add your token if needed
  // curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer YOUR_API_TOKEN']);
  $jsonResponse = curl_exec($ch);
  curl_close($ch);
  

Approach 2: Add Table Output Directly to Your api.php #

If you want your API to return either JSON or HTML based on a query parameter (like ?format=table), modify your existing api.php like this:

<?php
$connect = mysqli_connect('#host', '#user', '#password', '#db');
if(!$connect) {
    die('Database connection failed: ' . mysqli_connect_error());
}

// Fetch your product data (adjust the query to match your table/columns)
$query = "SELECT id, name, date FROM your_product_table";
$result = mysqli_query($connect, $query);
$products = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Check if user requested table format
if (isset($_GET['format']) && $_GET['format'] === 'table') {
    // Output HTML table
    ?>
    <!DOCTYPE html>
    <html>
    <head>
        <title>Product Table</title>
        <style>
            table { border-collapse: collapse; width: 80%; margin: 2rem auto; }
            th, td { border: 1px solid #ddd; padding: 0.8rem; text-align: left; }
            th { background-color: #f5f5f5; }
        </style>
    </head>
    <body>
        <h2 style="text-align: center;">Product List</h2>
        <?php if (!empty($products)): ?>
            <table>
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>Date</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($products as $product): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($product['id']); ?></td>
                            <td><?php echo htmlspecialchars($product['name']); ?></td>
                            <td><?php echo htmlspecialchars($product['date']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php else: ?>
            <p style="text-align: center;">No products available.</p>
        <?php endif; ?>
    </body>
    </html>
    <?php
} else {
    // Default: return JSON
    header('Content-Type: application/json');
    echo json_encode($products);
}

mysqli_close($connect);
?>

Now you can:

• Visit api.mystem.tk/product to get JSON data
• Visit api.mystem.tk/product?format=table to see the HTML table

Either approach should work smoothly with your existing setup—pick the one that fits your workflow best!

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