PHP from XML Inserting into DB

[ackerchez]

Hey Everyone,

 

I'm new to the forum and have a quick question which I am sure is a really simple thing for all you gurus out here.

 

I have a php file in which I am reading in XML from a file and trying to parse and insert the correct data into a database. The structure of the XML is as follows:

 

<?xml version="1.0" encoding="utf-8"?>
<root>
  <sectioncode label="Biology" id="1">
    <lab label="Biological Materials" id="2" />
  </sectioncode>
  <sectioncode label="Chemistry" id="2">
    <lab label="Chemical Materials" id="3" />
    <lab label="BioChem" id="4" />
    <lab label="Charles' law" id="5" />
  </sectioncode>
</root>

 

So what I am trying to do is to take the id attribute from the sectioncode node and the label attribute from the lab node and put them in a table. There is a twist however in that I need to first lookup the existing lab id's from that sectioncode and if a lab with that id already exists, it should not copy in that lab. Here is the php code that I have:

 

$xml = simplexml_load_file("sample.xml");

foreach ($xml->sectioncode as $section)
{
	$sectionid=(string)$section['id'];

	foreach ($section->lab as $lab)
	{
		$labname=(string)$lab['label'];
		$labid=(string)$lab['id'];

		$query = "Select * from dummy where sectionnumber='$sectionid'";
		$result = mysql_query($query);
		$row = mysql_fetch_assoc($result);

		if($row['labid']!=$labid)
		{
			$query2 = ("INSERT INTO `dummy` SET `sectionnumber`='$sectionid', `title`='$labname', `relpath`='',  `labid`='', `status`='',
			`publishdate`='', `unpublishdate`=''");
			$result2 = mysql_query($query2);
		}
	}	

}

When I run the code I am getting the first three labs copying (Biological Materials, Chemical Materials, and BioChem) regardless of whether the id's are equal or not and never getting the fourth lab copied.

 

Can anyone spot where I am going wrong here?

 

Thanks!!

[ignace]

Charles' Law that is where your problem is remove the single quote (')

[PFMaBiSmAd]

ALL string data that could have SQL special characters in it that would break a query (or allow sql injection) must be escaped. Use the mysql_real_escape_string() function on the data before you put it into the query.

[nadeemshafi9]

you shouldent put xml into  a db its nto practical, you should build a table out of the data and maintain it, convert the xml into an array whenever you need it.

[ackerchez]

ALL string data that could have SQL special characters in it that would break a query (or allow sql injection) must be escaped. Use the mysql_real_escape_string() function on the data before you put it into the query.

 

Hey Everyone,

 

Thanks for the advice about the apostrophie on the Charles' Law name. Now when I run the script it all gets entered. However, can anyone tell me if there is something wrong with my original if statement? The idea is to check if the new ID exists in the DB already and if it doesn't, copy in the new lab title as a new lab.

 

Thanks!!