xml from php query

[j milo taylor]

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]

Hi,

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

dc

[j milo taylor]

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]

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]

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]

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

[cameraman]

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]

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]

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]

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