syntax error

[jacko_162]

Not my night tonite, but before i go bed im hoping for some advice on what to do.

i have code that reads an XML file, and loops the data and inputs into a database.

i get "You have an error in your SQL syntax; check the"

its basically because all the "location" data contains dash's for example:

 

location="1V-LI2 III - Moon 2 - MbI BepHyJIucb"

 

so adding these to the database i get errors.

my code is as follows;

 

<?php
// INCLUDE DB CONNECTION FILE
include("connect.php");

// CHANGE THE VALUES HERE
$keyID = '***';
$vCode = '***';

// URL FOR XML DATA
$url = "https://api.eveonline.com/corp/MemberTracking.xml.aspx?keyID=".$keyID."&vCode=".$vCode."&extended=1";

// RUN XML DATA READY FOR INSERT
$xml = simplexml_load_file($url);


// Loop Through Names
foreach ($xml->result->rowset[0] as $value) {
echo "characterID:".$value['characterID']." name: ".$value['name']." location: ".$value['location']."<br />";

$characterID = $value['characterID'];
$name = $value['name'];
$startDateTime = $value['startDateTime'];
$baseID = $value['baseID'];
$base = $value['base'];
$title = $value['title'];
$logonDateTime = $value['logonDateTime'];
$logoffDateTime = $value['logoffDateTime'];
$locationID = $value['locationID'];
$location = $value['location'];
$shipTypeID = $value['shipTypeID'];
$shipType = $value['shipType'];
$roles = $value['roles'];
$grantableRoles = $value['grantableRoles'];

// NOW LETS INSERT INTO DATABASE!!
mysql_query("INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES ($characterID,$name,$startDateTime,$baseID,$base,$title,$logonDateTime,$logoffDateTime,$locationID,$location,$shipTypeID,$shipType,$roles,$grantableRoles)") or die(mysql_error());
};
?>

 

data from xml;

<row characterID="90158470" name="SOCK ZERO" startDateTime="2012-03-16 23:41:00" baseID="0" base="" title="" logonDateTime="2013-02-12 15:32:52" logoffDateTime="2013-02-12 15:37:53" locationID="60014911" location="1V-LI2 III - Moon 2 - MbI BepHyJIucb" shipTypeID="-1"shipType="" roles="0" grantableRoles="0"/>

 

any help appreciated.

[scootstah]

Can you do this for me? Replace your mysql_query("INSERT ... line with:

$query = "INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES ($characterID,$name,$startDateTime,$baseID,$base,$title,$logonDateTime,$logoffDateTime,$locationID,$location,$shipTypeID,$shipType,$roles,$grantableRoles)";

mysql_query($query) or die(mysql_error());

 

And then include $query and the full, actual error in your reply.

[jacko_162]

sure, just added what you said and page displays the following:

 

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 'ZERO,2012-03-16 23:41:00,0,,,2013-02-12 15:32:52,2013-02-12 15:37:53,60014911,1V' at line 1

Edited February 13, 2013 by jacko_162

[scootstah]

You didn't post $query.

echo "$query<br /><br />";

mysql_query($query) or die(mysql_error());

[jacko_162]

sorry, here we go;

 

INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90158470,SOCK ZERO,2012-03-16 23:41:00,0,,,2013-02-12 15:32:52,2013-02-12 15:37:53,60014911,1V-LI2 III - Moon 2 - MbI BepHyJIucb,-1,,0,0)

 

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 'ZERO,2012-03-16 23:41:00,0,,,2013-02-12 15:32:52,2013-02-12 15:37:53,60014911,1V' at line 1

[scootstah]

Hmm sorry, the problem was staring at me the whole time.

 

So the issue is that you did not quote any of the strings. Only numerical data and MySQL functions may be used without quotes.

 

'$name','$startDateTime',$baseID,'$base', and so on.

 

You're also not protected from SQL Injection. You should be running mysql_real_escape_string on all input that touches your database.

[jacko_162]

worked.

 

but i have a feeling my foreach loop isnt working as intended.

 

i get the following error now;

 

characterID:90158470 name: SOCK ZERO location: 1V-LI2 III - Moon 2 - MbI BepHyJIucb

INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90158470,'SOCK ZERO','2012-03-16 23:41:00',0,''',','2013-02-12 15:32:52','2013-02-12 15:37:53',60014911,'1V-LI2 III - Moon 2 - MbI BepHyJIucb',-1,'','0','0')

 

Column count doesn't match value count at row 1

updated code;

 

<?php


// INCLUDE DB CONNECTION FILE
include("connect.php");

// CHANGE THE VALUES HERE
$keyID = '*****';
$vCode = '*****';


// URL FOR XML DATA
$url = "https://api.eveonline.com/corp/MemberTracking.xml.aspx?keyID=".$keyID."&vCode=".$vCode."&extended=1";


// RUN XML DATA READY FOR INSERT
$xml = simplexml_load_file($url);


// Loop Through Names
foreach ($xml->result->rowset[0] as $value) {
echo "characterID:".$value['characterID']." name: ".$value['name']." location: ".$value['location']."<br />";


$characterID = $value['characterID'];
$name = $value['name'];
$startDateTime = $value['startDateTime'];
$baseID = $value['baseID'];
$base = $value['base'];
$title = $value['title'];
$logonDateTime = $value['logonDateTime'];
$logoffDateTime = $value['logoffDateTime'];
$locationID = $value['locationID'];
$location = $value['location'];
$shipTypeID = $value['shipTypeID'];
$shipType = $value['shipType'];
$roles = $value['roles'];
$grantableRoles = $value['grantableRoles'];



// NOW LETS INSERT INTO DATABASE!!
$query = "INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES ($characterID,'$name','$startDateTime',$baseID,'$base'',$title','$logonDateTime','$logoffDateTime',$locationID,'$location',$shipTypeID,'$shipType','$roles','$grantableRoles')";


echo "$query<br /><br />";


mysql_query($query) or die(mysql_error());
};
?>

 

still nothing in database yet.

Edited February 13, 2013 by jacko_162

[scootstah]

'$base''

 

You have an extra single-quote here.

[jacko_162]

spotted it just before you posted

 

now it seems to be getting somewhere.. i have 4 rows of data in the database and i get the following syntax error again!

 

characterID:90158470 name: SOCK ZERO location: 1V-LI2 III - Moon 2 - MbI BepHyJIucb
INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90158470,'SOCK ZERO','2012-03-16 23:41:00',0,'','','2013-02-12 15:32:52','2013-02-12 15:37:53',60014911,'1V-LI2 III - Moon 2 - MbI BepHyJIucb',-1,'','0','0')

characterID:90197511 name: good kharma location: LBC-AW
INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90197511,'good kharma','2011-10-05 00:21:00',0,'','','2013-02-11 13:48:41','2013-02-11 13:52:35',30000683,'LBC-AW',29248,'Magnate','0','0')

characterID:90210962 name: Dylan Rider location: Iivinen X - Moon 10 - Genolution Biohazard Containment Facility
INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90210962,'Dylan Rider','2011-12-09 13:31:00',0,'','','2011-12-18 12:01:34','2011-12-18 12:05:15',60013189,'Iivinen X - Moon 10 - Genolution Biohazard Containment Facility',602,'Kestrel','0','0')

characterID:90268294 name: Razell Starfire location: 23M-PX III - TROLL Pluto's Fun House
INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES (90268294,'Razell Starfire','2012-04-03 14:48:00',0,'','','2012-04-12 01:24:08','2012-04-12 01:26:53',61000245,'23M-PX III - TROLL Pluto's Fun House',-1,'','0','0')

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 Fun House',-1,'','0','0')' at line 1

[jacko_162]

problem solved..