PHP/MySQL Update code [closed]

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

"Fails out" with what error? Are you sure there's a record with the ID you're passing in? — RichieHindle
– RichieHindle, Commented May 3, 2009 at 18:16
What error are you getting? Change the code to this: $result = mysql_query(...) or die("Error: ".mysql_error()); — Chris Bartow
– Chris Bartow, Commented May 3, 2009 at 18:25

Chris Bartow · Accepted Answer · 2009-05-03 18:28:24Z

7

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

answered May 3, 2009 at 18:28

Chris Bartow

15.2k11 gold badges45 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

barfoon Over a year ago

You should really sanitize your input here to prevent injection, either with prepared statements or escaping the values.

Philip Whitehouse Over a year ago

It's easiest if you always put `s around all the fields as standard practice to be honest. There's a ton of reserved words and some are really good field names (desc is quite common).

Darryl Hein · Accepted Answer · 2009-05-03 18:32:27Z

1

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.

answered May 3, 2009 at 18:32

Darryl Hein

145k96 gold badges224 silver badges265 bronze badges

Comments

Nael El Shawwa · Accepted Answer · 2009-05-03 18:53:28Z

1

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.

answered May 3, 2009 at 18:53

Nael El Shawwa

3481 silver badge10 bronze badges

Comments

ahawker · Accepted Answer · 2009-05-03 18:32:12Z

-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.

edited May 3, 2009 at 18:32

answered May 3, 2009 at 18:18

ahawker

3,39426 silver badges23 bronze badges

1 Comment

The Digital Ninja Over a year ago

just that part that I copied.

Srikar Doddi · Accepted Answer · 2009-05-03 18:25:27Z

-2

try this out

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

answered May 3, 2009 at 18:25

Srikar Doddi

15.6k15 gold badges70 silver badges107 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Update code [closed]

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Linked

Related