Page 1 of 1

err.. sql error

Posted: Thu Aug 18, 2005 4:00 pm
by s.dot
Seems like I am having all kinds of troubles with my zip code script.

Code: Select all

$zip = $buffer3[0];
	$lat = $buffer3[1];
	$long = $buffer3[2];
	$city = $buffer3[3];
	$state = $buffer3[4];
	$county = $buffer3[5];
	$type = $buffer3[6];
	$SQL = "INSERT INTO location(zip,lat,long,city,state,county,type) VALUES('$zip','$lat','$long','$city','$state','$county','$type')";
	echo $SQL."<BR>";
	mysql_query("INSERT INTO location(zip,lat,long,city,state,county,type) VALUES('$zip','$lat','$long','$city','$state','$county','$type')") or die(mysql_error());
Result:

Code: Select all

INSERT INTO location(zip,lat,long,city,state,county,type) VALUES('00501','+40.922326','-072.637078','HOLTSVILLE','NY','SUFFOLK','UNIQUE ')

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 'long,city,state,county,type) VALUES('00501','+40.922326','-072.637078','HOLTSVIL' at line 1
Where's this error coming from?

Posted: Thu Aug 18, 2005 4:10 pm
by korto
Are all the table properties of string type ? Are you trying to pass a string to an integer?

Posted: Thu Aug 18, 2005 4:14 pm
by s.dot

Code: Select all

CREATE TABLE `location` (
  `id` int(20) NOT NULL auto_increment,
  `zip` int(6) NOT NULL default '0',
  `lat` varchar(20) NOT NULL default '',
  `long` varchar(20) NOT NULL default '',
  `city` varchar(100) NOT NULL default '',
  `state` varchar(20) NOT NULL default '',
  `county` varchar(100) NOT NULL default '',
  `type` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

Posted: Thu Aug 18, 2005 4:20 pm
by korto
are you sure you want zip to be of type int?
In your example zip = '00501', even without the quotes it would be 501 (as integer) not 00501

Posted: Thu Aug 18, 2005 4:20 pm
by Sander
The only thing I notice is that you have quotes around the zip code. If deleting those doesn't fix it, try to add those ` things to the "location(zip, etc)" part. Like this:

Code: Select all

INSERT INTO location(`zip`,`lat`,`long`,`city`,`state`,`county`,`type`) VALUES(00501,'+40.922326','-072.637078','HOLTSVILLE','NY','SUFFOLK','UNIQUE ')

Posted: Thu Aug 18, 2005 5:24 pm
by feyd
00501 != 501 ;)

changing the type to varchar, or leaving it int, but adding zero-fill may be a good idea..

Posted: Thu Aug 18, 2005 6:26 pm
by raghavan20
as Sander pointed out the fields were not with back ticks and should been considered as mysql keywords long and type.