XML Ampersand Issue

ambientsbs
ambientsbs used Ask the Experts™
on
Hello,

I've tried everything I can to get around this issue and I cannot get anything to work. I'm using PHP and SQL to return an XML file with various fields. One of those fields is a URL field which contains & signs within it. I've seen plenty of posts online that say to replace the & sign with & but my problem still persists.

Using this code:

while($row = mysql_fetch_array($rs)) {
								    
$query2 = "SELECT recordID, Year, Make, Model, Price, City, State, Zip, URL FROM MainTable_VehicleDataPull WHERE Year BETWEEN '$startyear' AND '$endyear' AND Make LIKE '%$make%' AND Model LIKE '%$model%' AND City = '$row[city]' AND State = '$row[state]'";
								
$rs2 = mysql_query($query2);					
$numrows = mysql_num_rows($rs2);
$count = $count + $numrows;
									
								
while($result_array = mysql_fetch_assoc($rs2)) {
								
//loop through each key,value pair in row   
						
$xml .= '<';
$xml .= 'record ';
																
foreach($result_array as $key => $value)
{ 
					
						
$xml .= "$key =\"";
$xml .= "";
$xml .= $value;
$xml .="\" ";   


}
							
$xml .= "/>";
								
}
									
									
}

Open in new window



What's happening is that my XML is returning properly for every record except the ones which contain an & or &amp; within them. Those records start with hrecord rather than the <record that all of the correct ones start with like such:


<record recordID="21063" Year="1965" Make="Chevrolet" Model="Corvair Corsa Hard Top" Price="9750" City="Reno" State="NV" Zip="" URL="http://www.hemmings.com/classifieds/carsforsale/chevrolet/corvair_corsa/1395983.html" />
 
hrecord recordID ="22904" Year ="1967" Make ="Chevrolet" Model ="Camaro - Custom Roadster ~ $110,000" Price ="$ 110,000" City ="Reno" State ="NV" Zip ="" URL ="http://www.hotrodhotline.com/classifieds/classifieds.php?a=2&b=39883" />

 

Does anyone have any ideas as to how I can fix this?? This data is for an app and I'm being told by the developer that using CDATA won't work with what he's doing if that's a potential solution. Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technology Scientist
Commented:
try outputing your XML in a different data structure try:

while($row = mysql_fetch_array($rs)) {
								    
$query2 = "SELECT recordID, Year, Make, Model, Price, City, State, Zip, URL FROM MainTable_VehicleDataPull WHERE Year BETWEEN '$startyear' AND '$endyear' AND Make LIKE '%$make%' AND Model LIKE '%$model%' AND City = '$row[city]' AND State = '$row[state]'";
								
$rs2 = mysql_query($query2);					
$numrows = mysql_num_rows($rs2);
$count = $count + $numrows;
									

						
while($result_array = mysql_fetch_assoc($rs2)) {
								
//loop through each key,value pair in row   
						
$xml .= '<record>\n';
foreach($result_array as $key => $value)
{
$xml .= "\t<" . $key . '>' . $value . '</' . $key . ">\n";
}
$xml .= "</record>\n";
								
}
									
									
}
                                  

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
What's the problem here?  Is it changing hrecord into <record ?  Or is there something wrong about the use of &amp; ?

I think you might also want to get an explanation of exactly why CDATA will not work.  There may be a gap in your developer's understanding of CDATA.
Kim WalkerWeb Programmer/Technician

Commented:
At what point are you determining that your opening bracket is being replaced by an "h"? I have to assume that beyond the script you've posted, you're concatenating a carriage return to each line and appending the line to an xml file. Are you opening the xml file in an editor to determine that the error exists or are you using some type of xml parser to produce the error?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial