sql>php>xml - I'm stuck any suggestions?

Hangin' Around Joined: Jul 06, 2005 Posts: 25

Ok im back again Smile

Im making a pod casting module for nuke. The project has been moving along pretty smooth.

Thus far I have the database completed.

The admin area for control is completed.

The user side of the module is also completed.

However, I am on the last step of the mod and I have ran in to some trouble.

What I am doing now is this:

- I have all the podcast info in the database.
- I'm pulling the data out in a php file with the xml areas in echo ""; style.

This is my file below:
xml.php

Code:




<?


// Connect Info


$db = "database-name";


$dbuser = "database-user";


$table = "table-name";


$dbpass = "password";





// Connect


$conn = mysql_connect("localhost", "$dbuser", "$dbpass") or die ('I cannot connect to the database because: ' . mysql_error());


mysql_select_db ($db, $conn) or die ("Could not select database $db");





// Get Main Info


$query = "SELECT * from nuke_pc_config";


$resultHandle = mysql_query( $query, $conn ) or die ("Could not run query");


$numRows = mysql_num_rows ($resultHandle);


while($row = mysql_fetch_row($resultHandle)) {


    $pc_title =  $row["pc_title"];


   $pc_desc =  $row["pc_desc"];


   $pc_lang =  $row["pc_lang"];


   $pc_copy =  $row["pc_copy"];


   $pc_build =  $row["pc_build"];


   $pc_pubdat =  $row["pc_pubdate"];


   $pc_sub =  $row["pc_sub"];


   $pc_sum =  $row["pc_sum"];


   $pc_authname =  $row["pc_authname"];


   $pc_authemail =  $row["pc_authemail"];


   $pc_imgurl =  $row["pc_imgurl"];


   $pc_cat =  $row["pc_cat"];


   $pc_subcat =  $row["pc_subcat"];


   


// XML Head


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


echo "<rss xmlns:itunes='http://www.itunes.com/DTDs/Podcast-1.0.dtd'>";


echo "  <channel>";


echo "    <title>$pc_title</title>";


echo "    <description>$pc_desc</description>";


echo "    <link>$pc_link</link>";


echo "    <language>$pc_lang</language>";


echo "    <copyright>$pc_copy</copyright>";


echo "    <lastBuildDate>$pc_build</lastBuildDate>";


echo "    <pubDate>$pc_pubdate</pubDate>";


echo "    <webMaster>$pc_authemail</webMaster>";


echo "    <itunes:author>$pc_authname</itunes:author>";


echo "    <itunes:subtitle>$pc_sub</itunes:subtitle>";


echo "    <itunes:summary>$pc_tsum</itunes:summary>";


echo "    <itunes:owner>";


echo "      <itunes:name>$pc_authname</itunes:name>";


echo "      <itunes:email>$pc_authemail</itunes:email>";


echo "    </itunes:owner>";


echo "    <image>";


echo "      <url>$pc_imgurl</url>";


echo "      <title>$pc_title</title>";


echo "      <link>$pc_link</link>";


echo "    </image>";


echo "    <itunes:link rel='image' type='video/jpeg' href='$pc_imgurl'>$pc_title</itunes:link>";


echo "    <itunes:category text='$pc_cat'>";


echo "      <itunes:category text='$pc_subcat' />";


echo "    </itunes:category>";


}





// Get Item Info


$query = "SELECT * from nuke_pc_item";


$resultHandle = mysql_query( $query, $conn ) or die ("Could not run query");


$numRows = mysql_num_rows ($resultHandle);


while($row = mysql_fetch_row($resultHandle)) {


   $pc_i_id = $row["pc_i_id"];


   $pc_i_title =  $row["pc_i_title"];


   $pc_i_sub =  $row["pc_i_sub"];


   $pc_i_desc = $row["pc_i_desc"];


   $pc_i_sum =  $row["pc_i_sum"];


   $pc_i_date =  $row["pc_i_date"];


   $pc_i_auth =  $row["pc_i_auth"];


   $pc_i_cat =  $row["pc_i_cat"];


   $pc_i_subcat =  $row["pc_i_subcat"];


   $pc_i_ex =  $row["pc_i_ex"];


   $pc_i_link =  $row["pc_i_link"];


   $pc_i_key =  $row["pc_i_key"];


   $pc_i_len =  $row["pc_i_len"];


   $pc_i_dur =  $row["pc_i_dur"];


   


// XML Items Layout


echo "    <item>";


echo "       <title>$pc_i_title</title>";


echo "       <link>$pc_i_link</link>";


echo "       <description>$pc_i_desc</description>";


echo "       <enclosure url='pc_i_link' length='$pc_i_len' type='audio/mpeg' />";


echo "       <category>$pc_i_cat</category>";


echo "       <pubDate>$pc_i_date</pubDate>";


echo "       <itunes:author>$pc_i_auth</itunes:author>";


echo "       <itunes:category text='$pc_i_cat'>";


echo "         <itunes:category text='$pc_i_subcat' />";


echo "       </itunes:category>";


echo "       <itunes:explicit>$pc_i_ex</itunes:explicit>";


echo "       <itunes:subtitle>$pc_i_sub</itunes:subtitle>";


echo "       <itunes:summary>$pc_i_sum</itunes:summary>";


echo "       <itunes:duration>$pc_i_dur</itunes:duration>";


echo "       <itunes:keywords>$pc_i_key</itunes:keywords>";


echo "    </item>";


}





//XML Foot


echo "  </channel>";


echo "</rss>";


echo "?>";


?>

Now when I direct my browser to my file I get the following pop up for me to download:
xml.htm

Code:




<?xml version='1.0' encoding='UTF-8'?>


<rss xmlns:itunes='http://www.itunes.com/DTDs/Podcast-1.0.dtd'>


<channel>


<title></title>


<description></description>


<link></link>


<language></language>


<copyright></copyright>


<lastBuildDate></lastBuildDate>


<pubDate></pubDate>


<webMaster></webMaster>


<itunes:author></itunes:author>


<itunes:subtitle></itunes:subtitle>


<itunes:summary></itunes:summary>


<itunes:owner>


<itunes:name></itunes:name>


<itunes:email></itunes:email>


</itunes:owner>


<image>


<url></url>


<title></title>


<link></link>


</image>


<itunes:link rel='image' type='video/jpeg' href=''></itunes:link>


<itunes:category text=''>


<itunes:category text='' />


</itunes:category>


<item>


<title></title>


<link></link>


<description></description>


<enclosure url='pc_i_link' length='' type='audio/mpeg' />


<category></category>


<pubDate></pubDate>


<itunes:author></itunes:author>


<itunes:category text=''>


<itunes:category text='' />


</itunes:category>


<itunes:explicit></itunes:explicit>


<itunes:subtitle></itunes:subtitle>


<itunes:summary></itunes:summary>


<itunes:duration></itunes:duration>


<itunes:keywords></itunes:keywords>


</item>


<item>


<title></title>


<link></link>


<description></description>


<enclosure url='pc_i_link' length='' type='audio/mpeg' />


<category></category>


<pubDate></pubDate>


<itunes:author></itunes:author>


<itunes:category text=''>


<itunes:category text='' />


</itunes:category>


<itunes:explicit></itunes:explicit>


<itunes:subtitle></itunes:subtitle>


<itunes:summary></itunes:summary>


<itunes:duration></itunes:duration>


<itunes:keywords></itunes:keywords>


</item>


</channel>


</rss>


?>

The xml layout is correct, as well as the amount of items .... however there is no data.

Any ideas?

Thanks in advance.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Try adding

Code:

header("Content-Type: text/xml");

before

Code:

// XML Head

Posted: Fri Jul 08, 2005 6:12 pm

Ok I did that and I recieved the following error:

Code:

The XML page cannot be displayed 


Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. 








--------------------------------------------------------------------------------





Invalid at the top level of the document. Error processing resource 'http://athcast.com/modules/NukePodCast/xml.php'. Line...





?>


^

Posted: Fri Jul 08, 2005 6:18 pm

Have you tried a different encoding, like ISO-8859-1 ?

Posted: Fri Jul 08, 2005 6:24 pm

Just tried that and It is giving me the very same error.

Posted: Fri Jul 08, 2005 6:30 pm

Remove the first change I suggested.

Posted: Fri Jul 08, 2005 6:36 pm

Ok I removed the

Code:

header("Content-Type: text/xml");

And I now have a blank page
see: http://athcast.com/modules/NukePodCast/xml.php

I appreciate your help on this.

registered · Regular Joined: May 28, 2005 Posts: 90

first of all the

// XML Head
echo "<?xml version='1.0' encoding='UTF-8'?>";
echo "<rss xmlns:itunes='http://www.itunes.com/DTDs/Podcast-1.0.dtd'>";
echo " <channel>";

should be off the while, you don't need to regenerate the header for all the lines. move it before while.

next, why if you're making a nuke module/addon you don't use the sql layer?
seems that $resultHandle is empty, are you sure the query is correct?

the correct syntax for xml is (example from my rss feed downloads 0.91):

Code:

<?php





   /*


   # Downloads Feed RSS v. 0.91 by Manuel                               # 


   # Copyright (c) 2005 by UltraDigital                                 #


   # http://www.ultradigital.it                                         #


   # Last update: 25/02/2005                                            #


   #                                                                   */





define('XMLFEED', 1);





include('mainfile.php');


include('includes/ipban.php');





global $prefix, $db, $nukeurl;





header("Content-Type: text/xml");





$sql = "SELECT lid, title FROM ".$prefix."_downloads_downloads ORDER BY lid DESC LIMIT 10";


$result = $db->sql_query($sql);





echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n\n";


echo "<!DOCTYPE rss PUBLIC \"-//Netscape Communications//DTD RSS 0.91//EN\"\n";


echo " \"http://my.netscape.com/publish/formats/rss-0.91.dtd\">\n\n";


echo "<rss version=\"0.91\">\n\n";


echo "<channel>\n";


echo "<title>".htmlspecialchars($sitename)."</title>\n";


echo "<link>$nukeurl</link>\n";


echo "<description>".htmlspecialchars($backend_title)."</description>\n";


echo "<language>$backend_language</language>\n\n";





while(list($lid, $title) = $db->sql_fetchrow($result)) {


    $title = stripslashes($title);


    $title2 = ereg_replace(" ", "_", $title);


    echo "<item>\n";


    echo "<title>".htmlspecialchars($title)."</title>\n";


    echo "<link>$nukeurl/modules.php?name=Downloads&amp;d_op=viewdownloaddetails&amp;lid=$lid&amp;ttitle=$title2</link>\n";


    echo "</item>\n\n";


}





echo "</channel>\n";


echo "</rss>";





?>

hope you find useful Wink

Posted: Fri Jul 08, 2005 7:02 pm

It was an issue with a few of the first xml tags.

We now have xml http://athcast.com/modules/NukePodCast/xml.php

However no data lol

I think I can work it out now using your guide as an example.

Thanks Again Raven!
((Big Hugs))

Posted: Fri Jul 08, 2005 7:14 pm

Yay!

All better!
See: http://athcast.com/xml.php

Thanks again!