PHP脚本调用SQL生成XML报500错误，请求排查问题

Let’s walk through resolving the 500 error and other issues in your script step by step:

Key Issues Identified #

• Syntax Error in SQL String: The double quotes inside your concat() functions were breaking the PHP string. I’ve replaced them with single quotes to avoid this conflict.
• Missing Semicolon: Your $sql variable definition didn’t end with a semicolon, causing a critical parse error.
• Variable Name Mismatch: You created a mysqli connection as $conn but tried to use $adb for queries—this led to an undefined variable error.
• Incorrect Array Keys: You referenced $row['fname'] and $row['lname'] which don’t exist; I’ve updated these to use the actual column aliases from your SQL query.
• Undefined Variable: Two lines were missing the $ before xml, leading to an undefined variable error.
• Invalid XML Structure: All product fields were directly under <products>—each product is now wrapped in a <product> tag to form valid XML.
• Uninitialized Variable: $xml wasn’t initialized before appending to it; now it starts with the root <products> tag.

Corrected Code #

<?php
// Replace these with your actual database credentials
$servername = "your_server_host";
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_db_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fixed SQL query with proper quoting and formatting
$sql = "SELECT 
            psc5_product.id_product AS 'product_id',
            psc5_product.ean13 AS 'ean13',
            psc5_product.price AS 'price',
            psc5_product.reference AS 'product_reference',
            psc5_stock_available.quantity AS 'available_stock',
            psc5_manufacturer.name AS 'brand',
            psc5_product_lang.description AS 'description',
            psc5_product_lang.name AS 'title',
            concat('https://www.natureldeluxe.be/', psc5_category_lang.link_rewrite, '/', psc5_product_lang.link_rewrite) AS 'deeplink',
            concat('https://www.natureldeluxe.be/', psc5_image.id_image, '-large_default/', psc5_product_lang.link_rewrite, '.jpg') AS 'imagelink'
        FROM psc5_product
        INNER JOIN psc5_stock_available ON psc5_stock_available.id_product = psc5_product.id_product
        INNER JOIN psc5_manufacturer ON psc5_manufacturer.id_manufacturer = psc5_product.id_manufacturer
        INNER JOIN psc5_product_lang ON psc5_product_lang.id_product = psc5_product.id_product
        INNER JOIN psc5_category_lang ON psc5_category_lang.id_category = psc5_product.id_category_default
        INNER JOIN psc5_image ON psc5_image.id_product = psc5_product.id_product";

// Execute query using the correct connection variable
$result = $conn->query($sql);

// Check if query succeeded
if (!$result) {
    die("Query failed: " . $conn->error);
}

// Initialize XML string properly
$xml = "<products>";

// Loop through results with valid XML structure and correct keys
while ($row = $conn->fetch_assoc($result)) {
    $xml .= "<product>";
    $xml .= "<product_id>" . htmlspecialchars($row['product_id']) . "</product_id>";
    $xml .= "<ean13>" . htmlspecialchars($row['ean13']) . "</ean13>";
    $xml .= "<price>" . htmlspecialchars($row['price']) . "</price>";
    $xml .= "<product_reference>" . htmlspecialchars($row['product_reference']) . "</product_reference>";
    $xml .= "<available_stock>" . htmlspecialchars($row['available_stock']) . "</available_stock>";
    $xml .= "<brand>" . htmlspecialchars($row['brand']) . "</brand>";
    $xml .= "<description>" . htmlspecialchars($row['description']) . "</description>";
    $xml .= "<title>" . htmlspecialchars($row['title']) . "</title>";
    $xml .= "<deeplink>" . htmlspecialchars($row['deeplink']) . "</deeplink>";
    $xml .= "<imagelink>" . htmlspecialchars($row['imagelink']) . "</imagelink>";
    $xml .= "</product>";
}

$xml .= "</products>";

// Generate and save XML file
$sxe = new SimpleXMLElement($xml);
$sxe->asXML("test.xml");

// Close database connection
$conn->close();
?>

Additional Improvements #

• Added htmlspecialchars() to escape special characters (like &, <, >) in your data, ensuring valid XML output.
• Used fetch_assoc() for cleaner access to row data by column name.
• Added error checking for the SQL query to catch any issues with the query itself.
• Closed the database connection properly to free resources.

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