Using MYSQLI in php to search data and return results as XML

Question

I'm tying to make a script which will return all the columns in my database for each row if one of the columns matches a keyword. E.g. in the example below all rows which have a match for the word tap are returned. I then want to render the results out as xml. The code below seems to work and totalResults shows the number of matches found. How do I loop over each results and render out all matched data by the SQL query?

   $query = 'tap';

    //connect to the database
$db = new mysqli('localhost', $username, $password, $database );
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

//query the database    
$sqlQuery = "select * from skus
where
`id` like '%$query%' OR
`name` like '%$query%' OR
`description` like '%$query%' OR
`ean` like '%$query%' OR
`price` like '%$query%' OR
`wasPrice` like '%$query%' OR
`deliveryCost` like '%$query%' OR
`deliveryTime` like '%$query%' OR
`stockAvailability` like '%$query%' OR
`skuAvailableInStore` like '%$query%' OR
`skuAvailableOnline` like '%$query%' OR
`channel` like '%$query%' OR
`manufacturersPartNumber` like '%$query%' OR
`specificationsModelNumber` like '%$query%' OR
`featuresBrand` like '%$query%' OR
`imageUrl` like '%$query%' OR
`thumbnailUrl` like '%$query%' OR
`features` like '%$query%' OR
`url` like '%$query%' OR
`productHierarchy` like '%$query%'";
if(!$result = $db->query($sqlQuery)){
    die('There was an error running the query [' . $db->error . ']');
}

    Header('Content-type: text/xml');

echo '<?xml version="1.0" encoding="utf-8"?>';

echo '<totalResults>Total results: ' . $result->num_rows . '</totalResults>';

//close the database connection
$db->close();

Leave meta-data out of the body of a tag. If you had 50 results, Your XML should be `50` — Burhan Khalid, Jun 30 '13 at 11:15
@Burhan Khalid: I feel okay with closing (so got my vote, good duplicate that is), however some details related to Mysqli are not covered with that answer. — hakre, Jun 30 '13 at 11:17

score 2 · Answer 1 · edited May 23 '17 at 12:00

You don't ask very specifically so I will answer this broadly.

You loop over the result of a Mysqli query (with the current non End-Of-Live versions of PHP which are 5.4 and 5.5) by iterating over the mysqli_result object which is a Traversable - so very easy to use:

foreach ($result as $row) 
{
    ...

For the XML output you should make use of the XMLWriter library because it takes care of your needs. For example with the foreach from just above:

foreach($result as $row)
{
    $writer->startElement('sku');

    foreach($row as $name => $value) {
        $writer->startElement($name);
        $writer->text($value);
        $writer->endElement();
    }

    $writer->endElement();
}

The full example at a glance (mine has a different SQL query and database but that should not cause any headaches I guess):

$mysql = new Mysqli('localhost', 'testuser', 'test', 'test');
if ($mysql->connect_errno) {
    throw new Exception(sprintf("Mysqli: (%d): %s", $mysql->connect_errno, $mysql->connect_error));
}

$sqlQuery = 'SELECT * FROM config';
if (!$result = $mysql->query($sqlQuery)) {
    throw new Exception(sprintf('Mysqli: (%d): %s', $mysql->errno, $mysql->error));
}

header('Content-type: text/xml');
$writer = new XMLWriter();

$writer->openUri('php://output');
$writer->startDocument();
$writer->startElement('results');
$writer->startElement('skus');

foreach($result as $row)
{
    $writer->startElement('sku');

    foreach($row as $name => $value) {
        $writer->startElement($name);
        $writer->text($value);
        $writer->endElement();
    }

    $writer->endElement();
}

$writer->endDocument();

Hope this helps. If you don't have PHP 5.4 yet, get it. If that's an issue, you can turn the Mysqli result also into an iterator for other PHP versions as outlined in my answer to "PHP mysqli_result: Use Traversable interface with fetch_object". Let me know if that causes you any problems.

score 1 · Answer 2 · answered Jun 30 '13 at 08:41

I had to use mysqli_fetch_assoc

Finished code:

//connect to the database
$db = new mysqli('localhost', $username, $password, $database );
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

//query the database    
$sqlQuery = "select * from skus
where
`id` like '%$query%' OR
`name` like '%$query%' OR
`description` like '%$query%' OR
`ean` like '%$query%' OR
`price` like '%$query%' OR
`wasPrice` like '%$query%' OR
`deliveryCost` like '%$query%' OR
`deliveryTime` like '%$query%' OR
`stockAvailability` like '%$query%' OR
`skuAvailableInStore` like '%$query%' OR
`skuAvailableOnline` like '%$query%' OR
`channel` like '%$query%' OR
`manufacturersPartNumber` like '%$query%' OR
`specificationsModelNumber` like '%$query%' OR
`featuresBrand` like '%$query%' OR
`imageUrl` like '%$query%' OR
`thumbnailUrl` like '%$query%' OR
`features` like '%$query%' OR
`url` like '%$query%' OR
`productHierarchy` like '%$query%'";

//run query
if ($result = mysqli_query($db, $sqlQuery)) {

    Header('Content-type: text/xml');

    echo '<?xml version="1.0" encoding="utf-8"?>';

    echo '<results>';

    echo '<skus>';

    //fetch associative array 
    while ($row = mysqli_fetch_assoc($result)) {
        echo '<sku>';

            echo '<id>' . htmlspecialchars($row["id"]) . '</id>';
            echo '<ean>' . htmlspecialchars($row["ean"]) . '</ean>';
            echo '<name>' . htmlspecialchars($row["name"]) . '</name>';
            echo '<description>' . htmlspecialchars($row["description"]) . '</description>';
            echo '<features>' . htmlspecialchars($row["features"]) . '</features>';
            echo '<productHierarchy>' . htmlspecialchars($row["productHierarchy"]) . '</productHierarchy>';
            echo '<url>' . htmlspecialchars($row["url"]) . '</url>';
            echo '<price>' . htmlspecialchars($row["price"]) . '</price>';
            echo '<wasPrice>' . htmlspecialchars($row["wasPrice"]) . '</wasPrice>';
            echo '<deliveryCost>' . htmlspecialchars($row["deliveryCost"]) . '</deliveryCost>';
            echo '<deliveryTime>' . htmlspecialchars($row["deliveryTime"]) . '</deliveryTime>';
            echo '<stockAvailability>' . htmlspecialchars($row["stockAvailability"]) . '</stockAvailability>';
            echo '<skuAvailableInStore>' . htmlspecialchars($row["skuAvailableInStore"]) . '</skuAvailableInStore>';
            echo '<skuAvailableOnline>' . htmlspecialchars($row["skuAvailableOnline"]) . '</skuAvailableOnline>';
            echo '<channel>' . htmlspecialchars($row["channel"]) . '</channel>';
            echo '<manufacturersPartNumber>' . htmlspecialchars($row["manufacturersPartNumber"]) . '</manufacturersPartNumber>';
            echo '<specificationsModelNumber>' . htmlspecialchars($row["specificationsModelNumber"]) . '</specificationsModelNumber>';
            echo '<featuresBrand>' . htmlspecialchars($row["featuresBrand"]) . '</featuresBrand>';
            echo '<imageUrl>' . htmlspecialchars($row["imageUrl"]) . '</imageUrl>';
            echo '<thumbnailUrl>' . htmlspecialchars($row["thumbnailUrl"]) . '</thumbnailUrl>';


        echo '</sku>';

    }

    echo '</skus>';

}

//close the database connection
$db->close();

echo '</results>';

Yes, as every tutorial should have showed you (or just take foreach if your PHP version is not end-of.life [PHP 5.4+]). Using an XML library that create the XML for you as well as iterating over each result row allows you even to reduce this code to just 4 lines or so. — hakre, Jun 30 '13 at 11:03
Added you that as an answer so it's better visible what I meant: http://stackoverflow.com/a/17389749/367456 — hakre, Jun 30 '13 at 11:10

score -1 · Answer 3 · answered Jun 29 '13 at 23:29

$result return an array of each column found from your query.

So, you just have to loop the array like so :

//loop through all columns of the result

foreach($result as $key => $value)

{

      // Output : { key : "name", value : "tap" } if "name" === "tap"

      echo "{ key : " . $key . ", result : " + $value + "}";

}

You just have to insert into xml how u wish to

Using MYSQLI in php to search data and return results as XML

3 Answers3