PHP, XML, MySQL and Flash

[JasonBruce88]

Hi all I have a small issue its probably something simple but I have been looking at it for hours and am just going round in circles trying to fix it. I am relatively new to PHP, but do understand why I get most errors and what they mean. Any way the problem is I am mking a Flash CS4 movie that takes data from a MySQL data base using XML generated by PHP the code I am using is

 

<?php 

$con = mysql_connect("server", "user", "password");


if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_data_base", $con);


$result = mysql_query ('SELECT * FROM media')or die(mysql_error());
echo "<?xml version=\"1.0\"  encoding=\"utf-8\"?>\n";
if ($row = mysql_fetch_array($result)) {


do{	
($line = mysql_fetch_array($result));

echo "<media>\n";
echo"<fileName>" . $line['fileName'] . "</fileName>\n";
echo"<Artist>" . $line["Artist"] . "</Artist>\n";
echo"<Caption>" . $line["Caption"] . "</Caption>\n";
echo"<fileURL>" . $line["fileURL"] . "</fileURL>\n";
echo "</media>\n";
}while($row = mysql_fetch_array($result));
} else {print "Sorry, no records were found!";}

?>

 

The 3 errors that I get are

 

1. only one result is taken from the database there should be 3 as there are 3 rows of data in that table.

2. When I look at the page source in my browser It shows the first set of data in the right nodes, but then it has a second empty set of nodes.

3.When get/attempt Flash to use the XML data it says TypeError: Error #1088: The markup in the document following the root element must be well-formed. I believe that this though is simply because the PHP file is not working correctly.

 

Any help on this will be a life saver thank you 

[trq]

You need to use a while not  a do while.

 

while ($row = mysql_fetch_array($result)) {
  echo "<media>\n";
  echo"<fileName>" . $row['fileName'] . "</fileName>\n";
  echo"<Artist>" . $row["Artist"] . "</Artist>\n";
  echo"<Caption>" . $row["Caption"] . "</Caption>\n";
  echo"<fileURL>" . $row["fileURL"] . "</fileURL>\n";
  echo "</media>\n";
}

[JasonBruce88]

Cheers for that, however now I just get a blank page and the page source is just one empty set of nodes

[trq]

Post your current code.

[JasonBruce88]

while	
($line = mysql_fetch_array($result));

echo "<media>\n";
echo"<fileName>" . $line['fileName'] . "</fileName>\n";
echo"<Artist>" . $line["Artist"] . "</Artist>\n";
echo"<Caption>" . $line["Caption"] . "</Caption>\n";
echo"<fileURL>" . $line["fileURL"] . "</fileURL>\n";
echo "</media>\n";

 

and the page source returns

 
<media> 
<fileName></fileName> 
<Artist></Artist> 
<Caption></Caption> 
<fileURL></fileURL> 
</media> 

 

[trq]

I meant all of it. You had numerous errors in your original post and you are now missing curly braces in your last.

[JasonBruce88]

Ah my bad

 

here you go

<?php 

$con = mysql_connect("server", "user", "password");


if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("databse", $con);


$result = mysql_query ('SELECT * FROM media')or die(mysql_error());
echo "<?xml version=\"1.0\"  encoding=\"utf-8\"?>\n";
if ($row = mysql_fetch_array($result)) {


while	
($line = mysql_fetch_array($result));

echo "<media>\n";
echo"<fileName>" . $line['fileName'] . "</fileName>\n";
echo"<Artist>" . $line["Artist"] . "</Artist>\n";
echo"<Caption>" . $line["Caption"] . "</Caption>\n";
echo"<fileURL>" . $line["fileURL"] . "</fileURL>\n";
echo "</media>\n";
}

?>

 

[trq]

The if() statement is calling mysql_fetch_array() when it shouldn't be. There goes one row of data never to be seen again. You are then still missing braces.

 

<?php

$con = mysql_connect("server", "user", "password");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("databse", $con);

if ($result = mysql_query ('SELECT * FROM media')) {
  if (mysql_num_rows($result)) {
    header ("Content-Type:text/xml");
    echo '<?xml version="1.0"  encoding="utf-8"?>' . "\n";
    while ($row = mysql_fetch_array($result)) {
      echo "<media>\n";
      echo"<fileName>" . $row['fileName'] . "</fileName>\n";
      echo"<Artist>" . $row["Artist"] . "</Artist>\n";
      echo"<Caption>" . $row["Caption"] . "</Caption>\n";
      echo"<fileURL>" . $row["fileURL"] . "</fileURL>\n";
      echo "</media>\n";
    }
  } else {
    echo "Sorry, no records were found!";
} else {
  trigger_error(mysql_error());
}

?>

 

Edit: Just added the header() call.

[JasonBruce88]

I have added what you have put into my code but it is not liking the second else statement as it is not expecting it but then when I put a brace in front of it does not expect that either

 

$con = mysql_connect("server", "user", "password");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("databse", $con);
if ($result = mysql_query ('SELECT * FROM media')) {
if (mysql_num_rows($result)) {
	echo "<?xml version=\"1.0\"  encoding=\"utf-8\"?>\n";
while	
($row = mysql_fetch_array($result));

echo "<media>\n";
echo"<fileName>" . $row['fileName'] . "</fileName>\n";
echo"<Artist>" . $row["Artist"] . "</Artist>\n";
echo"<Caption>" . $row["Caption"] . "</Caption>\n";
echo"<fileURL>" . $row["fileURL"] . "</fileURL>\n";
echo "</media>\n";
}
} 
else {
    echo "Sorry, no records were found!";
} 
else {
  trigger_error(mysql_error());
}

 

 

[trq]

Look at my while line compared to yours.

[JasonBruce88]

Stupid mistake made on my behalf there

<?php 

$con = mysql_connect("server", "user", "password");


if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("database", $con);


if ($result = mysql_query ('SELECT * FROM media')) {
if (mysql_num_rows($result)) {
	header ("Content-Type:text/xml");
	echo '<?xml version="1.0"  encoding="utf-8"?>' . "\n";
while ($row = mysql_fetch_array($result)) {

echo "<media>\n";
echo"<fileName>" . $row['fileName'] . "</fileName>\n";
echo"<Artist>" . $row["Artist"] . "</Artist>\n";
echo"<Caption>" . $row["Caption"] . "</Caption>\n";
echo"<fileURL>" . $row["fileURL"] . "</fileURL>\n";
echo "</media>\n";
}
} 
else {
    echo "Sorry, no records were found!";
} 
}
else {
  trigger_error(mysql_error());
}
?>

 

works kind of when looking at the page source all records are there, but only one is displayed on the page as i get an xml error

 

error on line 8 at column 1: Extra content at the end of the document

 

Line 8 on the php page returned from the server is when the next set of nodes begins again. does that mean there is a problem with where the XML decoration is placed, as it is also getting echoed at every loop?? 

[trq]

Can we see an example of the complete output? Do you have a link?

[JasonBruce88]

I do not have a link as it is only local

 

the message i get through the browser is

 

This page contains the following errors:

 

error on line 7 at column 1: Extra content at the end of the document

Below is a rendering of the page up to the first error.

 

test test test tasha.jpg

 

and the xml in the page source is

<media> 
<fileName>test</fileName> 
<Artist>test</Artist> 
<Caption>test</Caption> 
<fileURL>tasha.jpg</fileURL> 
</media> 
<media> 
<fileName>Bad Romance</fileName> 
<Artist>Lady Gaga</Artist> 
<Caption>Bad Romance Clip</Caption> 
<fileURL>Badromance.mp3</fileURL> 
</media> 
<media> 
<fileName>Spider</fileName> 
<Artist>me</Artist> 
<Caption>A massive spider</Caption> 
<fileURL>07092009151.jpg</fileURL> 
</media> 
<media> 
<fileName>Jellyfish</fileName> 
<Artist>Jellyfish</Artist> 
<Caption>Jellyfish</Caption> 
<fileURL>Jellyfish.jpg</fileURL> 
</media> 

 

which is the right amount of values in the database

 

[trq]

Looks fine to me. You might want to use a validator at w3c to take a better look.