product export to xml, using sql queries

问题:

I have a sql query that runs out the output I will That works perfectly.

Now I want to use the sql query in a php script to give a xml output every time I trigger the php.

This is the code I have now, but it runs out on a 500 error (I believe the error is created in line 24 - 25) 

<?php 

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$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"

$result = $adb->query($sql);
 $xml .= "<products>";
while($row=$adb->fetch_array($result))
{
    $xml .= "<product_id>".$row['fname']."</product_id>"; 
    $xml .= "<ean13>".$row['lname']."</ean13>";
    $xml .= "<price>".$row['lname']."</price>";
    $xml .= "<product_reference>".$row['lname']."</product_reference>";
    $xml .= "<available_stock>".$row['lname']."</available_stock>";
    $xml .= "<brand>".$row['lname']."</brand>";
        $xml .= "<description>".$row['lname']."</description>";
            $xml .= "<title>".$row['lname']."</title>";
            xml .= "<deeplink>".$row['lname']."</deeplink>";
            xml .= "<imagelink>".$row['lname']."</imagelink>";
            }
$xml .= "</products>";

$sxe = new SimpleXMLElement($xml);
$sxe->asXML("test.xml");
?>

回答1:

try oop xml SimpleXMLElement. The SimpleXMLElement class

Use SimpleXMLElement -> addAttribute() and SimpleXMLElement -> addChild().

Just like:

<?PHP
$sxe = simplexml_load_string(/*....xml string....*/);
$sxe -> addChild("product_id", $row['fname']);

回答2:

The problem is you have un-escaped double quotes inside your $sql string. Also you're missing the ; at the end of the assignment. Try this instead:

$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";

You also need to rewrite the processing code. You have $conn as the name of your connection in the beginning of the code, but are using $adb further down. Also the lines which output xml are all using the same .$row['lname'] variable when they should be using the appropriately named variable from the query. Try changing that code to this:

$result = $conn->query($sql);
$xml .= "<products>";
while ($row = $result->fetch_array())
{
    $xml .= "<product_id>".$row['product_id']."</product_id>"; 
    $xml .= "<ean13>".$row['ean13']."</ean13>";
    $xml .= "<price>".$row['price']."</price>";
    $xml .= "<product_reference>".$row['product_reference']."</product_reference>";
    $xml .= "<available_stock>".$row['available_stock']."</available_stock>";
    $xml .= "<brand>".$row['brand']."</brand>";
    $xml .= "<description>".$row['description']."</description>";
    $xml .= "<title>".$row['title']."</title>";
    $xml .= "<deeplink>".$row['deeplink']."</deeplink>";
    $xml .= "<imagelink>".$row['imagelink']."</imagelink>";
}
$xml .= "</products>";