How to create XML using PHP and MYSQL with conditional nesting

Question

I am attempting to make a TV Guide by using a database to create the XML file via MYSQL and PHP. I have had good success so far and have managed to output the various channels for each day.

The problem is when I try to bring the various programmes in. Within the database, each programme is assigned a day and channel it will be shown on. However, the XML being generated is placing every programme on every channel for every day.

Here is what I have so far:

$xml = new DOMDocument("1.0", "UTF-8");
$xml->formatOutput = TRUE;

$day_query = "SELECT day_name FROM day;";

$dq_result = mysql_query($day_query) or die(mysql_error());

$tvguide = $xml->createElement("guide");
$tvguide = $xml->appendChild($tvguide);

    if($dq_result)
    while($dayrow = mysql_fetch_row($dq_result)){
        $day = $xml->createElement("day");
        $day->setAttribute("name", $dayrow[0]);
        $day = $tvguide->appendChild($day);

        $channel_query = "SELECT channel_ID, channel_Name FROM channel ORDER BY channel_Name ASC;";

        $cq_result = mysql_query($channel_query) or die(mysql_error());

        if($cq_result)
            while($channelrow = mysql_fetch_row($cq_result)){

            $channel = $xml->createElement("channel");
            $channel->setAttribute("id", $channelrow[0]);
            $channel = $day->appendChild($channel);

            $channel_name = $xml->createElement("channel_name");
            $channelNameText = $xml->createTextnode($channelrow[1]);
            $channel_name->appendChild($channelNameText);
            $channel_name = $channel->appendChild($channel_name);


            $show_query = "SELECT programme_ID, programme_Name, start_Time, duration, description, day_Name, channel_Name FROM programme ORDER BY start_Time ASC;";

            $sq_result = mysql_query($show_query) or die(mysql_error());

            if($sq_result)
                while($showrow = mysql_fetch_row($sq_result)){


            $show = $xml->createElement("show");
            $show = $channel->appendChild($show);

            $show_name = $xml->createElement('show_name');
            $show_name->setAttribute("id", $showrow[0]);
            $showNameText = $xml->createTextnode($showrow[1]);
            $show_name->appendChild($showNameText);
            $show->appendChild($show_name);



            }

        }
}

Basically, I want to know how I can nest all the shows whose day_Name is equal to Monday, for example, within the 'Monday' XML tag. Thank you.

delCano · Accepted Answer · 2014-12-02 23:00:10Z

1

OK, to answer your question quickly, it be as easy as changing:

$show_query = "SELECT programme_ID, programme_Name, start_Time, duration, description, day_Name, channel_Name FROM programme ORDER BY start_Time ASC;";

Into

$show_query = "SELECT programme_ID, programme_Name, start_Time, duration, description, day_Name, channel_Name FROM programme WHERE day_Name='$dayrow[0]' AND channel_Name='$channelNameText' ORDER BY start_Time ASC;";

However, there are a couple more things to change:

You shouldn't use the mysql_ interface, use either mysqli or PDO_mysql.
You are doing inside the while a query (SELECT channel_ID, channel_Name FROM channel ORDER BY channel_Name ASC;) which is constant. You probably should make it only once, before the while, and cache its results.

answered Dec 2, 2014 at 23:00

delCano

3852 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

How to create XML using PHP and MYSQL with conditional nesting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related