求助：通过Woody Snippet将MySQL库存汇总表展示到WordPress前端

Let's break down the issues in your PHP code and fix them to get your inventory summary working correctly in WordPress via Woody Snippet:

Key Issues in Your Original Code #

• Missing connection parameter in mysqli_query: The mysqli_query() function requires the database connection object as its first argument, but you omitted it entirely.
• Unquoted field names with spaces: Columns like Product Name contain spaces, so they need to be wrapped in backticks (`) in your SQL query to avoid syntax errors.
• Typos and missing alias for SUM result: You tried to access SUM(Quality) (note the typo: Quality instead of Quantity), and without an alias for the summed value, referencing it reliably is tricky.
• WordPress best practice violation: Directly using mysqli_connect in WordPress isn't ideal—WordPress provides a built-in $wpdb class that handles database connections safely and uses the site's existing DB credentials (no need to hardcode them).

Corrected Code (Using WordPress $wpdb Recommended) #

This is the better approach for WordPress, as it leverages the platform's native database tools and follows security best practices:

<?php
global $wpdb;

// Define your table name (add WP prefix if your table uses it)
$table_name = 'Inventory'; // Adjust to $wpdb->prefix . 'Inventory' if your table has a WP prefix

// Run the query with proper backticks and alias for the summed quantity
$results = $wpdb->get_results(
    "SELECT `Product Name`, `SKU`, SUM(`Quantity`) AS `Total Quantity` 
     FROM $table_name 
     GROUP BY `Product Name`, `SKU`",
    ARRAY_A
);
?>
<table align="center" border="1px" style="width:100%; line-height:40px;">
    <tr>
        <th>Product Name</th>
        <th>SKU</th>
        <th>Total Quantity</th>
    </tr>
    <?php if (!empty($results)) : ?>
        <?php foreach ($results as $row) : ?>
            <tr>
                <td><?php echo esc_html($row['Product Name']); ?></td>
                <td><?php echo esc_html($row['SKU']); ?></td>
                <td><?php echo esc_html($row['Total Quantity']); ?></td>
            </tr>
        <?php endforeach; ?>
    <?php else : ?>
        <tr>
            <td colspan="3" align="center">No inventory data found</td>
        </tr>
    <?php endif; ?>
</table>

If You Prefer Using Native mysqli (Not Recommended for WordPress) #

If you still want to stick with your original approach, here's the fixed version of your code:

<?php
$hostname = "localhost";
$username = "invdb";
$password = "invpw";
$database = "tbbcom_inv";
$con = mysqli_connect($hostname, $username, $password, $database);

if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

// Fixed query: added backticks for spaced fields, alias for SUM, and passed the connection object
$result = mysqli_query($con, "SELECT `Product Name`, `SKU`, SUM(`Quantity`) AS `Total Quantity` FROM `Inventory` GROUP BY `Product Name`, `SKU`");
?>
<table align="center" border="1px" style="width:100%; line-height:40px;">
    <tr>
        <th> Product Name </th>
        <th> SKU </th>
        <th> Quantity </th>
    </tr>
    <?php if (mysqli_num_rows($result) > 0) : ?>
        <?php while($rows = mysqli_fetch_assoc($result)) : ?>
        <tr>
            <td><?php echo htmlspecialchars($rows['Product Name']); ?></td>
            <td><?php echo htmlspecialchars($rows['SKU']); ?></td>
            <td><?php echo htmlspecialchars($rows['Total Quantity']); ?></td>
        </tr>
        <?php endwhile; ?>
    <?php else : ?>
        <tr>
            <td colspan="3" align="center">No inventory data found</td>
        </tr>
    <?php endif; ?>
    <?php mysqli_close($con); ?>
</table>

Additional Notes #

• Always escape output (using esc_html() in WordPress or htmlspecialchars() in native PHP) to prevent XSS vulnerabilities.
• For WordPress, using $wpdb ensures you're using the same database credentials as your site, eliminating the need to hardcode sensitive info (a major security risk).
• Double-check that your Inventory table is in the same database as your WordPress installation if using the native mysqli approach.

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