Hi all,
I have a table with the attributes
user,date,count;
i have to log the number of times the user logs into the system each day

so eeverytime a user logs in, i want to call a SP which will update the respective row count by 1, if the user has logged in for the first time then the SP inserts a new row.

is there a way to do it ?

Your SP could check the number of rows in the table for that user. If it's 1, update. If it's 0, insert.

Or you could perform the update, and check the number of affected rows. If it's 0, insert a row.

thanks a lot,
did some hard googling
if not exists(select * from table as t where t.usr = @usr and t.date_logged = date(getdate())
then
insert here
else
update table as t set t.count = t.count+1 where......
end if


thanks a lot

What Db system are you using shashi?

aschk, it's sql server (you can tell by the use of the proprietary GETDATE function, although it isn't clear whether it's sybase sql server or microsoft sql server)

alternative solution: when the user logs in, insert a new row into a new table which has just two columns, userid and datetime

then you can get your counts whenever you need them by doing a COUNT query

With standard SQL, you would simply use the MERGE statement as defined in clause 14.9, <merge statement>.

Rudy : getDate is also a PHP function... so I was unclear as to whether what was being written there was PHP + SQL or pure SQL.

Just read your post on it Rudy, good idea
Obviously even if someone logs in 50 times a day then when you look them up you're only looking up 50 rows for user ID and date. Nice and quick to count