PHP/XML/MySQL problem...

[davejog]

 

Hi, new here, Dave is the name.

 

I have this problem:

 

every x minutes I have an XML which I save on local path, and I use new SimpleXMLElement() to parse it.

 

XML sample:

<?xml version="1.0" encoding="utf-8"?>
<all_cars>
	<car>
		<color>silver</color>
		<year>2008</year>
		<price>23000</price>
		<model>Focus</model>
		<company>Ford</company>
	</car>
	<car>
		<color>black</color>
		<year>2007</year>
		<price>20000</price>
		<model>Focus</model>
		<company>Ford</company>
	</car>
</all_cars>

 

 

now, I do a foreach loop like this:

foreach($xml->cars->car as $car) {
// bla bla
}

 

there is no problem with the DB con, I tested it, for some reason, it seems to fail to insert the query...

I want to insert into DB, so I created this:

$res = mysql_query("INSERT INTO tmp_test VALUES ({$car->color},{$car->year},{$car->price},{$car->model},{$car->company})",$hd)
or die ("Unable to run cars insert query");

 

it keeps returning "Unable to run cars insert query"... again, the problem is in how I put

{$car->year}

and such in the query...

 

anyone??

[jonsjava]

$res = mysql_query("INSERT INTO tmp_test VALUES ('{$car->color}','{$car->year}','{$car->price}','{$car->model}','{$car->company}');",$hd)
or die ("Unable to run cars insert query");

funny how a missing single quote can change everything.

[Psycho]

Or even better create the query as a variable so you can echo it out to the page if it fails!

 

$query = "INSERT INTO tmp_test VALUES ('{$car->color}','{$car->year}','{$car->price}','{$car->model}','{$car->company}');";

$res = mysql_query($query, $hd)
       or die ("Unable to run cars insert query:<br />$query<br />Error:<br />".mysql_error());

[davejog]

ok, found the problem, it looks like some of the fields have ' or " inside, is there a way to make sure they don't f**k the query?

[jonsjava]

whenever you put the data into the db, do this:

addslashes($_POST['user_data']);

[davejog]

jonsjava can you please explain how do I use

addslashes($_POST['user_data']);

in the php page? I looked in php.net but couldn't figure this out... thanks!

[jonsjava]

addslashes adds slashes to any escape character like ' and ". So, when you have data that you need to "un-escape" you pass it through addslashes

[davejog]

yup, I got that, but where exactly do I use it in the code? how do I insert this in the php page?

[jonsjava]

you add it any time you are importing data to the db. That way, when you pull the data back out, it's been cleaned up.  As for cleaning up what is already there....I'm too tired to go into that. sry.

[Barand]

yup, I got that, but where exactly do I use it in the code? how do I insert this in the php page?

 

<?php
$str = "<?xml version='1.0' encoding='utf-8'?>
<all_cars>
	<car>
		<color>silver</color>
		<year>2008</year>
		<price>23000</price>
		<model>Focus</model>
		<company>Ford's</company>
	</car>
	<car>
		<color>black</color>
		<year>2007</year>
		<price>20000</price>
		<model>Focus</model>
		<company>Ford's</company>
	</car>
</all_cars>";

$xml = simplexml_load_string($str);

foreach ($xml->car as $car)
{
    $color = addslashes($car->color);
    $year = addslashes($car->year);
    $price = addslashes($car->price);
    $model = addslashes($car->model);
    $company = addslashes($car->company);
    
    $sql = "INSERT INTO tmp_test VALUES ('$color','$year','$price','$model','$company')";
    echo $sql, '<br>';
#    $res = mysql_query($sql) or die ("Unable to run cars insert query");
}
?>

[davejog]

woohoo works! thanks for everyone who helped in this thread! thanks a lot!