PHP Update query : Unknown column in 'field list'

Question

Need help.

I'm having hard time trying to update the database with PHP. It keeps on giving me "Unknown column in 'field list'" error. I have run the same command through PHPMyadmin and it is successfully updating the data.

Below is the table structure:

CREATE TABLE `user` (
  `uid`              int AUTO_INCREMENT NOT NULL,
  `name`            text,
  `photo_localurl`  text,
  `birthday`        text,
  `nickname`        text,
  `height`          text,
  `lastupdate`      timestamp,
  PRIMARY KEY (`uid`)
) ENGINE = InnoDB;

if I insert with back-tick in column name

$sql = "UPDATE user SET `height` = '$height' WHERE uid = '$uid'";

i get this error

UPDATE user SET `height` = '6\' 2"/|!-!|/1.88 m' WHERE uid = '51' Unknown column 'height' in 'field list'

if i insert without back-tick

$sql = "UPDATE user SET height = '$height' WHERE uid = '$uid'";

I get this error

UPDATE user SET height = '6\' 2"/|!-!|/1.88 m' WHERE uid = '51' Unknown column 'height' in 'field list'

if I use single tick

$sql = "UPDATE user SET 'height' = $height WHERE uid = $uid";

I get this error

UPDATE user SET 'height' = 6\' 2"/|!-!|/1.88 m WHERE uid = 51 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 ''height' = 6\' 2"/|!-!|/1.88 m WHERE uid = 51' at line 1

Have you tried putting backticks around the tablename, user? It's not a reserved keyword in MySQL I think, but it is in some other SQL based systems, can't hurt to try anyway :) — fivedigit
– fivedigit, Commented Jan 14, 2012 at 10:27
Can you check that show create table user; is like your create table statement. — greut
– greut, Commented Jan 14, 2012 at 10:28
Im not creating table on runtime. I just showed that it is my table structure. — Bhaskar Wankhede
– Bhaskar Wankhede, Commented Jan 14, 2012 at 11:26

Ayman Safadi · Accepted Answer · 2012-01-14 10:27:56Z

2

An example of a height value could be 6'2". When you insert it into the query string ($sql), it ends up looking something like:

$sql = "UPDATE user SET height = '6'2"' WHERE uid = '8'";

You see how this messes up your string right? It's screwing up your quotes.

Try using PHP's mysql_real_escape_string() function:

$sql = "UPDATE user SET height = '" . mysql_real_escape_string($height) . "' WHERE uid = '$uid'";

answered Jan 14, 2012 at 10:27

Ayman Safadi

11.6k1 gold badge30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

fivedigit Over a year ago

addslashes() should never be used for this. Instead, mysql_real_escape_string() is the better solution (php.net/manual/en/function.mysql-real-escape-string.php). If magic quotes are on, stripslashes() should be called first before doing the mysql escape.

Bhaskar Wankhede Over a year ago

value for $height is already coming through mysql_real_escape_string()

Ayman Safadi Over a year ago

echo $sql; What do you get?

Bhaskar Wankhede Over a year ago

I think where the problem is. $height value is an array and implode function is used. $height = mysql_real_escape_string(implode("/|!-!|/", $height)); If I echo this out i get "6\' 2"/|!-!|/1.88 m" without implode its output is "Array ( [imperial] => 6' 2" [metric] => 1.88 m )".

Bhaskar Wankhede Over a year ago

If I hard code the value of height I'm still getting the same error.

Pradeep Singh · Accepted Answer · 2012-01-14 10:42:00Z

0

use mysql_real_escape_string

  $sql = "UPDATE `user`.`userit` SET `userit`.`height` = '" . mysql_real_escape_string($height) . "' WHERE uid = '$uid'";

edited Jan 14, 2012 at 10:42

answered Jan 14, 2012 at 10:35

Pradeep Singh

3,6323 gold badges33 silver badges42 bronze badges

1 Comment

Bhaskar Wankhede Over a year ago

Sorry guys this was a mistake from myside. I never looked at what DB was used. Somebody has selected different DB and I though that I was working on actual DB. It has almost same structure except few fields are missing. I'm really very embarrassed after finding that. I think I need a break. Time to go for a beer.

Collectives™ on Stack Overflow

PHP Update query : Unknown column in 'field list'

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related