I am getting syntax error with the following statement
REPLACE INTO users (screenname, token, secret) VALUES( '$screenname', '$token', '$secret' ) WHERE 'screenname' = $screenname
The table has a primary key named id, which auto-increments.
views:
64answers:
3
A:
You need to have a UNIQUE index on screenname.
Also your quotes are wrong in the WHERE clause:
WHERE screenname = '$screenname'
I'm going to assume all your variables have been put through mysql_real_escape_string() :)
Greg
2009-10-29 09:06:31
If my id field is the primary key, how do i set the screenname field to unique? Im using phpmyadmin, and the option to set this is inactive.
Patrick
2009-10-29 09:11:26
ErrorSQL query:ALTER TABLE `users` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `screenname` )MySQL said: Documentation#1170 - BLOB/TEXT column 'screenname' used in key specification without a key length
Patrick
2009-10-29 09:13:22
+1
A:
From what i know, REPLACE has no WHERE, you probably want UPDATE instead
stereofrog
2009-10-29 09:33:49
yes, but the other way round: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
stereofrog
2009-10-29 10:12:51
No need for a UPDATE IF EXISTS, running the query will only update the matching records. If none match nothing will get updated. You can try this out by copying the data you need into a temp table like this: (Use everything between the quotes) "CREATE TABLE test_table SELECT fields_you_need FROM table_name WHERE condition_you_need LIMIT x_amount" this is just a rough way to create some test data into a table so you can run your queries in a test environment and leave production/live table data alone. Cheers
Phill Pafford
2009-10-29 14:40:44
A:
try removing both single quotes in the variables of the
values VALUES( '$screenname', '$token', '$secret' )
Eg:
values VALUES($screenname, $token, $secret )
mepo
2009-10-29 11:05:04