tracking user logins part 2!

[rattlsnak]

OK, I am using this code to insert a new row into a database everytime a user logs in so I can see who has logged in and when:$inputuser = $_SESSION['username'];$queryuser = "INSERT INTO userlogins (username) VALUES('$inputuser')";mysql_query ($queryuser);Have two fields in the database, username and a timestamp.It works fine when a user logs in for the first time. Problem I just discovered, is if that same user logs in again, it will not add another new row. I understand why it wont update the previous row as I'm using an INSERT function and not an UPDATE, but why wouldnt it just keep adding new rows everytime someone logs in, whether a repeat user or not?How do I correct this? I would prefer for it to simply add a new row everytime instead of updating the current row, or adding a count feature.Thanks..

[jlhaslip]

You need to search the Data Table for the user, if you do not find the user, then INSERT a new row with username and timestamp.If you already have that user in the Table, then UPDATE the row with the new timestamp.You are trying to insert information that is already there, so rather than Insert, Update the record.

[rattlsnak]

I follow what you are saying, but is there no way to simply add a new row everytime someone logs in, even though it is a repeat user?In other words, if the same person logs in 10 times, I would like to see ten rows of data.If not, then how would I write the code to do as you suggest? (As in searching first, and then inserting or updating as required based on what was found?)I know how to write an insert OR update script, but not sure how to write in both at the same time.Thanks, as always!

[justsomeguy]

Write one, then the other one. If you just want to keep a record of every time someone logs in then have a table that only has that information in it. Give it a normal auto-incrementing primary key, have a field for username, time, IP, URL, MAC, WTF, hair color, favorite food, mother's maiden name (you can use that to hack their other accounts), whatever you want to store, and insert a new record every time. If you also want to update their other user record that's fine, there's no rule that says how many SQL queries you're allowed to run on a page.

[rattlsnak]

OK, I have changed the table an added an auto incrementing id field, and now it works as desired. Everytime a user logs in, whether new, or repeat, it adds a new row.Now, for the basis of learning, if I wanted to simply update the existing row for a repeat user, and insert a new row for a new user, I understand I can keep using the INSERT query and add an UPDATE query. I tried that but got stuck on the timestamp portion. Here is what I have for Insert:$inputuser = $_SESSION['username'];$queryuser = "INSERT INTO userlogins (username) VALUES('$inputuser')";mysql_query ($queryuser);But how do I update the timestamp for an existing user?$queryusers = "UPDATE userlogins (username, logintime) VALUES ('$inputuser', ' ??')";mysql_query ($queryusers);And once that is working, how do i add a field that autoincrement everytime they log in?

[justsomeguy]

What is the data type of the timestamp field? Is it just a regular timestamp field? Depending on the data type, you would use one of the date/time functions.http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.htmle.g.UPDATE userlogins SET logintime=NOW() WHERE ....

[rattlsnak]

the database data type is 'timestamp' with an 'on update current_timestamp' attribute. I have no time function on the page itself.

[justsomeguy]

If there's a trigger on update then the timestamp will update automatically when you update the record. In order to make sure the record gets updated you might want a third column, like number of logins, that you actually update. When that gets updated it will also update the timestamp because of the trigger.

[rattlsnak]

I tried using the current INSERT query I have running and added an UPDATE query:$queryusers = "UPDATE userlogins (username) VALUES ('$inputuser')";mysql_query ($queryusers);But it doesnt work. It will add a new user just fine, but will not update an existing user's timestamp. Does it matter what the primary field is? I have that set to a user id field now.

[justsomeguy]

It's not going to update a record if you give it the same value it already has. That's why I suggested using a counter.