Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Below I am getting a syntax error, 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 'call, county, id, location, callcreated, station, units, calltype, lat, lng) VAL' at line 1, and cant figure out why! Any help would be greatly appreciated!

<?php

mysql_connect("localhost", "test", "test") or die(mysql_error());
mysql_select_db("firecom") or die(mysql_error());

$data = file_get_contents("http://208.71.205.35/PITS/");//thanks WCCCA!
$pattern = "/id=\"hidXMLID\" value=\"([^\"]+)\"/";//looking for the rnd xml id#
preg_match_all($pattern, $data, $xmlext);

$url = "http://208.71.205.35/PITS/xml/fire_data_" . $xmlext[1][0] . ".xml";//putting together the secret xml url
$xml = simplexml_load_file($url);

foreach ($xml->marker as $element) {

$lat = $element->attributes()->lat;
$lng = $element->attributes()->lng;
$countydirty = $element->AGENCY;// gets agency
$wcccanumberdirty = $element->CALL_NO;
$iddirty = $element->TWO_DIGIT_CALL_NO;// gets call id#
$calldirty = $element->CALL_TYPE_FINAL_D;// gets call type
$locationdirty = $element->LOCATION;// gets location
$callcreateddirty = $element->CALL_CREATED_DATE_TIME;
$stationdirty = $element->BEAT_OR_STATION;// get first marker station
$unitsdirty = $element->UNITS;// get first marker units
$calltypedirty = $element->TYPE; 

//this next section removes the "~" from the start of all the lines
$county = str_replace('~','',$countydirty);
$wcccanumber = str_replace('~','',$wcccanumberdirty);
$id = str_replace('~','',$iddirty);
$call = str_replace('~','',$calldirty);
$location = str_replace('~','',$locationdirty);
$callcreated = str_replace('~','',$callcreateddirty);
$station = str_replace('~','',$stationdirty);
$units = str_replace('~','',$unitsdirty);
$calltype = str_replace('~','',$calltypedirty);

mysql_query("INSERT INTO calls (wcccanumber, call, county, id, location, callcreated, station, units, calltype, lat, lng) VALUES('$wcccanumber', '$call', '$county', '$id', '$location', '$callcreated', '$station', '$units', '$calltype', '$lat', '$lng')") or die(mysql_error()); 

echo "$call - $county - $wcccanumber - $id - $location - $callcreated - $station - $units - $calltype <br />";
}

?>
share|improve this question
 
I assume you have checked to be sure you have the column names right? –  NappingRabbit Dec 10 '12 at 20:23
 
The problem was the reserved word "call". –  Jon Erickson Dec 10 '12 at 20:26
add comment

3 Answers

up vote 5 down vote

call is a reserved word, it must be encased in back ticks:

INSERT INTO calls (wcccanumber, `call`, ...
share|improve this answer
add comment
up vote 2 down vote

call is a reserved word in mysql, so if you use it as a column name you need to quote it in backticks:

wcccanumber, `call`, county...

Apart from that you need to switch to PDO / mysqli and prepared statements to fix the potential sql injection problem you have.

share|improve this answer
 
Thanks, that was it. What are you talking about the possible sql injection? –  Jon Erickson Dec 10 '12 at 20:25
 
@Jon Erickson If one of your fields contains for example a ' character, it will break your sql statement and this can be abused. Without changing to PDO or mysqli you should at least use mysql_real_escape_string on your variables before you add them to your query. –  jeroen Dec 10 '12 at 20:27
 
Alright, thanks. By the way, the XML document has no way to be abused. It is coming from a governmental agency where the URL to the xml link is constantly changing and completely random. –  Jon Erickson Dec 10 '12 at 20:29
 
@Jon Erickson Okay, but there still could be (valid) ' characters in the data and that will break your application. –  jeroen Dec 10 '12 at 20:30
add comment
up vote 1 down vote

call is a reserved word. You'll have to quote it with backticks:

mysql_query("INSERT INTO calls (wcccanumber, `call`, county, id, ...

P.S. For a database problem (especially syntax errors), you don't need to include all of that DOM stuff. how you get the values for a query is pretty much always irrelevant.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.