PHP/MySQL Update code

Question

I'm trying to call a row update from php to an mysql database. It fails out but when i try to call an insert new row formated just the same it works.

$result = mysql_query("INSERT INTO auth (username, password, studycode, description, server) VALUES ('$username', '$password', '$studycode', '$description', '$server')");

but this code fails

$result = mysql_query("UPDATE auth SET username='$username', password='$password', studycode='$studycode', description='$description', server='$server' WHERE index='$id' LIMIT 1;");

index is the first column and its the key/id for the table.

Edit: Ok so i just went into mysql admin and tried the exact command my code would have sent to track the error.

UPDATE auth SET username='username', password='password', studycode='ab9102y', description='test change', server='server2' WHERE index='5' LIMIT 1;

gives me the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index='5' LIMIT 1' at line 1

Answer 1

You have "UPDATE atuh" instead of "UPDATE auth". Spelling error on here or in your code?

EDIT: Try removing the quotes around your index = '$id' and also you want to remove the semi-colon from your queries in PHP.

Answer 2

try this out

$result = mysql_query("UPDATE auth SET username='$username', password='$password', studycode='$studycode', description='$description', server='$server' WHERE index='$id'");

Answer 3

Possibly a reserved keyword issue with index. Try:

$result = mysql_query("UPDATE auth SET username='$username', password='$password', studycode='$studycode', description='$description', server='$server' WHERE `index` ='$id'");

Answer 4

Judging from the error message try removing the quotes around $id. I assume it's an int of some kind.

Answer 5

I believe it's because the word index is a reserved word in MySQL. There for you need change your where clause to:

WHERE `index` = '$id'

Notice the ticks around index.

Answer 6

To avoid these kind of problems I usually name my tables like this: (for ex. a users table)

> CREATE TABLE usr_user (
>          usr_id INT,
>          usr_name VARCHAR(100),
>          usr_email VARCHAR(100) );

On a side note: Learn about prepared statements; I would be very scared of using the SQL code you have there.