homepage Welcome to WebmasterWorld Guest from 54.205.105.23
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

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



 
Msg#: 4351193 posted 5:08 pm on Aug 13, 2011 (gmt 0)

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

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4351193 posted 5:21 pm on Aug 13, 2011 (gmt 0)

Try

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

Jamier101



 
Msg#: 4351193 posted 6:00 pm on Aug 13, 2011 (gmt 0)

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

trillianjedi

WebmasterWorld Senior Member trillianjedi us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4351193 posted 11:59 pm on Aug 13, 2011 (gmt 0)

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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4351193 posted 5:47 pm on Aug 14, 2011 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4351193 posted 9:40 pm on Aug 14, 2011 (gmt 0)

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



 
Msg#: 4351193 posted 11:00 pm on Aug 14, 2011 (gmt 0)

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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4351193 posted 10:19 am on Aug 15, 2011 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4351193 posted 4:00 pm on Aug 15, 2011 (gmt 0)

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



 
Msg#: 4351193 posted 9:47 pm on Aug 16, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved