Can't find what I have done wrong

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Can't find what I have done wrong

Post by andym01480 »

Code: Select all

$address="INSERT INTO address (add1, add2, town, county, postcode, homephone) VALUES (`{$clean['add1']}`,`{$clean['add2']}`,`{$clean['town']}`,`{$clean['county']}`,`{$clean['postcode']}`,`{$clean['phone']}`)";
echo $address.'<br>';
$result2=mysql_query($address) OR DIE("Couldn't store address".mysql_error());
is outputting this

Code: Select all

INSERT INTO address (add1, add2, town, county, postcode, homephone) VALUES (`streetname`,`areaname`,`town`,`countyname`,`B21 5FE`,``)
Couldn't store addressUnknown column 'streetname' in 'field list'
Sql dump of table structure

Code: Select all

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL auto_increment,
  `add1` varchar(255) NOT NULL,
  `add2` varchar(255) NOT NULL,
  `town` varchar(255) NOT NULL,
  `county` varchar(255) NOT NULL,
  `postcode` varchar(8) NOT NULL,
  `homephone` varchar(15) NOT NULL,
  PRIMARY KEY  (`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
Where have I gone wrong?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Use either single (') or double (") quotes to quote values in sql queries. Backticks (`) are used to quote field and table names, not values.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Can't find what I have done wrong

Post by timvw »

[quote="andym01480"]

Code: Select all

INSERT INTO address (add1, add2, town, county, postcode, homephone) VALUES (`streetname`,`areaname`,`town`,`countyname`,`B21 5FE`,``)
Couldn't store addressUnknown column 'streetname' in 'field list'
In MySQL you can escape column names with ``.. but the values need to be between ''.

Thus:

Code: Select all

INSERT INTO address
(
 add1,
 add2,
 ...
)
VALUES
(
 'streetname',
 'areaname',
 ...
);
(Off-topic: 'clean' seems like a vague name for variables that have been prepared for use in a query... $mysql['add1'] seems more appropriate, but that might be a matter of subjective taste...)
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Post by andym01480 »

Thanks for the assist.

And yes $clean was a silly choice especially as it is filtered and escaped for mysql!
Post Reply