PHP, MySQL User registration with email verification and login system (history) with insert date of last seen

Question

I wish to create a log in system where every time a user logs in, this activity is logged in another table. How is this possible?

I am using PHP and MySQL, I have an ok knowledge of the two and currently have a system that I have designed using various tutorials.

It is very simple as that is all I require at the moment however I may look to streamline the code at a later date by making it object oriented however this is above my skill level at this time.

The user registers with their name, username, password and email. The user is then emailed their log in details - what I would like is for the user to have to click a link in within the email lot activate their account.*1

Once the user has activated their account the user logs in with a username and password - I would like their 'activity' to be stored, i.e the date/time that they log in.*2

*1: Currently, when the user registers, they receive an email with their log in details, I would like the email to contain a link so that they can 'activate' their account. How can I implement this. I know that I should store a unique code in the database but I do not know how to send that information to the user and to check it against the database.

*2: I would like the date and time stored in a table when the user logs in, so that their 'activity history' can be checked. How do I go about this. I know that I need to create a new table called something like 'log' which merely stores the username and date, but I do not know how to populate it when the user logs in.

If anyone can help that's be more than appreciated.

@JJ I have tried a few tutorials and at the moment have a mishmash of the lot along with very crude and basic code that I have created myself. It works but is lacking the features I mentioned (email activation and log-in history). — Michael, Aug 27 '11 at 11:16
@Gumbo Yes, two things - how to send an email with an activation code and secondly, how to record the date/time of when a user logs in. — Michael, Aug 27 '11 at 11:19
@Mike: Please edit your question and add your questions to it. — Gumbo, Aug 27 '11 at 11:20
@Mike: Questions usually have a question mark at their end. :) Additionally, try to answer JJ’s question and add some information on your thoughts of how to solve this to let the readers know that you’ve actually done some mental work. — Gumbo, Aug 27 '11 at 11:27

score 4 · Accepted Answer · edited May 23 '17 at 10:33

4

here i have explained earlier how to create a login system. check this post. this might help you.

Open-source rich HTML editor

and regarding sending mails for activation. you need to append the following to the above explained code.

add an extra column activation_string to the table users. now your final table should look like.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `activation_string` varchar(50) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
);

The activation_string column will hold our activation string that is to be sent to the user's email. so that when user click on it, users account get activated. now to generate an activation string use the below function.

function generateActivationString() {
    $randomSalt = '*&(*(JHjhkjnkjn9898';
    $uniqId = uniqid(mt_rand(), true);
    return md5($randomSalt.$uniqId);
}

Now represent the user with the registration form. and while submitting the form you have to do the following.

$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string(sha1($_POST['password']));
$activationString = generateActivationString();
mysql_query("INSERT INTO users (email,password, activation_string) VALUES('$name','$password','$activationString')");

and at the same time send an email to the user with the activation string represented as a URI. check the below code on how to do it.

$email = $_POST['email'];
$message = 'http://mysite.com/activate.php?confirm='.$activationString;
$to = $email;
$subject = 'User Account Confirmation';
$body =     $message . PHP_EOL;
mail($to, $subject, $body);

and then write another script to verify the string and update the value in status to 1 if the activation string is verified.

hope this helps you in understanding what you should be doing.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 27 '11 at 11:08

Ibrahim Azhar Armar

24,538
34
126
199

I have just found userCake. I am taking a look at that at the moment, but if that isn't fit for purpose I shall take a look at your post. Thank you Ibrahim. – Michael Aug 27 '11 at 11:15
@Mike if you find this post useful, you can mark it as answered. – Ibrahim Azhar Armar Aug 27 '11 at 11:55
I get an error Parse error: syntax error, unexpected T_PROTECTED in register.php, it is this line that causes it "protected function generateActivationString() {" also, the activation code doesn't generate, it is not sent in the email nor is it stored in the database. – Michael Aug 27 '11 at 12:28
i am sorry for i have been using too much of Object oriented programming, and unintentionally added the protected keyword. i have updated the function. check now. – Ibrahim Azhar Armar Aug 27 '11 at 12:33
the activation code is still empty? Am I doing something wrong? – Michael Aug 27 '11 at 12:39
Nope, no error, the other fields are inserted, but even when I output the sql insert statement (echo it to check it) the activation code is blank? do I need to echo the activation string or is just calling the function enough as you 'returned' the string within the function? – Michael Aug 27 '11 at 12:53
call the function and assign it to variable. can you post your code so that i can have a look? – Ibrahim Azhar Armar Aug 27 '11 at 13:09
if (!get_magic_quotes_gpc()) { $username = addslashes($_POST['username']); $firstname = addslashes($_POST['firstname']); $surname = addslashes($_POST['surname']); $email = mysql_real_escape_string(addslashes($_POST['email'])); $pass = mysql_real_escape_string(sha1($_POST['pass'])); $activationString = generateActivationString(); } – Michael Aug 27 '11 at 13:16
$insert = "INSERT INTO users (username, password, firstname, surname, email, activation_string) VALUES ('".strtolower($username)."', '".$pass."', '".strtolower($firstname)."', '".strtolower($surname)."', '".strtolower($email)."', '".$activationString."')"; $add_member = mysql_query($insert); – Michael Aug 27 '11 at 13:16
how would I go about creating the activate.php page? – Michael Aug 27 '11 at 23:10
@Mike, at the time of creating the user, value in status column is set to `0`. all you need to do is in activate.php firstly check if that activation string exist and if it does then update the value of status to `1`. – Ibrahim Azhar Armar Aug 28 '11 at 11:44
How do I check that it exists? Do I query the database by the user's username and pull the activation code into a variable, compare that with the $_GET variable and then if they are the same, set the status to '1' (active) and if not, then ask to retry or something? Is this right? – Michael Aug 28 '11 at 11:45

score 1 · Answer 2 · answered Aug 27 '11 at 12:30

If you want to log the user activity (i.e. their last login date), you can just make another field in the users table and name it something like last_login or last_activity (whichever suits you). And then when they're logging in, after checking the password they entered, update the field with the current date.

if ($password === $storedpassword)
{

  // Password correct.
  $currentDate = date('Y-m-d g:i:s a');
  mysql_query('UPDATE `users` SET `last_login` = "' . $currentDate . '"');

}

PHP, MySQL User registration with email verification and login system (history) with insert date of last seen

2 Answers2