MySQL Adding One to Numeric Column

Question

In my PHP code I have the following SQL statement:

$query = "UPDATE users SET num_logins = num_logins+1, last_login='$timeStamp' WHERE id = $id";

I recently implemented the num_logins column for a project administrator so he could see who the frequent users are. It looks like it is working most of the time, however, I've noticed two users who have num_logins = 0 with timestamp values in the last_login field that fall after the date I implemented the change.

The default for the num_logins column is zero, so if they successfully login, and the above query is the only query that updates their last_login field, how is is that num_logins is still zero when their last_login field has been updated (neither field gets updated anywhere else in the code and I am the only db admin)?

ENGINE=InnoDB.

Mysql won't update just one field and not another. The num_logins incrementation is fine - but how are you generating the timestamp variable? Could that be incorrect? Have you any more code to help diagnose this? — HappyTimeGopher
– HappyTimeGopher, Commented Apr 19, 2012 at 19:47
Have you examined the two that are different? Do they have anything in common with one another that the others (that are working) don't have? — Matt Moore
– Matt Moore, Commented Apr 19, 2012 at 19:47
is there a NULL in the original record perhaps instead of a 0... — Randy
– Randy, Commented Apr 19, 2012 at 19:51
The timestamp is showing up correctly, just num_logins is still zero. There are no NULL values in either column. And I agree. It should update both columns, not just one. And this is the only place in the code where either column gets updated. It is really blowing my mind. — A Jolly Geek
– A Jolly Geek, Commented Apr 19, 2012 at 20:04
@A Jolly Geek - Sounds like it's time to add some logging (or turn on your existing logging). — dcp
– dcp, Commented Apr 19, 2012 at 20:08

dcp · Accepted Answer · 2012-04-19 19:48:59Z

1

Some scenarios to consider:

Are you sure the $id is matching up properly on the update query? Maybe the record isn't being updated.
Are you sure all values for the num_logins are not null? Is the column nullable? Default may be 0, but you should check to make sure there aren't any null values. Remember, null + anything is always null.
Are you sure the query is actually being executed in your code? Can you add some instrumentation (e.g. logging) to ensure this? Maybe the query isn't being run in all scenarios.

answered Apr 19, 2012 at 19:48

dcp

55.7k24 gold badges149 silver badges169 bronze badges

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

3 Comments

A Jolly Geek Over a year ago

Thanks dcp for the response. As per #1, yes the id values are matching up. As per #2, there are no NULL values. As per #3, yes the query gets executed because the timestamp field is being set and this is the only place that the last_login field gets set. I'm more inclined to think along the lines of #2, but as far as I can tell, there are no NULL values in the num_logins field.

dcp Over a year ago

@A Jolly Geek - Maybe the timestamp field is being updated by some other query, not the one you suspect. Also, do you have any triggers that could be firing? That would be another possibility (not sure if MySQL has triggers or not to be honest).

A Jolly Geek Over a year ago

Yes, as octern says, mysql has triggers, but I don't have any on this table. I have enabled logging, which I have not done in years. I'll have to wait to see if another zero value shows. And there are no other places in the code that reference these fields, other than for display. I did a global search of the code base for the two fields, so I know everywhere they get used.

Collectives™ on Stack Overflow

MySQL Adding One to Numeric Column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related