PHP XML Output from mySQL Database

Question

I am pulling information from an SQL database and forming it as XML via PHP. I am attempting to make my XML format like this:

<channel>
    <channel_name>ASN</channel_name>
    <program id="1">
        <title>The Simpson</title>
        <info>Melfi considers turning to Homer for help...</info>
        <season>S01</season>
        <episode>E09</episode>
        ...
        <channel>ASN</channel>
    </program>
</channel>
<channel>
    <channel_name>NBC</channel_name>
    <program id="2">
        <title>The Simpson</title>
        <info>Melfi considers turning to Homer for help...</info>
        <season>S02</season>
        <episode>E04</episode>
        ...
        <channel>NBC</channel>
    </program>
</channel>
...etc

This is my PHP that is attempting to mimic the XML structure above:

<?php
header('Content-Type: application/xml; charset=utf-8');
    include_once "db_connect.php";
    //SQL Query
    $program_sql = <<<SQL
    SELECT * FROM Programs
SQL;
    if ($result = $db->query($program_sql)) {
        /* fetch associative array */
        while ($row = mysqli_fetch_assoc($result)) {
            //Data
            #Descriptions
            $program_title = $row['program_title'];
            $program_id = $row['program_id'];
            $program_info = $row['program_info'];
            #Guides
            $program_season = $row['program_season'];
            $program_episode = $row['program_episode'];
            $program_genre = $row['program_genre'];
            #Times
            $program_duration_hours = $row['program_duration_hours'];
            $program_duration_mins = $row['program_duration_mins'];
            $program_day = $row['program_day'];
            $program_time_start = $row['program_time_start'];
            $program_time_end = $row['program_time_end'];
            #Assets
            $program_image_fullsize = $row['program_image_fullsize'];
            $program_image_thumbnail = $row['program_image_thumbnail'];
            #Channel
            $channel .= $row['channel'];
            //XML Output
            $output .= "<program id='".$program_id."'>
        <title>".$program_title."</title>
        <info>".$program_info."</info>
        <season>S".$program_season."</season>
        <episode>E".$program_episode."</episode>
        <duration>
            <hours>".$program_duration_hours."</hours>
            <minutes>".$program_duration_mins."</minutes>
        </duration>
        <day>".$program_day."</day>
        <time>
            <start>".$program_time_start."</start>
            <end>".$program_time_end."</end>
        </time>
        <genre>".$program_genre."</genre>
        <image>
            <fullsize>/images/program_images/".$program_image_fullsize."</fullsize>
            <thumbnail>/images/program_images/thumbnails/".$program_image_thumbnail."</thumbnail>
        </image>
        <channel>".$channel."</channel>
    </program>";
        }//Channel While Loop
    }//if

    echo $output;
    mysqli_close($db);
?>

My PHP is currently only outputting the program node; How would I go about wrapping these in their corresponding channel tags?

well we don't know what these tables look inside, but just try to do same logic as you have already done. fetch the values then make another one for the channel. your already done the program right? — Kevin
– Kevin, Commented Nov 12, 2014 at 0:33

alu · Accepted Answer · 2014-11-12 01:08:55Z

2

Try this.

$rows = [
    ['channel_name' => 'ASN', 'program_id' => 1, 'program_title' => 'Program 1'],
    ['channel_name' => 'ASN', 'program_id' => 2, 'program_title' => 'Program 2'],
    ['channel_name' => 'NBC', 'program_id' => 3, 'program_title' => 'Program 3'],
];

// pre grouping pattern
$grouped = array_reduce($rows, function($channels, $row) {
    if (!array_key_exists($row['channel_name'], $channels)) {
        $channels[$row['channel_name']] = [];
    }
    $channels[$row['channel_name']][] = $row;

    return $channels;
}, []);

foreach ($grouped as $channelName => $program) {
    // build up XML
}

// Or SimpleXML pattern
$root = new SimpleXMLElement('<root />');

foreach ($rows as $row) {
    $channel = $root->xpath("/root/channel/channel_name[text()='{$row['channel_name']}']/parent::node()");
    if (count($channel) === 0) {
        $channel = $root->addChild('channel');
        $channel->addChild('channel_name', $row['channel_name']);
    } else {
        $channel = $channel[0];
    }

    $program = $channel->addChild('program');
    $program->addAttribute('id', $row['program_id']);
    $program->addChild('title', $row['program_title']);
}

echo $root->asXML();

answered Nov 12, 2014 at 1:08

alu

4562 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1826917 Over a year ago

This is fantastic, thank you so much— Just one quick query, How would I go about populating that $rows (array right?) from a database? Apologies for the pestering, I am a little green with PHP. Thanks again!

alu Over a year ago

The $rows is example. You can use while loop on the your original code (foreach to while).

Collectives™ on Stack Overflow

PHP XML Output from mySQL Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related