Time Limits on Updates????

jwitte

I have looked everywhere and can't figure out how to do this!!! I don't even know what it is called or how to properly term it....but...I see it done everywhere so I know it can be done...

I have an application that to give the user points for each time they log in BUT only once every two hours. I have the points working so they get points each time they log in, but currently they can basically keep refreshing and they keep getting points.

$insertSQL = "INSERT INTO player(playerID) VALUES($playerID) ON DUPLICATE KEY UPDATE points=points+5";

I am using php4

Thanks for any help!!!!

JVassie

Hi jwitte, welcome to the forums firstly, hope we can help you out a bit.

The way to do i think that would be best is used to set a cookie in the user's browser which expires after 2 hours. When they login, they check to see if the cookie is set, in which case the points arent updated, and if the cookie doesnt exist, update the points and create a new cookie.

One of the more experienced coders here might have a better solution, however i reckon this could work, only problem i forsee is people who turn cookie's off in their browser.

Piranha

As JVassie says there is an easy way to get around his solution.

Instead store the date/time when a user have logged in. When he logs in again first check against that. If it is more than 2 hours ago give him points, otherwise don't give him points.

By the way, why do you use INSERT INTO and not UPDATE?

jwitte

Piranha wrote:
By the way, why do you use INSERT INTO and not UPDATE?

I used INSERT INTO because if they are a new user it will create a new record for them, but if there is an existing record already for them (ON DUPLICATE) then instead of inserting I just have the points updating.
I am a total newbie to php and mysql and am struggling through this tiny little application I am learning with - using multiple PHP, MySQL textbooks open all over my desk, multiple IE tabs open to all different PHP tutorial sites. I know my code is not going to be the best off the bat, but that's why I am doing it!!! I have used Cold Fusion in the past, but I NEED to know PHP too and I must say CF was easier to pick up (not proficient but I can use it) then PHP..but I'll get it!

Thanks for all your help! I am sure I will have a ton more questions and I am going to try these suggestions for this problem and see how they work.

Drakla

With the insert he won't have do checking to see if a user exists because of the duplicate key clause - it's a nice lazy way of avoiding some extra code if you know what your intentions are.

Piranha

The reason I asked about INSERT INTO and UPDATE is that it seems a little bit confusing to me. I would like one script to add a user and another to login. That makes it easier to edit any script, for example if you want an admin to accept new members.

bradgrafelman

EDIT: Oops, misread the issue, sorry.

You could add a TIMESTAMP column that updates with CURRENT_TIMESTAMP every time the row is updated, and check if the user has a row in the table and if that TIMESTAMP column's time is less than a given period of time from now. This approach would cost 2 queries instead of one, though (as far as I can see).

Piranha

bradgrafelman, it is possible to do it with one query, something like

update table
set timestampcolumn = now()
where id = 1
and timestampcolumn < '2008-07-14 10:00:00'

In this example the time 2 hours ago was calculated from PHP and inserted in the query. It is possible to do it in SQL as well, but I don't know the date functions good enough to do it without looking in the manual.

Drakla, I know what the INSERT INTO...ON DUPLICATE KEY does. But I think it is better to use INSERT and UPDATE separate from each other since the code is much more easy to read. And it allows you to change the queries independentley of each other. Especially important when you are still learning the basics of coding, database queries and application design.

jwitte

RE: INSERT INTO / ON DUPLICATE
I struggled with this issue for quite a while because I had to add them if they were new but kept finding they were being duplicated with a new record if they weren't. (Its not the typical log in like that is used on this forum) and read hundreds of forum posts, and tried a ton of complicated (to me anyways) code to make it work, then I found this command combination and tried it. One simple line of code and all my troubles were taken care of! It works VERY well for me, I have tested it over and over and not once did it break on me, but it needs something to do ON DUPLICATE so I used the points.

With all the great suggestions I have gotten from you all, right now I will try creating the timestamp field within my table and also a current date field. Then I can use my INSERT INTO .. ON DUPLICATE update current date. This will give me the option of having a basic "last Visit" type thing and then I will have the code check the timestamp, if its more then 2 hours, I will UPDATE the points AND update the timestamp then too.

Cool! This should work (at least it is in my head). I will come back once I have played with it and let you all know!

Roger_Ramjet

FYI the mysql function for the current date-time is Now(). You can also use the synonym CURRENT_TIMESTAMP(), but Now() is less typing.

If you want to do it all in the query then:

INSERT INTO player(playerID, timecheck) VALUES($playerID, Now()) ON DUPLICATE KEY UPDATE points = IF(TIMESTAMPDIFF(HOUR,timecheck,Now()) >= 2, points+5, points), timecheck = Now()

[edit]sorry about the typo in there[/edit]

PS Piranha, there is absolutely nothing wrong with using both REPLACE and ON DUPLICATE KEY UPDATE where they are appropriate. Indeed, from a database developer's perspective, and that is mostly what I am, using 3 queries plus code to do something that can be done in 1 query is definitely the 'wrong' way.