PHP creating XML output problem

[JoeSyn]

Hey everyone,

The PHP below was written to accept eight variables to search a MySQL database and return three results. However I am getting the following errors:

Localhost: Only one top level element is allowed in an XML document. Error processing resource ‘http:’.
Dreamweaver: error on line 2 at column 1: extra content at the end of the document
Flash: Error #2101: The String passed to URLVariables.decode() must be a URL-encoded query string containing name/value pairs

[php]<?php

header(“Content-type: text/xml”);

$artist =$_POST[‘artist’];
$medium =$_POST[‘medium’];
$size =$_POST[‘size’];
$format =$_POST[‘format’];
$subject =$_POST[‘subject’];
$colour =$_POST[‘colour’];
$price =$POST[‘price’];
$available =$POST[‘available’];

$xmlBody = ‘<?xml version="1.0" encoding="ISO-8859-1"?>’;

$xmlBody .= “”;

$host = “127.0.0.1”;
$user = “root”;
$pass = “”;
$database = “syntegallery”;

$con = mysql_connect($host, $user, $pass) or die (mysql_error());
mysql_select_db($database, $con) or die (“Could not find database”);

$query = “SELECT Filename, Thumbnail, Empirical”;
$query .= “FROM gallery”;
$query .= “WHERE Artist=’$artist’”;
$query .= “AND Medium=’$medium’”;
$query .= “AND Category=’$size’”;
$query .= “AND Format=’$format’”;
$query .= “AND Subject=’$subject’”;
$query .= “AND Colour=’$colour’”;
$query .= “AND Range=’$price’”;
$query .= “AND Available=’$available’”;
$query .= “ORDER BY Empirical”;

$result = mysql_query($query, $con) or die (“Data not found”);

$i = 0;

while($row = mysql_fetch_array($result))
{
$Filename = $row[“Filename”];
$Thumbnail = $row[“Thumbnail”];
$Empirical = $row[“Empirical”];
$i++;
$xmlBody .= ’

‘.$Filename.’
‘.$Thumbnail.’
‘.$Empirical.’
';
}
$xmlBody .= “”;

print $xmlBody;
mysql_close($con);
exit();
?>[/php]

Any advice on what could be the problem would be greatly appreciated!

[jSherz]

It appears that you haven’t actually made a top level element. Try using:

[php]$xmlBody .= “”;[/php]

and

[php]$xmlBody .= “”;[/php]

(Where both of those would contain ‘’ and ‘’ respectively). Please could you also provide a sample output (the XML that is generated by your script)?

[JoeSyn]

Hey thanks for the reply,

Just to confirm, are the $xmlBody .= and $xmlBody .= tags in my script in the wrong position /sequence and therefore not defining a top level element?

Unfortunately I can also not generate any XML from this script whatsoever (just the error codes I listed initially) or am I loosing the plot?

[jSherz]

For testing, comment out:

[php]header(“Content-type: text/xml”);[/php]

So that the browser doesn’t try and process it. Please could you then post the resulting XML?

[JoeSyn]

The output reads as follows:

Notice: Undefined index: artist in C\Program Files (x86)\EasyPHP-5.3.8.1\www.test.php on line 9

and continues to give the same error for all variables ‘medium’ (line 10) through to ‘available’ (line 16)

I have tried placing the actual data to be searched for into these variables but to no avail. I noticed too that the last two $_POST lines were missing an underscore which I have rectified but I think the problem may lie with defining the above variables?

[jSherz]

When you test this, are you submitting data to the PHP file with a form?

[JoeSyn]

Ok I removed the $_POST variable altogether and simply entered the data to be searched for. The result was “Data not found” as defined in the die condition of the $result variable.

I then removed the die condition and received a warning:

mysql_fetch_array() expects parameter 1 to be resource, boolean given in … URL :’(

[JoeSyn]

Apologies - yes I’m using a ComboBox/drop down list so I removed the variables being sent to test the script without external input

[jSherz]

When you use mysql_query with a select query, it will return TRUE if the query succeeded or FALSE if the query failed. The TRUE result does not mean that any data was returned by the query. I believe the issue is that when you generate your query, you do not add any spaces after each new line of code.

Try changing:

[php]$query = “SELECT Filename, Thumbnail, Empirical”;
$query .= “FROM gallery”;
$query .= “WHERE Artist=’$artist’”;
$query .= “AND Medium=’$medium’”;
$query .= “AND Category=’$size’”;
$query .= “AND Format=’$format’”;
$query .= “AND Subject=’$subject’”;
$query .= “AND Colour=’$colour’”;
$query .= “AND Range=’$price’”;
$query .= “AND Available=’$available’”;
$query .= “ORDER BY Empirical”;
[/php]

to:

[php]$query = "SELECT Filename, Thumbnail, Empirical ";
$query .= "FROM gallery ";
$query .= "WHERE Artist=’$artist’ ";
$query .= "AND Medium=’$medium’ ";
$query .= "AND Category=’$size’ ";
$query .= "AND Format=’$format’ ";
$query .= "AND Subject=’$subject’ ";
$query .= "AND Colour=’$colour’ ";
$query .= "AND Range=’$price’ ";
$query .= "AND Available=’$available’ ";
$query .= “ORDER BY Empirical”;
[/php]

Also, you should use mysql_num_rows in future to test if any data was returned.

[JoeSyn]

I added spaces but still no data is being returned. I tried adding the mysql_num_rows to see if any data is returned at all but again the script dies in the $result line.

Would you suggest I reconsider using the mysql_query together with select query?

Thanks again for your patience.

Just to give you some more information, I removed all <> and " references in the database and replaced all image URL’s (for both Filename and Thumbnail columns) to: “image.jpg” as I believe XML attributes need to be quoted. Do you think the problem is with the image URL’s?

Or is there any chance that the filename and thumbnail results need to be defined as images? For instance:

‘.$Filename.’
‘.$Thumbnail.’

[jSherz]

Are all the field / table names correct?

[JoeSyn]

Yes I double-checked everything:

Database name: syntegallery
Table: gallery
Columns: Artist, Medium, Category, Format, Subject, Colour, Range, Available, Filename, Thumbnail, Empirical

There is an ID column at the beginning of the table which is auto incremented, serves as the index and carries a primary key, I’m not sure that has any affect?

[jSherz]

Do echo on the $query after it’s created and then post that here and test it in PHPMyAdmin or a similar tool.

[JoeSyn]

Below is the PHP we’ve reduced so far with fixed search criteria:

[php]<?php

$artist = “Jackson, Barry”;
$medium = “Bronze”;
$size = “Small”;
$format = “Horizontal”;
$subject = “Fauna”;
$colour = “Brown”;
$price = “R20,001 and above”;
$available = “Yes”;

$host = “127.0.0.1”;
$user = “root”;
$pass = “”;
$database = “syntegallery”;

$con = mysql_connect($host, $user, $pass) or die (mysql_error());
mysql_select_db($database, $con) or die (“Could not find database”);

$query = "SELECT Filename, Thumbnail, Empirical ";
$query .= "FROM gallery ";
$query .= "WHERE Artist=’$artist’ ";
$query .= "AND Medium=’$medium’ ";
$query .= "AND Category=’$size’ ";
$query .= "AND Format=’$format’ ";
$query .= "AND Subject=’$subject’ ";
$query .= "AND Colour=’$colour’ ";
$query .= "AND Range=’$price’ ";
$query .= "AND Available=’$available’ ";
$query .= “ORDER BY Empirical”;

echo $query;

?>[/php]

The output is as follows:

SELECT Filename, Thumbnail, Empirical FROM gallery WHERE Artist=‘Jackson, Barry’ AND Medium=‘Bronze’ AND Category=‘Small’ AND Format=‘Horizontal’ AND Subject=‘Fauna’ AND Colour=‘Brown’ AND Range=‘R20,001 and above’ AND Available=‘Yes’ ORDER BY Empirical

Can the echoed result (replacing criteria with defined variables) be used in the mysql_query?

[JoeSyn]

I’ve created a simple my_sql query to test the result:

[php]$result = mysql_query("SELECT Empirical FROM gallery WHERE Artist = ‘$artist’ ") or die (“Data not found”);

echo $result;[/php]

Using the defined variables in the last post. The echoed result was: Resource id#4

[jSherz]

Well, at least we know the query worked (if it returned a valid result). Could you try adding some more fields and then seeing when it stops working?

[JoeSyn]

Progress at last! I have written the following and am finally getting results:

[php]<?php

$artist = ‘Jackson, Barry’;
$medium = ‘Bronze’;
$size = ‘Small’;
$format = ‘Horizontal’;
$subject = ‘Fauna’;
$colour = ‘Brown’;
$price = ‘R20,001 and above’;
$available = ‘Yes’;

$host = “127.0.0.1”;
$user = “root”;
$pass = “”;
$database = “syntegallery”;

$con = mysql_connect($host, $user, $pass) or die (mysql_error());
mysql_select_db($database, $con) or die (“Could not find database”);

$result = mysql_query("SELECT Filename, Thumbnail, Empirical FROM gallery WHERE Artist=’$artist’ AND Medium=’$medium’ AND Category=’$size’ AND Format=’$format’ AND Subject=’$subject’ AND Colour=’$colour’ AND Available=’$available’ ", $con);

$row = mysql_fetch_array($result);

print $row[‘Filename’];
print $row[‘Thumbnail’];
print $row[‘Empirical’];

mysql_close($con);
exit();
?>[/php]

I haven’t yet created a while loop but the result shows the first row of matching data.

The problem is the AND Range=’$price’ query. When this is added to the query it returns the following warning:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:… on line 23

Is there any reason why the $price variable is not being recognised as a resource?

[jSherz]

It’s the mysql_query result that’s not a resource - it will have returned FALSE due to an error while trying to run it.

Is the ‘R20,001 and above’ how the price is stored in the database or are you trying to look for prices above 20,001. Also, what format is the field in the database? If it’s a number type then the issue may be comparing it to a string.

[JoeSyn]

Yes I have entered 'R20,001 and above" exactly as typed into each entry and set the type to VARCHAR(20) and MIME type to text/plain to try curb exactly what you mentioned.

Just to clarify, each artwork is listed in the database according to the price range it falls into i.e.:

less than R1,000
R1,001 - R5,000
…
R20,001 and above

The aim being that users select the price category they wish to purchase for, so ideally I’d want the variable being sent to be recognised as no more than being text

[jSherz]

As a final check, are you sure that the Range field is called ‘Range’ exactly? Apart from that, I’ve got no idea what is causing the query to fail.

Scrap that.

Try placing Range inside of back ticks:

AND `Range`='VALUE'

I think the word Range might be picked up by MySQL as a keyword.