err.. sql error

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

err.. sql error

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
korto
Forum Commoner
Posts: 36
Joined: Thu Aug 18, 2005 6:30 am
Location: Greece
Contact:

Post by korto »

Are all the table properties of string type ? Are you trying to pass a string to an integer?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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`)
)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
korto
Forum Commoner
Posts: 36
Joined: Thu Aug 18, 2005 6:30 am
Location: Greece
Contact:

Post 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
Sander
Forum Commoner
Posts: 38
Joined: Sat Aug 06, 2005 12:43 pm

Post 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 ')
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

00501 != 501 ;)

changing the type to varchar, or leaving it int, but adding zero-fill may be a good idea..
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

as Sander pointed out the fields were not with back ticks and should been considered as mysql keywords long and type.
Post Reply