INSERT INTO MySQL and PHP

Question

I have a MySQL table ( members ) with six columns ( FirstName, LastName, Email, username, password, Key)

I need to insert a string into the Key column for the username admin

How would I go about doing this?

Answer 1

UPDATE members 
   SET `Key`='<SOMETHING>' 
 WHERE username='admin' 
 LIMIT 1

You should have a primary key, like member_id (usually an INT with auto_increment). Then you can do:

UPDATE members 
   SET `Key`='<SOMETHING>' 
 WHERE member_id=<X>

Answer 2

Suppose the string you want to insert is the literal string 'MyString'. You could update the table like this:

UPDATE `members` SET `Key` = 'MyString' WHERE `username` = 'admin'

Some things to note:

• I've enclosed the column names, such as Key, in identifier quotes (backticks). This is mainly because I know that MySQL may treat the word 'key' as an SQL reserved word, which might result in it failing to parse my UPDATE query.
• This assumes that the column Key has a string datatype, such as TEXT or CHAR(20). If it doesn't (for example if it's an INT), then it doesn't make sense to try to update it to contain a string.
• I also assume that the column length, if it's a CHAR or VARCHAR, is long enough to contain this string (i.e. it's of length at least 8). If the column is too short to contain the string I'm trying to put in it, then MySQL will either truncate the string or issue an error message, depending on the SQL mode.

Alternatively, if there isn't a row in the table for which username is 'admin', but you want to insert one, you use

INSERT INTO `members` (`username`, `key`) VALUES ('admin', 'MyString')

This statement is subject to the same provisos as the one above. Also, any columns that are NOT NULL will need to have values specified as well (with the possible exception of an auto-incrementing integer field).

Answer 3

If you don't know whether or not that user exists use ON DUPLICATE... http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html