CODEWITHSUNDEEP
phpmysqldateip
Roland
Roland

Reputation: 9711

Insert dates in MySql database table?

I'm building a login application, and I want to store the date the user registered and the last login date as well as the last login ip.

When creating the table what type should I give each of these three columns?

How would I proceed when I insert a new username and other data?

Should I use PHP to get the register date or should I use mysql's current date?

For the last login date and ip, what way do I do it?

Upvotes: 1

Views: 1735

Answers (3)

konsolenfreddy
konsolenfreddy

Reputation: 9671

For createdDate and lastLoginDate use datetime fields, for the IP Address an int field and insert the data via PHP's ip2long() function, see PHP.net.

To retrieve the data, you can do it on MySQL level:

SELECT INET_NTOA(ip) FROM 'log' WHERE

To get the real IP address, you want to check for proxies as well:

if (!empty($_SERVER['HTTP_CLIENT_IP'])){
  $ip=$_SERVER['HTTP_CLIENT_IP'];
//Is it a proxy address
}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
  $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
  $ip=$_SERVER['REMOTE_ADDR'];
}

Upvotes: 1

webbiedave
webbiedave

Reputation: 48887

When creating the table what type should I give each of these three columns?

make create_date and last_login_date both DATETIME's and last_login_ip a VARCHAR

Should I use PHP to get the register date or should I use mysql's current date?

This doesn't matter unless you are required to not use MySQL specific functions.

For the last login date and ip, what way do I do it?

When a user logs in, run an update statement that fills last_login_date with the current timestamp and last_login_ip with the contents of $_SERVER['REMOTE_ADDR'].

Upvotes: 1

Shyju
Shyju

Reputation: 218812

CreatedDate    : datetime
LastLoginDate  : datetime
LastLoginIp    : varchar(20)

Check this link to see how to get ip of the user. http://www.plus2net.com/php_tutorial/php_ip.php

When the user tries to login , you check the username and password against a database and if the user is valid get the user IP and update your table to have this ip address as the value for "LastLoginIp" column.

Upvotes: 1

Related Questions