Log all times user logs in.

[techgearfree]

I need to log all the times a user has logged into their account. I need this to be saved in my sql database.

 Right now, it saves only the most recent time someone logs in. And, now it saves it as something like: 1106420461 Now, from what I read, this is the # of seconds since some epox or something. So, I really just need help with 2 things:

1.) Getting 1106420461 to show up as something that will show me the Month, Day, Year, and Time of day

2.) Getting it to keep adding times to the database Column called time for the user(not just writing the most current and erasing the old one).

This is the code I am using:

 $time = time();

$logintime = "UPDATE users SET time='$time' WHERE username='$username'";

Thanks for your help 😃

[Roger_Ramjet]

You need to create an extra table to store the data in. It will require

Primary key column autoinc
Foreign key column for primary key from users table
Login column Datetime column type

Every time the user logs in you add a record to this table. The easiest way to record the date and time is with a datetime column type where the default value is set to Now(). (this is version specific so check your mysql version and the manual). Now when you add a user's id and omit any value for the datetime column, the db engine will automatically record the current date and time. Using mysql native datetime column type will allow you to use the native date and time functions in your queries. A lot easier than messing about with unix timestamps and conversion in your code.

[Roger_Ramjet]

PS the autoinc is not strictly neccessary for this type of table as the datetime will automatically give you the sequence of insertions for order by etc. It's just habit on my part so omit it if you want.

[techgearfree]

WOW you lost me. I have a table that when a user joins, their name is in the username column, and their time goes into the time column. I just need to learn how to make the 234924892 show an actual date/ time. And to make it so that it just keeps adding times.

[Roger_Ramjet]

OK. When you say 'a user logs in', how and where do you keep their user name and password?

[techgearfree]

I have a table called users that has a row for each user and collumns like: username, password, time, etc.

In time, it gives me the time like: 13929293

That is the first problem.

Also, if a user logs in a second time, it erases the old login time, and gives a new one (the most current time is only logged).

I use this code for when they log in:

$logintime = "UPDATE users SET time='$time' WHERE username='$username'";

I want it so that it doesn't erase the old time, but just adds it.

Thanks

PS: If you want, I have AIM: spetsacdc to solve the prob. quicker

[Roger_Ramjet]

So. Create a new table called logins.

It will have 2 columns

username and login

the login column will be column type DATETIME, and for the default value it will be Now()

When the user logs in, instead of updating the users table you will insert a new record into the logins table with this query

INSERT INTO logins SET
username = '$username'

You will then be recording every login as a seperate row in the logins table. The login column will contain the date and time in this format 'yyyy-mm-dd hh:mm:ss' , which is exactly what you want. By using the default of Now() you let mysql record the current system time for you, so you do not have to include it in the query. Now() is an inbuilt mysql function that always returns the system date and time, to the second.

When you want to look at the data you simply select username and it will return all of that user's logins.

[techgearfree]

Thanks, just reading over that, it makes since. I will try it later tonight. I will probally have another question later about displaying it into the website.

Thank you

[techgearfree]

I tried making the table, but when I hit save, it says This is not a number b/c I did'nt put anything in the length/value input box. I do not know what to put in that box, the Attributes, the Null, the Default**, and the extra Extra. And should I click the insert, index, unique, or full text boxes?

Thanks

[techgearfree]

I tried fooling around with it, and might have gotten a little closer to getting it right. I will tell you exactly my settings:

In mysql, I made a table with the name of logins These are the settings: (if you can't tell, I left attributes extra and action blank. I also left default blank but it added the numbers to the datetime row.)

Field / Type / Attributes / Null / Default / Extra /Action
username / text / / No /

login / datetime / / No / 0000-00-00 00:00:00

Now, In my login.php file I have this code:

<?
define('IN_RAFS', true);
include "./sessions.php";
include 'includes/db.php';
include 'includes/mysql.php';
//include 'includes/templates.php';

$page = "Login";
$adsense = "no";
$logout = $_GET['logout'];

if ( $logout != NULL ) {
        session_unset();
        session_destroy();
        setcookie("easyiuser", "");
        setcookie("easyipass", "");
        header("Location: index.php");
}

else {

// Make the database connection.
$db = new sql_db($dbhost, $dbuser, $dbpass, $dbname, false);
if(!$db->db_connect_id)
{
   die("Could not connect to the database");
}
        $cookie = $_GET['cookie'];
        if ( $cookie == "yes" ) { 
                $username = $_COOKIE['easyiuser'];
                $password = $_COOKIE['easyipass'];
                $enpassword = $password;
        }
        else {
                $username = $_POST['username'];
                $password = $_POST['password'];
                $enpassword = $password;
        }
        $cookiepass = ($enpassword);
        $time = time();
        $autologin = $_POST['autologin'];
        if ($username != NULL && $password != NULL) {
                include("includes/db.php");
                $usercheck = mysql_query("SELECT * from users WHERE username='$username'and password='$enpassword'");
                $useractive = mysql_query("SELECT * from users WHERE username='$username'and active='1'");
                $usercorrect = mysql_fetch_array($usercheck);
                $activeuser = mysql_fetch_array($useractive);
                if ($usercorrect && $activeuser ) {
                        $_SESSION['username'] = $username;
                        if ( $autologin != NULL ) {
                                if ( $_COOKIE["easyiuser"] == NULL or $_COOKIE["easyipass"] == NULL ) {
                                        setcookie("easyiuser", $username, time()+2592000);
                                        setcookie("easyipass", $cookiepass, time()+2592000);
                                }
                                else {
                                }
                        }
                        else {
                        }
                        INSERT INTO logins SET 
username = '$username'
                        mysql_query($logintime);
                        if ( $cookie != "yes" ) {
                                header("Location: index.php?sid=$sid");
                        }
                        else {
                                header("Location: index.php?sid=$sid");
                        }
                }
                elseif ( $usercorrect && $activeuser != "1" ) {
                        $message = "Your account is not active, please activate it via the e-mail you received.<br /><br />Click <a href=\"login.php\">here</a> to go back.";
                        include 'includes/header.php';
                        $tvars = array(
                                       'message' => $message);
                        pparse('message_body');
                }
                else {
                        $message = "The username and password you entered are invalid.<br /><br />Click <a href=\"login.php\">here</a> to go back.";
                        include 'includes/header.php';
                        $tvars = array(
                                       'message' => $message);
                        pparse('message_body');
                }
        }
        else {
                include 'includes/header.php';
                pparse('login');
        }
}
include 'includes/footer.php';
?>

Note, I put in the code:

INSERT INTO logins SET 
username = '$username'

but Insert into logins set is not blue, it is regular. This might be a problem.

When I try to log in now, it give the error:

Parse error: parse error, unexpected T_STRING in /home/techgear/public_html/login.php on line 60

Thank you

[Roger_Ramjet]

As I said, you should make the default value for the login column = Now(). Then the db engine will add the correct time for you.

Are you using phpmyadmin? If so, go to the logins table and click 'Insert new row'. When the input form loads, it should be showing the current date and time in the value box for the login filed. If it is then your default is correct. If not then go to the table design page and select 'change' on the login row. Type Now() in the Default field and save the changes. Test it by adding a new record. If you just type a name in the username field and save it, and then view the new row, you should see the correct date and time. Then you are in business.

Once you have done that, come back here and tell me it is working. Then we will deal with the errors in your script.

I can see them, but I'm not going to deal with them untill the table is correct.

[techgearfree]

I am using phpmyadmin, but I do not think I am setting up the table the correct way. This is what I am doing:

1.) From main page clicking:

Create new table on database, after I have entered the name logins and the fields 2.

2.) Now it takes me to a screen With the columns :Field, Type ,Length/Values*, Attributes, Null, Default**, Extra

There are 2 rows underneath these columns in the first row, I give the field name: username , the type is text, length/ values is blank, attributes is blank, null is set to not null, default is blank, extra is blank, and the little check boxes: primary, index, unique, and full text is unchecked.

Now, the second row the field named login, the type is datetime,
length/values is blank, attributes is blank, it is not null, and default is Now() , extra is blank, and primary, index, unique, and full text is unchecked.

3.) I click save, and it gives a little box on the screen saying:

Table logins has been created.

SQL-query:
CREATE TABLE logins (
username TEXT NOT NULL ,
login DATETIME DEFAULT 'Now()' NOT NULL
);

4.) Now, when I click to see the actual table, their are the collumns like field, type, attributes, null, etc. and then, under those in rows is what I just entered except the default is no longer now(), but 0000-00-00 00:00:00

5.) Now, in another table that a friend set up, the collumns are not like field, type, attributes, but they are what I think you are telling me to make. The collumns are like username, password, etc. Then, in rows beneath them there are peoples usernames and passwords. I am pretty sure this is what you want me to do,(because on the table my friend set up, there is a Insert new row button, butnot in the one I set up) and then enter in a username , and the other collumn will give the time. However, I do not see how to do this.

Thanks (and sorry for the other topics)

[Roger_Ramjet]

OK. Now we are getting somewhere.

1 Since there is no data in the new table, phpmyadmin will not display an empty table but instead defaults back to the table properties sheet.

2 don't worry about the 0000-00-00 00:0:00 , that is just showing the layout. Since Now is constantly changing, to show an actual value would be wrong as it would be wrong after 1 second - you get me.

3 we will not worry about creating any indexes for this table now, it is ready for what you want as is.

4 The main error in your code is that you just have the query written out.

else {
                        }
                        INSERT INTO logins SET 
username = '$username'
                        mysql_query($logintime);
                        if ( $cookie != "yes" ) {
                                header("Location: index.php?sid=$sid");
                        } 

This will never work, and I would expect a parse error when you try to run the script. Now you KNOW it should be

else {
                        }
                       $logintime = "INSERT INTO logins SET 
username = '$username'";
                        mysql_query($logintime);
                        if ( $cookie != "yes" ) {
                                header("Location: index.php?sid=$sid");
                        } 

Don't worry, it's just a typo and we all make them.

Now when you login the query should run. Test it and then go to phpmyadmin, there will be an entry in the logins table.

[Roger_Ramjet]

Just a point.

I know using the syntax

mysql_query($sql);

works for action queries (insert, update, delete). But these type of queries do actually return a result, so always use

$result = mysql_query($sql);

from the manual

If you use an INSERT ... VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:

Records: 100 Duplicates: 0 Warnings: 0

So, you can check for unexpected behaviour.

Likewise for update and delete, the result returned will be TRUE or FALSE / 1 or 0, so you can tets for succes before continuing, and alwasy should test as well.

[techgearfree]

Ok, I changed the code, and logged in as a user. It added this into the table: username login
[e-mail] 0000-00-00 00:00:00

I logged in again, and it didn't erase the first, but added another! This was what I wanted! However, if I understood what you said, the now, will always show 0000000000 because that is when NOW occurs. However, I have a question:

How do I get the actual time?

Thanks, you are really helpful.

[Ceril]

Originally posted by techgearfree
Ok, I changed the code, and logged in as a user. It added this into the table: username login
[e-mail] 0000-00-00 00:00:00

I logged in again, and it didn't erase the first, but added another! This was what I wanted! However, if I understood what you said, the now, will always show 0000000000 because that is when NOW occurs. However, I have a question:

How do I get the actual time?

Thanks, you are really helpful.

The Now() command might be the trouble here...

instead also in the insert query set login=now()

should take care of this 🙂

[Roger_Ramjet]

Yep. I did not know that mysql refuses to accept a function as the default value. All the other dbs I work with do. Pain in the arse in't it? Sorry! Still we both learnt something today.

Just do it in the query as Ceril says and everything will be hunky-dory.

[techgearfree]

So, instead of replacing the code with:

else { 
                        } 
                       $logintime = "INSERT INTO logins SET  

username = '$username'"; 
                        mysql_query($logintime); 
                        if ( $cookie != "yes" ) { 
                                header("Location: index.php?sid=$sid"); 
                        }

I make it

else { 
                        } 
                                       $logintime = "INSERT INTO logins SET  

username = '$username'"; 
                                       $logintime = "INSERT INTO logins SET login=now()";
                        mysql_query($logintime); 
                        if ( $cookie != "yes" ) { 
                                header("Location: index.php?sid=$sid"); 
                        }

[techgearfree]

My code is just a little messed up. It doesn't give me a username, but the time is right: 2005-01-23 12:11:18 !!!!

Thanks, do you know how to fix my code?

[Roger_Ramjet]

$logintime = "INSERT INTO logins SET
                          username = '$username',
                          login = Now()";

One query with values for both fields. Should be hunky-dory now.

Sorry about the problem with the default value for the login time column. It's just basic best-practice to do it in the table itself when possible. Unfortunately, not being able to use a function for the default value is another of those areas where MySQL is not as complete as commercial DB engines, but then it is free so we can't complain.

One point: I did not get you to create indexes on the login table because I doubt you will query it that often. Indexes slow down inserts but speed up selects, so its lots of logins v. a few admin queries = no indexes.