Updating users

Orc · 02-09-2008, 12:08 AM

I have made my own CMS, but if I try to update the users rows or edit the users rows, it would change every row in the column to what I put in!

Yet, I did the same procedure with making a news editing system and that turned out fine.

xenon · 02-09-2008, 12:41 AM

You forgot a WHERE clause in the UPDATE statement, I'm sure of that. Review the update query.

Orc · 02-09-2008, 12:42 AM

Quote:

Originally Posted by xenon

You forgot a WHERE clause in the UPDATE statement, I'm sure of that. Review the update query.

I have, set on there.. but the news system does perfectly, I don't understand. :/

Update: nevermind, the news system is now doing it. -_- Why is this happening.. It didn't happen before. -_-

xenon · 02-09-2008, 12:43 AM

Perhaps you could paste the query in here?

Orc · 02-09-2008, 12:45 AM

Quote:

Originally Posted by xenon

Perhaps you could paste the query in here?

PHP Code:


			
mysql_query("UPDATE `users` SET `username` = '". mysql_real_escape_string($username) . "', `password` = '". mysql_real_escape_string($password) ."', `email` = '". mysql_real_escape_string($email) ."', `access` = '". mysql_real_escape_string($access)."', `joindate` = '". mysql_real_escape_string($date)."' ") or die(mysql_error());

All those variabes are from the $_POST array.

RobertK · 02-09-2008, 01:16 AM

I see no where statement, so of course it'll set everything. You need to make it userid dependent.

Orc · 02-09-2008, 01:20 AM

Quote:

Originally Posted by RobertK

I see no where statement, so of course it'll set everything. You need to make it userid dependent.

how? this way?

PHP Code:


			
WHERE `id` = '". $_GET['id'] ."'

?

RobertK · 02-09-2008, 01:24 AM

Yeah, pretty much. I'd also combine it with a:

sql Code:

LIMIT 1

Orc · 02-09-2008, 01:28 AM

Quote:

Originally Posted by RobertK

Yeah, pretty much. I'd also combine it with a:

sql Code:

LIMIT 1

Okay, I did that, but now they won't change. -_- Do I have to add the whole thing over again, except set is where? ???

RobertK · 02-09-2008, 01:47 AM

Can you show me your current query please? LIMIT is the absolute last statement you should include.

Orc · 02-09-2008, 01:50 AM

Quote:

Originally Posted by RobertK

Yeah, pretty much. I'd also combine it with a:

sql Code:

LIMIT 1

Quote:

Originally Posted by RobertK

Can you show me your current query please? LIMIT is the absolute last statement you should include.

PHP Code:


			
                        mysql_query("UPDATE `news` SET `title` = '". mysql_real_escape_string($title) . "', `poster` = '". mysql_real_escape_string($poster) ."', `tags` = '". mysql_real_escape_string($tags) ."', `msg` = '". mysql_real_escape_string($msg)."' WHERE `id` = '". $_GET['id']."');

Salathe · 02-09-2008, 01:58 AM

It'll make things infinitely easier for you if your code is laid out nicely and easier to read. Also the clauses in the query should go UPDATE, SET, WHERE, LIMIT; in that order. The WHERE clause should catch only a single row (assuming you check against a primary key column) but just in case the LIMIT is there to make sure only one row is affected.

PHP Code:


			
mysql_query(sprintf("

    UPDATE 

        users

    SET

        username = '%s',

        password = '%s',

        email    = '%s',

        access   = '%s',

        joindate = '%s',

    WHERE

        id = %d

    LIMIT 1

    ;",

    mysql_real_escape_string($username),

    mysql_real_escape_string($password),

    mysql_real_escape_string($email),

    mysql_real_escape_string($access),

    mysql_real_escape_string($date),

    (int) $id

)) or die(mysql_error());

Orc · 02-09-2008, 02:03 AM

Quote:

Originally Posted by Salathe

It'll make things infinitely easier for you if your code is laid out nicely and easier to read. Also the clauses in the query should go UPDATE, SET, WHERE, LIMIT; in that order. The WHERE clause should catch only a single row (assuming you check against a primary key column) but just in case the LIMIT is there to make sure only one row is affected.

PHP Code:


			
mysql_query(sprintf("

    UPDATE 

        users

    SET

        username = '%s',

        password = '%s',

        email    = '%s',

        access   = '%s',

        joindate = '%s',

    WHERE

        id = %d

    LIMIT 1

    ;",

    mysql_real_escape_string($username),

    mysql_real_escape_string($password),

    mysql_real_escape_string($email),

    mysql_real_escape_string($access),

    mysql_real_escape_string($date),

    (int) $id

)) or die(mysql_error());

I'm getting this:

Code:

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 'WHERE id = 2 LIMIT 1' at line 9

Orc · 02-09-2008, 02:50 AM

I guess, nobody can help me :[

TlcAndres · 02-09-2008, 03:00 AM

Take of the last ","

Orc · 02-09-2008, 03:02 AM

Yes, I did that just a few minutes ago, but thanks for helping me anyway. :] And yes it's working successfully!