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, Dec 20 '12 at 14:39

score 4 · Answer 1 · answered Dec 20 '12 at 14:32

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']."'");

score 4 · Accepted Answer · answered Dec 20 '12 at 14:33

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 '12 at 14:33

Mark Baker

209,507
32
346
385

Thanks I used that but when I copy/paste it from here it works could it have to do something with my notepad++ – Internet caffe Dec 20 '12 at 14:49

score 3 · Answer 3 · edited May 23 '17 at 12:12

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

php mysql insert/update field name show

3 Answers3