Jump to content

Generating XML using PHP


mark107

Recommended Posts

Hi all,
 
I need some of your help, I'm working on my PHP script to create the XML document with encoding utf8 so I can generate the XML file to allow me to save the XML file in my web host.
 
 
I want to make the xml output to something like this:
 
<?xml version="1.0" encoding="UTF-8" ?>
<tv generator-info-name="www.mysite.com/xmltv">
<channel id="">
   <display-name>Information from database</display-name>
   <programme channel="Information from database" start="" stop="">
       <title lang="en"></title>
       <sub-title lang="en">
       </sub-title>
       <desc lang="en"></desc>
       <category lang="en"></category>
   </programme>
</channel>
 
 
 
Here's what my XML output looks like:
 
<?xml version="1.0" encoding="UTF-8"?>
<tv generator-info-name="www.mysite.com/xmltv"><channel><display-name>Information from database</display-name><programme/><desc/></channel></tv>
 
 
Here's the current code:
 
<?php

function db_connect()
{
define('DB_HOST', 'localhost');
define('DB_USER', 'myusername');
define('DB_PASSWORD', 'mypassword');
define('DB_DATABASE', 'mydbname');

$errmsg_arr = array();
$errflag = false;
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link)
{
die('Failed to connect to server: ' . mysql_error());
}

$db = mysql_select_db(DB_DATABASE);
if(!$db)
{
die("Unable to select database");
}
}
db_connect();

function clean($var)
{
return mysql_real_escape_string(strip_tags($var));
}
$channels = clean($_GET['channels']);
$id = clean($_GET['id']);

if($errflag)
{
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
echo implode('<br />',$errmsg_arr);
}
else
{
$insert = array();

if(isset($_GET['channels']))
{
$insert[] = 'channels = \'' . clean($_GET['channels']) .'\'';
}
if(isset($_GET['id']))
{
$insert[] = 'id = \'' . clean($_GET['id']) . '\'';
}


if($channels && $id)
{
$qrytable1="SELECT id, channels, links FROM tvguide WHERE channels='$channels' && id='$id'";
$result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());
echo '<?xml version="1.0" encoding="UTF-8" ?>
<tv generator-info-name="www.mysite.com/xmltv">
<channel id="">
<display-name></display-name>
<programme channel="" start="" stop="">
<title lang="en"></title>
<sub-title lang="en"></sub-title>
<desc lang="en"></desc>
<category lang="en"></category>
</programme>
</channel>
</tv>';
    
while ($row = mysql_fetch_array($result1))
{

}
mysql_close();
}
else if(!$channels && ! $id)
{
$qrytable1="SELECT id, channels, links, streams FROM tvguide";
$result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());

    
while ($row = mysql_fetch_array($result1))
{

}
mysql_close();
}
}
// create a dom document with encoding utf8
$domtree = new DOMDocument('1.0', 'UTF-8');

// create a root element of the xml tree
$tv = $domtree->createElement('tv');

//create attributes for element
$generator_info_name = $domtree->createAttribute('generator-info-name');
$generator_info_name->value = 'www.mysite.com/xmltv';
//append attribute
$tv->appendChild($generator_info_name);
// append element to the doc
$tv = $domtree->appendChild($tv);

//add a channel as a child of the root
$channel = $domtree->createElement('channel');
$channel_id = $domtree->createAttribute('id');
$channel_id->value = '""';
$channel = $tv->appendChild($channel);

//append children to channel
$channel->appendChild($domtree->createElement('display-name','Information from database'));
$channel->appendChild($domtree->createElement("programme"));
$channel->appendChild($domtree->createElement('desc'));

//finally, save the file
echo $domtree->saveXML();
$domtree->save('myChannel.xml');
?>

 

 
Do you know how I can make the same XML output as the first code?
 
And how I can output for each data from mysql database to put it in each channel tag and I want to add the tags under the channel tag including the display-name, programme-channel, title, sub-title, desc and category tags when I output for each data from mysql?
 
Any advise would be much appreciated.
 
Thanks in advance
Edited by mark107
Link to comment
Share on other sites

your xml definition doesn't make complete sense (there's no program id for example) and your query is not retrieving the data that the xml needs.

 

each row in your tvguide table should correspond to one program. the id should be the program's id and there should be a channel id or channel number and a start, stop, title, sub-title, description, and category(id) value.

 

the channel id and the channel display-name should be stored in a separate table and join'ed to the tvguide table in the query to get the channel display name. the same would be true for the category id and category name.

 

your query should get the data you want in the order that you want it, with the field values that you need to build the xml. the logic forming the sql query and running it should all come first. the code producing the xml should be general purpose (it doesn't care how many channels or how many programs there are under each channel.) unless you are editing existing xml, there's little need for manipulating the xml nodes/attributes... directly, just output the xml tags at the appropriate place when you are iterating over the data.

 

the following example shows how, after you have retrieved the data from the query (in an array named $data), that you can produce the xml output -

<?php
// fake some example data. the actual data would be retrieved from a database query
$data[] = array('channel_id'=>10,'display_name'=>'channel 10 name','program_id'=>123,'start'=>'s1','stop'=>'e1','title'=>'program title',
    'sub_title'=>'sub title','description'=>'program description1','category'=>'some category');
$data[] = array('channel_id'=>10,'display_name'=>'channel 10 name', 'program_id'=>456,'start'=>'s2','stop'=>'e2','title'=>'program title',
    'sub_title'=>'sub title','description'=>'program description2','category'=>'some category');
$data[] = array('channel_id'=>15,'display_name'=>'channel 15 name', 'program_id'=>789,'start'=>'s1','stop'=>'e1','title'=>'program title',
    'sub_title'=>'sub title','description'=>'program description3','category'=>'some category');


// build the xml    
$xml = '<?xml version="1.0" encoding="UTF-8" ?>
<tv generator-info-name="www.mysite.com/xmltv">';
$last_channel = null; // used to detect when the channel changes
foreach($data as $arr){
    if($last_channel != $arr['channel_id']){
        // the channel changed
        if($last_channel != null){
            // not the first channel, close out the previous channel
            $xml .= '</channel>';
        }
        // start a new channel
        $xml .= "<channel id=\"{$arr['channel_id']}\">";
        $xml .= "<display-name>{$arr['display_name']}</display-name>";
        $last_channel = $arr['channel_id'];
    }
    // output the program info under each channel
    $xml .= "<programme channel=\"{$arr['channel_id']}\" start=\"{$arr['start']}\" stop=\"{$arr['stop']}\">";
    // i don't see a program id in this definition, but it likely needs one
    $xml .= "<title lang=\"en\">{$arr['title']}</title>";
    $xml .= "<sub-title lang=\"en\">{$arr['sub_title']}</sub-title>";
    $xml .= "<desc lang=\"en\">{$arr['description']}</desc>";
    $xml .= "<category lang=\"en\">{$arr['category']}</category>";
    $xml .= "</programme>";
}
if($last_channel != null){
    // close out the previous channel if any
    $xml .= '</channel>';
}
$xml .= '</tv>';

// output the xml to the browser in this example, if you want to store it to a file, just write $xml to a file here...
header("Content-Type: text/xml");
echo $xml;
Link to comment
Share on other sites

@mac_gyver: My xml definition does make complete sense, you did not read my first post carefully what I want to achieve.

 

The source you post it doesn't match in my needs. I want to get the arrays of channel from mysql database and I want to output them in each xml tag called `channel` then save the xml file called myChannel.xml.

 

I want to make xml source that looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<tv generator-info-name="www.mysite.com/xmltv">
<channel id="row1">
   <display-name>row1</display-name>
   <programme channel="row1" start="" stop="">
       <title lang="en"></title>
       <sub-title lang="en">
       </sub-title>
       <desc lang="en"></desc>
       <category lang="en"></category>
   </programme>
</channel>

<channel id="row2">
   <display-name>row2</display-name>
   <programme channel="row2" start="" stop="">
       <title lang="en"></title>
       <sub-title lang="en">
       </sub-title>
       <desc lang="en"></desc>
       <category lang="en"></category>
   </programme>
</channel>

<channel id="row3">
   <display-name>row3</display-name>
   <programme channel="row3" start="" stop="">
       <title lang="en"></title>
       <sub-title lang="en">
       </sub-title>
       <desc lang="en"></desc>
       <category lang="en"></category>
   </programme>

</channel>

</tv>

 

 

Here's what my xml output looks like:

<?xml version="1.0" encoding="UTF-8"?>
<tv generator-info-name="www.mysite.com/xmltv"><channel><display-name>Information from database</display-name><programme/><desc/></channel></tv>

As you can see the difference between my xml source and the other xml source i want to make it, on my xml source it doesn't look the same as the other xml source.

 

Can you modify the source I use to allow me to output the list of arrays of rows from mysql to put the rows in the xml tag `channel`, `display-name` and `programme channel`?

Link to comment
Share on other sites

between the two php help sites that you have posted this on (that i know of, there could be more) you have gotten three replies that show essentially the same method of producing the xml. in each case, someone defined some input data, because your query and code doesn't show anything or how it relates to the xml (your code doesn't even attempt to do anything in the while(){} loop with the data) then they produced the xml from the point of having the data that the xml implies it would need.

 

if you take the example code i posted in reply #2, and change the input data to the following, it produces EXACTLY the xml that you stated you want in reply #3 -

$data[] = array('channel_id'=>'row1','display_name'=>'row1','program_id'=>'','start'=>'','stop'=>'','title'=>'',
    'sub_title'=>'','description'=>'','category'=>'');
$data[] = array('channel_id'=>'row2','display_name'=>'row2','program_id'=>'','start'=>'','stop'=>'','title'=>'',
    'sub_title'=>'','description'=>'','category'=>'');
$data[] = array('channel_id'=>'row3','display_name'=>'row3','program_id'=>'','start'=>'','stop'=>'','title'=>'',
    'sub_title'=>'','description'=>'','category'=>'');

your task is to write the sql query that retrieves that data, based on the columns you have in your database table, and makes it available to that code.

 

no one in the help forums you have been posting is going to fix your code and post a working copy/paste solution for you to use, for two reasons - 1) the code you posted doesn't show any attempt and it doesn't show the needed information or how it relates to the xml fields, and 2) if you want someone to fix your code, hire someone and pay them to do this for you. programming help forums are not about writing or finding code for you to copy/paste.

Edited by mac_gyver
Link to comment
Share on other sites

@mac_gyver: Thanks for your help. There are two things I need to make some changes. I want to know how do you break for each xml tag for </channel> to allow to make it separate with <channel id=""> tag which I want to make it looks like this:

 

 
<tv generator-info-name="www.mysite.com/xmltv">

 

<channel id="ABC FAMILY">
<display-name>ABC FAMILY</display-name>
<programme channel="ABC FAMILY" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
 
<channel id="CBS">
<display-name>CBS</display-name>
<programme channel="CBS" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
 
<channel id="">
<display-name>CBS</display-name>
<programme channel="CBS" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
 
<channel id="CNN USA">
<display-name>CNN USA</display-name>
<programme channel="CNN USA" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>

....and so on

 

 

 

 

 

Here's what is my PHP output:

 

 
<tv generator-info-name="www.mysite.com/xmltv">
<channel id="ABC FAMILY">
<display-name>ABC FAMILY</display-name>
<programme channel="ABC FAMILY" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
<channel id="CBS">
<display-name>CBS</display-name>
<programme channel="CBS" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
<channel id="CNN USA">
<display-name>CNN USA</display-name>
<programme channel="CNN USA" start="s1" stop="e1">
<title lang="en">program title</title>
<sub-title lang="en">sub title</sub-title>
<desc lang="en">program description1</desc>
<category lang="en">some category</category>
</programme>
</channel>
 

 

 

 

Here's the code:

 

<?php


function db_connect()
{
  define('DB_HOST', 'localhost');
  define('DB_USER', 'myusername');
  define('DB_PASSWORD', 'mypassword');
  define('DB_DATABASE', 'mydbname');


  $errmsg_arr = array();
  $errflag = false;
  $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);


  if(!$link) 
  {
    die('Failed to connect to server: ' . mysql_error());
  }


  $db = mysql_select_db(DB_DATABASE);
  if(!$db) 
  {
    die("Unable to select database");
  }
}
db_connect();


  function clean($var)
  {
    return mysql_real_escape_string(strip_tags($var));
  } 
  $channels = clean($_GET['channels']);
  $id = clean($_GET['id']);


  if($errflag) 
  {
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    echo implode('<br />',$errmsg_arr);
  }
  else 
  {
    $insert = array();


    if(isset($_GET['channels'])) 
    {
      $insert[] = 'channels = \'' . clean($_GET['channels']) .'\'';
    }
    if(isset($_GET['id'])) 
    {
      $insert[] = 'id = \'' . clean($_GET['id']) . '\'';
    }


    if(!$channels && ! $id) 
    {
      $qrytable1="SELECT id, channels, links, streams FROM tvguide";
      $result1=mysql_query($qrytable1) or die('Error:<br />' . $qry . '<br />' . mysql_error());
      
      
      while ($row = mysql_fetch_array($result1))
      {
        // fake some example data. the actual data would be retrieved from a database query
        $data[] = array('channel_id'=>$row['channels'],
        'display_name'=>$row['channels'],
        'program_id'=>123,'start'=>'s1','stop'=>'e1',
        'title'=>'program title',
         'sub_title'=>'sub title',
         'description'=>'program description1',
         'category'=>'some category');






        // build the xml    
        $xml = '<?xml version="1.0" encoding="UTF-8" ?>
<tv generator-info-name="www.mysite.com/xmltv">';
        $last_channel = null; // used to detect when the channel changes
        foreach($data as $arr)
        {
            if($last_channel != $arr['channel_id'])
            {
                // the channel changed
                if($last_channel != null){
                    // not the first channel, close out the previous channel
                    $xml .= '
</channel>
';
                }
                // start a new channel
                $xml .= "
<channel id=\"{$arr['channel_id']}\">";
                $xml .= "
   <display-name>{$arr['display_name']}</display-name>";
                $last_channel = $arr['channel_id'];
            }
            // output the program info under each channel
            $xml .= "
   <programme channel=\"{$arr['channel_id']}\" start=\"{$arr['start']}\" stop=\"{$arr['stop']}\">";
            // i don't see a program id in this definition, but it likely needs one
            $xml .= "
       <title lang=\"en\">{$arr['title']}</title>";
            $xml .= "
       <sub-title lang=\"en\">{$arr['sub_title']}</sub-title>";
            $xml .= "
       <desc lang=\"en\">{$arr['description']}</desc>";
            $xml .= "
       <category lang=\"en\">{$arr['category']}</category>";
            $xml .= "
   </programme>";
        }
        if($last_channel != null)
        {
            // close out the previous channel if any
            $xml .= '
</channel>';
        }
     }


   }
   $xml .= '
</tv>';
   // output the xml to the browser in this example, write $xml to a file here...
   header("Content-Type: text/xml");
   echo $xml;
 }
?>

 

 

Can you please tell me how do you break for each xml tag for </channel> to allow to make it to get separate with <channel id=""> tag?

 

 

And how I can save the xml file in my webhost?

Edited by mark107
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.