homepage Welcome to WebmasterWorld Guest from 54.205.247.203
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
xml from php query
j milo taylor




msg:3653512
 10:57 am on May 19, 2008 (gmt 0)

I need to output the contents of a mysql database as xml from a response to a php query. i'm almost there, but this code doesn't quite work, i think there is some contradiction with the mysql_fetch_assoc and mysql_num_rows instances:

any help would be greatfully received:

<?php require_once('Connections/immapp.php'); ?>

<?php

mysql_select_db($database_immapp, $immapp);
$recordID = $_GET['recordID'];
$query_rs_country = "SELECT * FROM artist WHERE location_id_artist = $recordID ORDER BY 'year_artist'";
$rs_country = mysql_query($query_rs_country, $immapp) or die(mysql_error());
$row_rs_country = mysql_fetch_assoc($rs_country); //--- problem here?
$totalRows_rs_country = mysql_num_rows($rs_country); //-- here?

$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<artists>\n";

for($x = 0 ; $x < mysql_num_rows($rs_country) ; $x++){ //--re here?
$row = mysql_fetch_assoc($rs_country); //--- and here?
$xml_output .= "\t<artist>\n";
$xml_output .= "\t\t<Artist_Image>" . $row['imageTB_artist'] . "</Artist_Image>\n";
$xml_output .= "\t\t<Artist_First_Name>" . $row['1stname_artist'] . "</Artist_First_Name>\n";
$xml_output .= "\t\t<Artist_Second_Name>" . $row['2ndname_artist'] . "</Artist_Second>\n";
$xml_output .= "\t\t<Artist_Year>" . $row['year_artist'] . "</Artist_Year>\n";
$xml_output .= "\t\t<Artist_Country>" . $row['country_artist'] . "</Artist_Country>\n";

$xml_output .= "\t</artist>\n <br />";
}

$xml_output .= "</artists>";

$filename = 'XML-ARTIST_COUNTRY.xml';
if (is_writable($filename)) {
if (!$handle = fopen($filename, 'w')) {
echo "Cannot open ($filename)";
exit;
}
if (fwrite($handle, $xml_output) === FALSE) {
echo "Cannot write to ($filename)";
exit;
}
echo "Success, wrote <br> ($xml_output) to ($filename)";
fclose($handle);
} else {
echo "The file $filename is not writable";
}
?>

----
thanks
milo

 

dreamcatcher




msg:3653533
 11:32 am on May 19, 2008 (gmt 0)

Hi,

When you say it doesn`t quite work, what do you mean? Error messages? More information would be helpful.

dc

j milo taylor




msg:3653553
 11:59 am on May 19, 2008 (gmt 0)

Sorry for not being clearer.

I'm not getting any errors . The problem is

1)The XML output contains the correct number of records, but the last record returned is empty

e.g. ireland has two artists. This is the xml output:

Success, wrote <br> (<?xml version="1.0"?>
<artists>

<artist>
<Artist_Image>/images/artists/tbs/Finer.jpg</Artist_Image>
<Artist_First_Name>Jem</Artist_First_Name>
<Artist_Second_Name>Finer</Artist_Second>
<Artist_Year>1955</Artist_Year>
<Artist_Country>Ireland</Artist_Country>

</artist>
<br /><artist>
<Artist_Image></Artist_Image>
<Artist_First_Name></Artist_First_Name>
<Artist_Second_Name></Artist_Second>
<Artist_Year></Artist_Year>
<Artist_Country></Artist_Country>
</artist>

<br /></artists>) to (XML-ARTIST_COUNTRY.xml)

2) Meanwhile, the PHP content displayed in the browser, is that content that is missing from the XML

i.e

<td >
<a href="ARTISTS-details.php?recordID=131">
<img src="/images/artists/tbs/Cunningham.jpg"/></a>
</td>
<td>David Cunningham</td>
<td >Born:1954 </td>

I hope this is clearer

Regards
Milo

Steerpike




msg:3653568
 12:10 pm on May 19, 2008 (gmt 0)


for($x = 0 ; $x < mysql_num_rows($rs_country) ; $x++){

Should be:

for($x = 0 ; $x <= mysql_num_rows($rs_country) ; $x++){

as you're currently stopping the loop before it actually outputs the final element of the array.

j milo taylor




msg:3653579
 12:28 pm on May 19, 2008 (gmt 0)

hi steerpike,

thanks for the comment. i have made the change you suggested, but it has not solved the problem.

now (using the example of ireland discussed earlier) i get 3 three XML records returned (there should only be two), and two of these XML returns are empty:

.i.e.

<artists>

<artist>
<Artist_Image>/images/artists/tbs/Finer.jpg</Artist_Image>
<Artist_First_Name>Jem</Artist_First_Name>
<Artist_Second_Name>Finer</Artist_Second>
<Artist_Year>1955</Artist_Year>
<Artist_Country>Ireland</Artist_Country>

</artist>
<br /><artist>
<Artist_Image></Artist_Image>
<Artist_First_Name></Artist_First_Name>
<Artist_Second_Name></Artist_Second>
<Artist_Year></Artist_Year>
<Artist_Country></Artist_Country>
</artist>

<br /><artist>
<Artist_Image></Artist_Image>
<Artist_First_Name></Artist_First_Name>
<Artist_Second_Name></Artist_Second>
<Artist_Year></Artist_Year>
<Artist_Country></Artist_Country>
</artist>
<br /></artists>

and the same problem as discussed above remains true for the PHP output in the browser

Steerpike




msg:3653665
 2:58 pm on May 19, 2008 (gmt 0)

Have you checked to make sure the database doesn't have a blank record in it?

cameraman




msg:3653761
 5:07 pm on May 19, 2008 (gmt 0)

for($x = 0 ; $x < mysql_num_rows($rs_country) ; $x++){

is correct because you're starting from zero.
The problem is you're "pre" fetching a record before the loop and not doing anything with it:

$row_rs_country = mysql_fetch_assoc($rs_country); //--- problem here?
$totalRows_rs_country = mysql_num_rows($rs_country); //-- here?

$xml_output = "<?xml version=\"1.0\"?>\n";
.
.

j milo taylor




msg:3653785
 5:41 pm on May 19, 2008 (gmt 0)

cameraman,

thanks once again. that makes sense.

is this further modification possible?

i.e. for the xml filename to be modified according the query?

so if i look at irish artists the results will be written to
XML-ARTIST_COUNTRY-22.xml

if i look at norwegian artists the results will written to
XML-ARTIST_COUNTRY-15.xml

uk artists
XML-ARTIST_COUNTRY-1.xml

and so on.. .

would i need to create these pages first, or can they be made on the fly?

cameraman




msg:3653898
 7:21 pm on May 19, 2008 (gmt 0)

You should be able to, I'm not understanding why you're doing this:
$filename = 'XML-ARTIST_COUNTRY.xml';
if (
is_writable($filename)) {
if (!$handle = fopen($filename, 'w')) {

That function will return false if the file doesn't already exist, but the 'w' flag on the next line is going to wipe out its contents anyway. If you're only using is_writable because it seemed like a good idea at the time, you can take it out and tack on the country code (you'll need to get it from somewhere) to the file name:
$filename = 'XML-ARTIST_COUNTRY-' . $code . '.xml';

If you were using it to determine write permission then you may want to combine it with some other checks, and you might want to have a look at fileperms() [us.php.net].

j milo taylor




msg:3654038
 10:22 pm on May 19, 2008 (gmt 0)

cameraman,

you are a total star, yet again you save the day!

thanks so much for responding to my rather strange requests.

a bit of background is perhaps appropriate.

i'm working on a phd project. i have developed a database of sound artists (strange in itself). the idea is to query the database, create xml files, transform these via xslt into x3d, and then use this x3d to create an immersive audio-visual environment where sounds and images can be manipulated in realtime performance

so there you go!

thanks once again
milo

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved