update statement using php mysql not working

Question

I have a table with the below values:

Table
-----------------------------------------------------------
Column               | Type           | Default
-----------------------------------------------------------
name                   varchar(100)     none
loginCount             int(11)          0
lastLoginDate          datetime         0000-00-00 00:00:00
-----------------------------------------------------------

Now, I want to check if the user is logged in properly and if yes, update loginCount and lastLoginDate correspondingly.

The php script is as below:

<?php
  // Verification query..
  if($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $sql = "UPDATE table
               SET `loginCount` = `loginCount` + 1,
                   `lastLoginDate` = now()
             WHERE `name` = '".$username"'";
    $result = mysql_query($sql);
  }
?>

The issue is, this is NOT working!

However, if I use the same by logging into mysql console directly, the update happens!

Could you please tell me what am I doing wrong here?

Thank you!

check your mysql.log file if the query is being fired and if it does what are the parameters it is sending. — coder101
– coder101, Commented Feb 15, 2013 at 3:00
And of course, echo $sql to see the query that is going to be executed. — Michael Berkowski
– Michael Berkowski, Commented Feb 15, 2013 at 3:01
and please stop using the mysql extension for php, rather start using mysqli, it is almost the same and you won't find it difficult to switch. mysql is soon going to be deprecated and php will most probably not bundle it in the next release. — coder101
– coder101, Commented Feb 15, 2013 at 3:04
@curlyreggie If $result === FALSE, then you must check echo mysql_error();. Also, you have already used $result as the resource for your first query. Even though you have done only one fetch call and it shouldn't collide, I recommend using a different variable for the second mysql_query() call to cause less confusion. — Michael Berkowski
– Michael Berkowski, Commented Feb 15, 2013 at 3:17

Michael Berkowski · Accepted Answer · 2013-02-15 12:22:56Z

2

You're missing a . for concatenation before the closing quote

WHERE `name` = '".$username"'";
//------------------------^^^

Should be

WHERE `name` = '".$username . "'";
//--------------------------^^^

Or better, omit the concatenation because you are enclosing in double quotes.

$sql = "UPDATE `table` SET ..... WHERE `name` = '$username'";

This means you probably don't have error_reporting enabled, or you would see a syntax error.

Something like:

Parse error: syntax error, unexpected '"'"' (T_CONSTANT_ENCAPSED_STRING)...

ini_set('display_errors', 1);
error_reporting(E_ALL);

Finally, it should not cause problems, I wouldn't expect based on the posted code, but it is unwise to reuse $result, which is the result resource variable from your first (unseen here) query. You will not be able to fetch again from it later if needed, once it has been overwritten by TRUE/FALSE from the UPDATE statement. Use a different variable for the UPDATE success/failure.

$other_var_not_result_for_clarity = mysql_query($sql);

edited Feb 15, 2013 at 12:22

answered Feb 15, 2013 at 3:05

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

Michael Berkowski Over a year ago

@curlyreggie Did you debug it by showing testing for true/false on $result and showing errors as suggested in the comments? If no row is updated and there is no error ($result is true), then $username does not contain what you expect it to.

Michael Berkowski Over a year ago

@curlyreggie And did you debug the first query from which you fetched $row? Did it return a row causing the fetch to enter the if block?

Collectives™ on Stack Overflow

update statement using php mysql not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related