how to setup a mysql table for use with UPDATE WHERE statement

Question

I have a table that i store userinfo. Each user has a unique userid, but the rest of their information varies.

When i try to run a statement to UPDATE that table, where userid = $userid, i get a Duplicate entry 'XXX' for key 'userid' where XXX is their userid.

I have the userid field set as unique, but obviously ive done something wrong somewhere.

Here is my statement

UPDATE `users` SET `screenname` =  '$screenname' ,`real_name` =  '$real_name' ,`profimg` =  '$profimg'  WHERE `userid` = '$userid'

Table Structure

CREATE TABLE `users` (
  `userid` int(11) NOT NULL,
  `screenname` text COLLATE utf8_unicode_ci NOT NULL,
  `real_name` text COLLATE utf8_unicode_ci NOT NULL,
  `profimg` text COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

I think you are using PHP. did you put query in double quotes like

$sql = "UPDATE foobar SET bar = '$foo' WHERE id = '$id'";

if you used single quote PHP won't replace your variables

Answer 2

First of all you should use a primary key index instead of a unique index. A primarey key index disables null entries, and although you set the collumn to NOT NULL it's better to use a PK index, since the collumn is in fact the primary key for the table.

try redefining your table like this:

CREATE TABLE `users` (
  `userid` int(11) NOT NULL,
  `screenname` text NOT NULL,
  `real_name` text NOT NULL,
  `profimg` text NOT NULL,
  PRIMARY KEY `useridKey` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You could also have a look at the AUTO_INCREMENT collumn attribute.
Also be sure to escape your variables before putting them into your query to negate sql injections...