Welcome to WebmasterWorld Guest from 23.20.238.193

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

xml from php query

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

5+ Year Member



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

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

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Hi,

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

dc

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

5+ Year Member



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

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

5+ Year Member




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.
12:28 pm on May 19, 2008 (gmt 0)

5+ Year Member



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

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

5+ Year Member



Have you checked to make sure the database doesn't have a blank record in it?
5:07 pm on May 19, 2008 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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";
.
.

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

5+ Year Member



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?

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

WebmasterWorld Senior Member 5+ Year Member



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].

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

5+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month