php mysql insert/update field name show

Question

I have a problem in my DB there is a fieldname 'show' in I have a php script that updates table or insert and everything is working to the field name 'show' even if I change it to 'showz' it works! I think it is a glitch here is a part of the php code!

mysql_query("INSERT INTO db.table (mid, name, show) VALUES ('".$row['mid']."',     '".$row['name']."', '".$row['show']."') ON DUPLICATE KEY UPDATE name = '".$row['name']."',  show = '".$row['show']."'");

I tried to put the show thing in 'show' , show , "show" nothing helps!

You said you tried to put show into backticks `show`, which would be correct, as you can see in all three answers. Are you sure, you put both occurences of show into backticks? — Michel Feldheim
– Michel Feldheim, Commented Dec 20, 2012 at 14:39

Mark Baker · Accepted Answer · 2012-12-20 14:33:16Z

4

AS show is a MySQL reserved word, you need to enclose it in backticks (`) if you use it as a table or column name

`show`

answered Dec 20, 2012 at 14:33

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

1 Comment

Internet caffe Over a year ago

Thanks I used that but when I copy/paste it from here it works could it have to do something with my notepad++

Stu · Accepted Answer · 2012-12-20 14:32:40Z

that's because show is a reserved word (like insert, update, order etc). You can read about reserved words here: MySQL Reserved Words

In order to use a reserved word as a field name you need to escape it with backticks (`) like so:

mysql_query("INSERT INTO db.table (mid, name, `show`) VALUES ('".$row['mid']."',     '".$row['name']."', '".$row['show']."') ON DUPLICATE KEY UPDATE name = '".$row['name']."',  `show` = '".$row['show']."'");

This tells mysql to treat it as a field rather than use it in its reserved word meaning.

I find it's good practice to always escape my table and field names with backticks like so:

mysql_query("INSERT INTO `db`.`table` (`mid`, `name`, `show`) VALUES ('".$row['mid']."',     '".$row['name']."', '".$row['show']."') ON DUPLICATE KEY UPDATE `name` = '".$row['name']."',  `show` = '".$row['show']."'");

Community · Accepted Answer · 2017-05-23 12:12:32Z

3

you are using reserved keyword use backticks

Table 9.2. Reserved Words in MySQL 5.0.96

enter image description here

so use like

`show`

rather warping all colon name in backticks is good approach

mysql_query("INSERT INTO `db`.`table` (`mid`, `name`, `show`) VALUES ('".$row['mid']."',     '".$row['name']."', '".$row['show']."') ON DUPLICATE KEY UPDATE `name` = '".$row['name']."',  `show` = '".$row['show']."'");

Note

The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. So use either PDO or MySQLi

Good read

The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Dec 20, 2012 at 14:33

NullPoiиteя

57.3k23 gold badges130 silver badges148 bronze badges

Collectives™ on Stack Overflow

php mysql insert/update field name show

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related