I currently have a login system, how can I put the date and time of the user’s last login into the database where the user information is kept.
Thanks
Add it to the code! Seriously we can’t help you without your current code and db structure.
This is the PHP Code:
[php]<?php
include_once ‘…/php/core/init.php’;
include_once ‘…/includes/head.php’;
logged_in_redirect();
if (empty($_POST) === false) {
$required_fields = array(‘username’, ‘first_name’, ‘last_name’, ‘email’, ‘email_again’, ‘password’);
foreach($_POST as $key=>$value) {
if (empty($value) && in_array($key, $required_fields) === true) {
$errors[] = ‘You must fill in all of the fields’;
break 1;
}
}
$username = strtolower($username);
if (empty($errors) === true) {
if (user_exists($_POST['username']) === true) {
$errors[] = 'Sorry, the username \'' . $_POST['username'] . '\' is already taken.';
}
if (preg_match("/\\s/", $_POST['username']) == true) {
$errors[] = 'Your username must not contain any spaces.';
}
if (preg_match("/\\s/", $_POST['first_name']) == true) {
$errors[] = 'Your first name must not contain any spaces.';
}
if (preg_match("/\\s/", $_POST['last_name']) == true) {
$errors[] = 'Your last name must not contain any spaces.';
}
if (strlen($_POST['password']) < 6) {
$errors[] = 'Your password must be at least 6 characters long. Please try another.';
}
if ($_POST['email'] !== $_POST['email_again']) {
$errors[] = 'Your email addresses do not match. Please try again.';
}
if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL) === false) {
$errors[] = 'Please enter a valid email address.';
}
if (email_exists($_POST['email']) === true) {
$errors[] = 'Sorry, it looks like the email ' . $_POST['email'] . ' belongs to an existing account.';
}
if (empty($_POST['sex']) === true) {
$errors[] = 'Please select either Male or Female.';
}
if ($_POST['birthday_day'] === '0') {
$errors[] = 'You must indicate your full date of birth to register.';
}
if ($_POST['birthday_month'] === '0') {
$errors[] = 'You must indicate your full date of birth to register.';
}
if ($_POST['birthday_year'] === '0') {
$errors[] = 'You must indicate your full date of birth to register.';
}
if ($_POST['first_name'] === 'Justin' && $_POST['last_name'] === 'Bieber') {
$errors[] = 'The name \'Justin Bieber\' is not allowed.';
}
}
}
?>
Create Account - Justin’s Beliebers
<div id="content">
<div id="main" style="width: 558px;" class="lfloat">
<h1>Create your Justin's Beliebers Account</h1>
<p>
Creating an account for Justin's Beliebers has many amazing benefits, it allows<br />you to connect with other fans around the world! You can also:
</p>
<ul>
<li>Join competitions and contests to win amazing prizes!</li>
<li>View exclusive photos and videos of Justin Bieber!</li>
<li>Share your Justin Bieber experience with all your friends!</li>
<li>...and lots more!</li>
</ul>
<img src="/images/pages/create_your_account.png" width="400" alt="Justin Bieber" />
</div>
<div class="purpleBox rfloat ffa fsl">
<?php
if (empty($_POST) === false && empty($errors) === true) {
$register_data = array(
'username' => strtolower($_POST['username']),
'first_name' => ucwords($_POST['first_name']),
'last_name' => ucwords($_POST['last_name']),
'email' => strtolower($_POST['email']),
'email_code' => md5($_POST['username'] + microtime()),
'password' => $_POST['password'],
'birthday_day' => $_POST['birthday_day'],
'birthday_month' => $_POST['birthday_month'],
'birthday_year' => $_POST['birthday_year'],
'sex' => $_POST['sex']
);
register_user($register_data);
echo '<p class="success">You\'ve been registered successfully! Please check your email to activate your account.</p>';
} else if (empty($errors) === false) {
echo output_errors($errors);
}
?>
<form action="" method="post">
<table>
<tr>
<td>Username:</td>
<td class="plm"><input type="text" size="30" name="username" class="inputTypeText" <?php if (isset($_POST['username']) === true) { echo 'value="', strip_tags($_POST['username']),'"'; } ?>></td>
</tr>
<tr>
<td>First Name:</td>
<td class="plm"><input type="text" size="30" name="first_name" class="inputTypeText" <?php if (isset($_POST['first_name']) === true) { echo 'value="', strip_tags($_POST['first_name']),'"'; } ?>></td>
</tr>
<tr>
<td>Last Name:</td>
<td class="plm"><input type="text" size="30" name="last_name" class="inputTypeText" <?php if (isset($_POST['last_name']) === true) { echo 'value="', strip_tags($_POST['last_name']),'"'; } ?>></td>
</tr>
<tr>
<td>Your email address:</td>
<td class="plm"><input type="text" size="30" name="email" class="inputTypeText" <?php if (isset($_POST['email']) === true) { echo 'value="', strip_tags($_POST['email']),'"'; } ?>></td>
</tr>
<tr>
<td>Reenter email address:</td>
<td class="plm"><input type="text" size="30" name="email_again" class="inputTypeText" autocomplete="off"></td>
</tr>
<tr>
<td>New Password:</td>
<td class="plm"><input type="password" size="30" name="password" class="inputTypeText"></td>
</tr>
<tr>
<td>Birthday:</td>
<td class="plm pvs"><select name="birthday_month" id="month">
<option value="0" selected>Month</option>
<option value="1">Jan</option>
<option value="2">Feb</option>
<option value="3">Mar</option>
<option value="4">Apr</option>
<option value="5">May</option>
<option value="6">Jun</option>
<option value="7">Jul</option>
<option value="8">Aug</option>
<option value="9">Sep</option>
<option value="10">Oct</option>
<option value="11">Nov</option>
<option value="12">Dec</option>
</select>
<select name="birthday_day" id="day">
<option value="0" selected>Day</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<select name="birthday_year" id="year">
<option value="0" selected>Year</option>
<option value="2013">2013</option>
<option value="2012">2012</option>
<option value="2011">2011</option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</option>
<option value="1962">1962</option>
<option value="1961">1961</option>
<option value="1960">1960</option>
<option value="1959">1959</option>
<option value="1958">1958</option>
<option value="1957">1957</option>
<option value="1956">1956</option>
<option value="1955">1955</option>
<option value="1954">1954</option>
<option value="1953">1953</option>
<option value="1952">1952</option>
<option value="1951">1951</option>
<option value="1950">1950</option>
<option value="1949">1949</option>
<option value="1948">1948</option>
<option value="1947">1947</option>
<option value="1946">1946</option>
<option value="1945">1945</option>
<option value="1944">1944</option>
<option value="1943">1943</option>
<option value="1942">1942</option>
<option value="1941">1941</option>
<option value="1940">1940</option>
<option value="1939">1939</option>
<option value="1938">1938</option>
<option value="1937">1937</option>
<option value="1936">1936</option>
<option value="1935">1935</option>
<option value="1934">1934</option>
<option value="1933">1933</option>
<option value="1932">1932</option>
<option value="1931">1931</option>
<option value="1930">1930</option>
<option value="1929">1929</option>
<option value="1928">1928</option>
<option value="1927">1927</option>
<option value="1926">1926</option>
<option value="1925">1925</option>
<option value="1924">1924</option>
<option value="1923">1923</option>
<option value="1922">1922</option>
<option value="1921">1921</option>
<option value="1920">1920</option>
<option value="1919">1919</option>
<option value="1918">1918</option>
<option value="1917">1917</option>
<option value="1916">1916</option>
<option value="1915">1915</option>
<option value="1914">1914</option>
<option value="1913">1913</option>
<option value="1912">1912</option>
<option value="1911">1911</option>
<option value="1910">1910</option>
<option value="1909">1909</option>
<option value="1908">1908</option>
<option value="1907">1907</option>
<option value="1906">1906</option>
<option value="1905">1905</option>
</select></td>
</tr>
<tr>
<td></td>
<td class="plm pvs">
<input type="radio" value="1" name="sex"> <span class="prl">Female</span>
<input type="radio" value="2" name="sex"> <span>Male</span>
</td>
</tr>
<tr>
<td></td>
<td class="plm"><input type="submit" value="Create Account" class="button mediumButton purpleButton ffa fsl fcw mrm mtl rfloat"></td>
</tr>
</table>
</form>
</div>
<?php include_once '../includes/advertisement.php'; ?>
</div>
<div class="clear"></div>
That is the register code, not the login code.
Apologies, here it is:
[php]<?php
include_once ‘…/php/core/init.php’;
include_once ‘…/includes/head.php’;
logged_in_redirect();
if (empty($_POST) === false) {
$username = $_POST[‘username’];
$password = $_POST[‘password’];
if (empty($username) === true || empty($password) === true) {
$errors[] = 'The username you entered does not belong to any account.';
} else if (user_exists($username) === false) {
$errors[] = 'The username you entered does not belong to any account.';
} else if (user_active($username) === false) {
$errors[] = 'You have not activated your account. Please check the email address you used to create your account.';
} else {
$login = login($username, $password);
if ($login === false) {
$errors[] = 'Incorrect username/password combination, Justin\'s Beliebers passwords are case sensitive.';
} else {
$_SESSION['user_id'] = $login;
header('Location: /');
exit();
}
}
}
?>
Sign in - Justin’s Beliebers
<div id="content">
<div id="main" style="width: 558px;" class="lfloat">
<h1>Sign in to Justin's Beliebers</h1>
<p>
<b>Join the largest Justin Bieber community!</b><br /><br />Get full access to Justin's Beliebers with your account:
</p>
<ul>
<li>Join competitions and contests to win amazing prizes!</li>
<li>View exclusive photos and videos of Justin Bieber!</li>
<li>Share your Justin Bieber experience with all your friends!</li>
<li>...and lots more!</li>
</ul>
<img src="/images/pages/sign_in.png" width="400" alt="Justin Bieber" />
</div>
<div class="purpleBox rfloat ffa fsl">
<?php
if (empty($errors) === false) {
echo output_errors($errors);
}
?>
<p class="calign pts">
Sign in to Justin's Beliebers
</p>
<form action="" method="post">
<table class="mtl mll">
<tr>
<td>Username:</td>
<td class="plm"><input type="text" size="30" name="username" class="inputTypeText"></td>
</tr>
<tr>
<td>Password:</td>
<td class="plm"><input type="password" size="30" name="password" class="inputTypeText"></td>
</tr>
<tr>
<td></td>
<td class="plm"><input type="submit" value="Sign in" class="button mediumButton purpleButton ffa fsl fcw lfloat mll mtm"></td>
</tr>
<tr>
<td></td>
<td class="plm ptm"><a href="/sign-in/account-recovery/">Forgotten your password?</a></td>
</tr>
</table>
</form>
<p class="calign ptl pbm mtl" style="border-top: 1px solid #bcb1ff;">
Don't have an account?<br /><a href="/create-account/"><b>Sign up for Justin's Beliebers!</b></a>
</p>
</div>
<?php include_once '../includes/advertisement.php'; ?>
</div>
<div class="clear"></div>
Well you still didn’t provide the code that actually logs the session for the user. We need the login() code and any other relevant code for the login functions.
I have these 2 functions files:
General:
[php]<?php
function email($to, $subject, $body) {
mail($to, $subject, $body, ‘From: [email protected]’);
}
function logged_in_redirect() {
if (logged_in() === true) {
header(‘Location: /’);
exit();
}
}
function protect_page() {
if (logged_in() === false) {
header(‘Location: p.php’);
exit();
}
}
function admin_protect() {
global $user_data;
if (has_access($user_data[‘user_id’], 1) === false) {
header(‘Location: /’);
exit();
}
}
function array_sanitize(&$item) {
$item = htmlentities(strip_tags(mysql_real_escape_string($item)));
}
function sanitize($data) {
return htmlentities(strip_tags(mysql_real_escape_string($data)));
}
function output_errors($errors) {
return ‘
- ’ . implode(’
- ’, $errors) . ‘
}
?>[/php]
Users:
[php]<?php
function change_profile_image($user_id, $file_temp, $file_extn) {
$file_path = ‘images/profile/’ . substr(md5(time()), 0, 10) . ‘.’ . $file_extn;
move_uploaded_file($file_temp, $file_path);
mysql_query(“UPDATE users SET profile = '” . mysql_real_escape_string($file_path) . "’ WHERE user_id = " . (int)$user_id);
}
function mail_users($subject, $body) {
$query = mysql_query(“SELECT email, first_name FROM users WHERE allow_email = 1”);
while (($row = mysql_fetch_assoc($query)) !== false) {
email($row[‘email’], $subject, "Hello " . $row[‘first_name’] . “,\n\n” . $body);
}
}
function has_access($user_id, $type) {
$user_id = (int)$user_id;
$type = (int)$type;
return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}
function recover($mode, $email) {
$mode = sanitize($mode);
$email = sanitize($email);
$user_data = user_data(user_id_from_email($email), 'user_id', 'first_name', 'username');
if ($mode == 'username') {
email($email, 'Your username', "Hello " . $user_data['first_name'] . ",\n\nYour username is: " . $user_data['username'] . "\n\n-phpacademy");
} else if ($mode == 'password') {
$generated_password = substr(md5(rand(999, 999999)), 0, 8);
change_password($user_data['user_id'], $generated_password);
update_user($user_data['user_id'], array('password_recover' => '1'));
email($email, 'Your password recovery', "Hello " . $user_data['first_name'] . ",\n\nYour new password is: " . $generated_password . "\n\n-phpacademy");
}
}
function update_user($user_id, $update_data) {
$update = array();
array_walk($update_data, ‘array_sanitize’);
foreach($update_data as $field=>$data) {
$update[] = '`' . $field . '` = \'' . $data . '\'';
}
mysql_query("UPDATE `users` SET " . implode(', ', $update) . " WHERE `user_id` = $user_id");
}
function activate($email, $email_code) {
$email = mysql_real_escape_string($email);
$email_code = mysql_real_escape_string($email_code);
if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
return true;
} else {
return false;
}
}
function change_password($user_id, $password) {
$user_id = (int)$user_id;
$password = md5($password);
mysql_query("UPDATE `users` SET `password` = '$password', `password_recover` = 0 WHERE `user_id` = $user_id");
}
function register_user($register_data) {
array_walk($register_data, ‘array_sanitize’);
$register_data[‘password’] = md5($register_data[‘password’]);
$fields = '`' . implode('`, `', array_keys($register_data)) . '`';
$data = '\'' . implode('\', \'', $register_data) . '\'';
mysql_query("INSERT INTO `users` ($fields) VALUES ($data)");
email($register_data['email'], 'Activate your Justin\'s Beliebers account', "Hey " . $register_data['first_name'] . ",\n\nTo enjoy the amazing Belieber experience on Justin's Beliebers, you must activate your account. To do this just click the link below and your account will be activated! :\n\nhttp://www.justinsbeliebers.com/activate.php?email=" . $register_data['email'] . "&email_code=" . $register_data['email_code'] . "\n\n See you soon!\n- Justin's Beliebers");
}
function user_count() {
return mysql_result(mysql_query(“SELECT COUNT(user_id) FROM users WHERE active = 1”), 0);
}
function user_data($user_id) {
$data = array();
$user_id = (int)$user_id;
$func_num_args = func_num_args();
$func_get_args = func_get_args();
if ($func_num_args > 1) {
unset($func_get_args[0]);
$fields = '`' . implode('`, `', $func_get_args) . '`';
$data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM `users` WHERE `user_id` = $user_id"));
return $data;
}
}
function logged_in() {
return (isset($_SESSION[‘user_id’])) ? true : false;
}
function user_exists($username) {
$username = sanitize($username);
return (mysql_result(mysql_query(“SELECT COUNT(user_id) FROM users WHERE username = ‘$username’”), 0) == 1) ? true : false;
}
function email_exists($email) {
$email = sanitize($email);
return (mysql_result(mysql_query(“SELECT COUNT(user_id) FROM users WHERE email = ‘$email’”), 0) == 1) ? true : false;
}
function user_active($username) {
$username = sanitize($username);
return (mysql_result(mysql_query(“SELECT COUNT(user_id) FROM users WHERE username = ‘$username’ AND active = 1”), 0) == 1) ? true : false;
}
function user_id_from_username($username) {
$username = sanitize($username);
return mysql_result(mysql_query(“SELECT user_id FROM users WHERE username = ‘$username’”), 0, ‘user_id’);
}
function user_id_from_email($email) {
$email = sanitize($email);
return mysql_result(mysql_query(“SELECT user_id FROM users WHERE email = ‘$email’”), 0, ‘user_id’);
}
function login($username, $password) {
$user_id = user_id_from_username($username);
$username = sanitize($username);
$password = md5($password);
return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) == 1) ? $user_id : false;
}
?>[/php]
Ok understanding how your login system works now, add a field to the db where you want and to store the date/time and set it to int(11) cause you will use a timestamp integer. I prefer a timestamp cause it offers the most flexibility of manipulation for other things later. Then do something like this in the else{} where you set the session user id.
[php]
$now = time();
mysql_query(“UPDATE table SET field = $now”);
$_SESSION[‘user_id’] = $login;
header(‘Location: /’);
exit();
[/php]
obviously change the table name to the table you are storing the info in and change the field name to the field in the table you are storing in. So depending on what you are wanting to do with the logged in time, you could convert it to a readable format using the date()
I’ve made a field called “last_login” and it’s an int(11) like you said. I’m just confused on where you want me to put the code you gave.
I simply prepended to the code you already had, so here is a bigger snippet for you to reference
[php]
$login = login($username, $password);
if ($login === false) {
$errors[] = ‘Incorrect username/password combination, Justin’s Beliebers passwords are case sensitive.’;
} else {
$now = time();
mysql_query(“UPDATE table SET field = $now”);
$_SESSION[‘user_id’] = $login;
header(‘Location: /’);
exit();
}
[/php]
That code didn’t work, the last_login field just stayed “0”.
Post the code as you have it now with my additions.
[php]<?php
include_once ‘…/php/core/init.php’;
include_once ‘…/includes/head.php’;
logged_in_redirect();
if (empty($_POST) === false) {
$username = $_POST[‘username’];
$password = $_POST[‘password’];
if (empty($username) === true || empty($password) === true) {
$errors[] = 'The username you entered does not belong to any account.';
} else if (user_exists($username) === false) {
$errors[] = 'The username you entered does not belong to any account.';
} else if (user_active($username) === false) {
$errors[] = 'You have not activated your account. Please check the email address you used to create your account.';
} else {
$login = login($username, $password);
if ($login === false) {
$errors[] = 'Incorrect username/password combination, Justin\'s Beliebers passwords are case sensitive.';
} else {
$now = time();
mysql_query("UPDATE `table` SET `field` = $now");
$_SESSION['user_id'] = $login;
header('Location: /');
exit();
}
}
}
?>[/php]
I know I haven’t changed the field names in the code posted above. However, even when I do it still doesn’t work.
Change the query line to this and see what error it gives you.
[php]mysql_query(“UPDATE users SET last_login = $now” WHERE user_id = $login) or die(mysql_error());[/php]
You need to give more info other than “it didn’t work”. What did it do, what errors did you get if any. You need to try and diagnose some of this yourself too. The code I gave you should work just fine if you changed out the table and field names like I said to what they should be. I have edited the code with the users table and last_login field, assuming that you put the last_login in the users table.
Ah, just figured it out, it needs a WHERE clause to set it to the correct username, I edited it above.
Ok thanks, this is pretty good. The only problem I have is that this is the code I’m using:
[php]
$last_login = date(“Y-m-d H:i:s”);
mysql_query(“UPDATE users SET last_login = $last_login WHERE user_id = $login”);
[/php]
In the database, it just shows “-1997” which is completely confusing me.
This
[php]
$last_login = date(“Y-m-d H:i:s”);[/php]
Should be this
[php]
$last_login = time();[/php]
To be able to work with the db correctly. Remember you setup the db to store an int which is a timestamp not a formatted date string. I explained the use of the timestamp earlier.
I’m slightly confused, I’d like the database field to show data which I can read easily. Example: 25-12-2013 16:04:21
Well that’s up to you, not quite as flexible but will still work, change the db structure to DATETIME instead of int and then use the date() that you had posted.
I’ve done that but it just shows as “0000-00-00 00:00:00” in the database now when I login.