XML data added to MySQL Database Problem

[bigmac90]

Hi all

 

I've got a slight problem adding some XML data to a MySQL database. My code looks like: 

 

// Load XML file

$xml = simplexml_load_file('products.xml') or die ("Could not load XML file");

 

// Database variables

$host = "localhost";

$username = "user_name";

$password = "pass_word";

$dbname = "database_name";

 

// Foreach piece of XML data - send to MySQL database and put into the xmlFeed_db database

foreach ($xml->product as $data) {

 

// MySQL connect to database

$con = mysql_connect("$host", "$username", "$password") or die ("Unable to connect to database");

$select_db = mysql_select_db($dbname) or die ("Unable to select database");

 

// Put all data into numerous variables 

$id = $data->id;

$man = $data->manufacturers;

$des = $data->description;

$price = $data->price;

$sku = $data->sku;

$category = $data->category;

$subcategory = $data->subcategory;

 

// SQL statement

$query = "INSERT INTO tablename VALUES ('$id','$man','$des','$price','$sku','$category','$subcategory')";

 

// Execute SQL statement

mysql_query($query) or die ("Insertion Error: " . mysql_error());

 

// Close MySQL 

mysql_close();

 

When I run this script it only stores 249 rows out of 2000 and then it shows an error stating: 

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's)', '117.08','CAB-OCTAL-ASYNC=','Networking','Networking Cables')' at line 1"

 

Does anyone know why this error message is showing? I've looked at my syntax but I can't see a problem. Maybe a fresh pair of eyes can help.

 

Thanks

Edited May 6, 2013 by bigmac90

[Barand]

Seems like you have a data value containing an apostrophe.

 

Use mysql_real_escape_string() on each of the data items before putting tham in your query