Code isn't updating a database set but says its working

[Jamier101]

I have a line of code that I've been running from a php page and although it returns to say its successful it isn't actually updating my database.

{
// Run a query to activate the account
$activate = mysql_query("UPDATE users SET activated='1' WHERE id='$id'");
die("Your account was successfully activated");
}
else
die("Invalid ID or actication code!");

I decided to run the code through mySQL console as:

UPDATE users SET activated='1' WHERE id='17';

17 being an id number of an actual database entry.

Running it manually it works and updates the database, the php script in my eyes is also correct, any ideas?

[brotherhood of LAN]

Try

mysql_query("UPDATE users SET activated='1' WHERE id='$id'") or die(mysql_error());

[Jamier101]

The page echo's back that the update was successful but it just doesn't update.

[trillianjedi]

It will return true because the query successfully executed. That doesn't mean that it had any effect on the database, necessarily.

Best guess is $id is not what you think it is in the code. I would add this:-

die("Your account of ID : $id was successfully activated");

.... and see what $id actually is at this point in code.

[penders]

// Run a query to activate the account
$activate = mysql_query("UPDATE users SET activated='1' WHERE id='$id'");
die("Your account was successfully activated");

Because this code does not check the success of your SQL statement! It will ALWAYS output "...success...". See brotherhood of LAN's post for trapping this error. And, as trillianjedi says, $id is probably not what you think it is.

[Matthew1980]

Hi there Jamier101,

$sqlUpdate = "UPDATE `users` SET `activated` = 1 WHERE `id` = ".$id;
$activate = mysql_query($sqlUpdate);

With this code (slightly altered..) you can prefix the variable with 'echo' to see exactly how the string is being built.

Also (I see this so often it really surprises me) the int's that you're using need to be UNQUOTED as sql will treat this as them being int's then, quoting int's turns them (for sql reasons I can't recall right now..) into strings type. This will play havoc in your overall DB design.

Also the statement that you're enclosing this all in needs to be clearer for logic/assessment reasons - place the update variable in an if statement to evaluate the outcome of this action. Because at the moment, this will always say update successful because of the procedural nature of your code.

Try to refine your code with the revised SQL part that I have posted (treating the vars as int's).

Hope that this makes sense.

Cheers,
Mrb

[Jamier101]

When I added the code of the ID check:

die("Your account of ID : $id was successfully activated");

I got back the correct answer:

<b>Your account of ID : 17 was successfully activated</b>

I'm going to try Matthew's suggestion and see how that fairs as none of this is making sense since in my mind it should just work :-s

[penders]

Matthew1980: Also (I see this so often it really surprises me) the int's that you're using need to be UNQUOTED as sql will treat this as them being int's then, quoting int's turns them (for sql reasons I can't recall right now..) into strings type. This will play havoc in your overall DB design.

MySQL should implicitly cast the string to an integer (if indeed the field is an integer? ;). I would tend to agree, however, don't quote integer field values. Efficiency, ability to use indices, etc. stated as reasons not to quote integer field values.

HOWEVER, from the MySQL manual page on Security Guidelines [dev.mysql.com]...

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.

[rocknbil]

Interesting comment from the manual . . . better yet is to cleanse it before you even get to the query.

Yes, it will cast it as an integer (or other numeric type) if the data type is numeric but if you do this

UPDATE users SET activated='1' WHERE id=''

or this

UPDATE users SET activated='1' WHERE id='oops'

it will be a zero . . . hence the query will run but it won't error. This will throw a mysql error if it's not a number

UPDATE users SET activated='1' WHERE id=$id

Which is (kind of) a built in way to error trap if you use query or die.

[Jamier101]

Hi guys,

I don't know what to say, I re-wrote my code from scratch and for some reason this time it worked a treat :-S