[SOLVED] User Login timestamp or datetime

giraffemedia · January 16, 2008

I've got a table on mysql setup with user information such as login name, password, first name, last name etc. I also have row call last_login using the timestamp function in mysql and i'm trying to update that everytime a user log's in (or out - i'm not fussy) and i'm using sessions for the user section. I cannot get it working, does anyone have an idea how I would implement this?

Would I need to use an INSERT query on the login success page to update the last_login row where the id = '$user_id';

Regards

James

dooper3 · January 16, 2008

that's how i usually do it, yes.

giraffemedia · January 16, 2008

I'm not quite sure how to do this properly. I'm trying to set the timestamp according to the id of the person logged in but it's not working. Here is what I have so far...

<?php
//Start session
session_start();

include('../includes/config.php');
include('../includes/opendb.php');

//Sanitize the value received from login field
//to prevent SQL Injection
if(!get_magic_quotes_gpc()) {
	$login=mysql_real_escape_string($_POST['login']);
}else {
	$login=$_POST['login'];
}

//Create query
$query="SELECT member_id FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
$result=mysql_query($query);
//Check whether the query was successful or not
if($result) {
	if(mysql_num_rows($result)>0) {
		//Login Successful

		session_regenerate_id();
		$member=mysql_fetch_assoc($result);
		$_SESSION['SESS_MEMBER_ID']=$member['member_id'];
		session_write_close();

		$query="INSERT INTO members (last_visit) VALUES (CURRENT_TIMESTAMP()) WHERE login='$login' ";
			$result=mysql_query($query);


		header("location: /admin.php");
		exit();
	}

	else {
		//Login failed
		header("location: login_failed.php");
		exit();
	}
}else {
	die("Query failed");
}



?>

Anyone have an idea?

Regards

James

adam291086 · January 16, 2008

add error_reports(E_ALL); at the top of your script and dispay any errors and the line that they are on.

You should also add

or die (mysql_error()) to the end of your sql statements.

giraffemedia · January 16, 2008

I've done that now Adam (hello again) and now get the error...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE member_id ='login'' at line 1

It looks like i've not got the correct info after WHERE but i'm not sure what info this would be here. I'm presuming the data i've just passed through the login form?

adam291086 · January 16, 2008

try this

$query="SELECT member_id FROM members WHERE login='$login' AND passwd='md5($_POST['password'])'";

also echo $_POST['password'] and $login and see if there are results there

revraz · January 16, 2008

$query="UPDATE members SET last_visits = (NOW()) WHERE member_id ='".$_SESSION['SESS_MEMBER_ID'])"' ";

Try that

giraffemedia · January 16, 2008

Afraid not - blank pages.

Here is my login form which passes the login name and password (md5) to the processing form:

<?php

include (INCLUDES_DIR."config.php");

?>
<html>
<head>
<title>. : Cricket Club - Please Log-In : .</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>



<form id="loginForm" name="loginForm" method="post" action="library/login_exec.php">
  <table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
    <tr>
      <td width="112"><b>Login</b></td>
      <td width="188"><input name="login" type="text" class="textfield" id="login" /></td>
    </tr>
    <tr>
      <td><b>Password</b></td>
      <td><input name="password" type="password" class="textfield" id="password" /></td>
    </tr>
    <tr>
      <td> </td>
      <td><input type="submit" name="Submit" value="Login" /></td>
    </tr>
  </table>
</form>

			<?php
if ($errorMessage != '') {
?>
<p align="center"><strong><font color="#990000"><?php echo $errorMessage; ?></font></strong></p>
<?php
}
?>
</body>
</html>

the processing form is the one I have previously posted

revraz · January 16, 2008

Are you not logging in or is it not updating the time?

adam291086 · January 16, 2008

Its not post anything through right? so if you echo

$_POST['password'] and $_POST['login'] you get nothing

giraffemedia · January 16, 2008

Its not post anything through right? so if you echo

$_POST['password'] and $_POST['login'] you get nothing

Would this go in the processing form or the admin page once the user had been confirmed as logged in?

adam291086 · January 16, 2008

the part where you are processing the form

i.e where the form is being sent to

giraffemedia · January 16, 2008

Are you not logging in or is it not updating the time?

I think i'm logging in fine revrz, otherwise the session wouldn't work correctly would it?

giraffemedia · January 16, 2008

the part where you are processing the form

i.e where the form is being sent to

Do i need to take out the header location to stop it from redirecting me first?

giraffemedia · January 16, 2008

If I use this for the processing page I can echo the input fields:

<?php

//Start session
session_start();

include('../includes/config.php');
include('../includes/opendb.php');

//Sanitize the value received from login field


echo $_POST['login'];
echo $_POST['password'];

?>

adam291086 · January 16, 2008

so you can see the username and password

giraffemedia · January 16, 2008

Yes

adam291086 · January 16, 2008

try this then in the form processing page

<?php
//Start session
session_start();

include('../includes/config.php');
include('../includes/opendb.php');

//Sanitize the value received from login field
//to prevent SQL Injection
if(!get_magic_quotes_gpc()) {
	$login=mysql_real_escape_string($_POST['login']);
}else {
	$login=$_POST['login'];
}

//Create query
$query="SELECT member_id FROM members WHERE login='$login' AND passwd=md5('$_POST['password']')";
$result=mysql_query($query);
//Check whether the query was successful or not
if($result) {
	if(mysql_num_rows($result)>0) {
		//Login Successful

		session_regenerate_id();
		$member=mysql_fetch_assoc($result);
		$_SESSION['SESS_MEMBER_ID']=$member['member_id'];
		session_write_close();

		$query="INSERT INTO members (last_visit) VALUES (CURRENT_TIMESTAMP()) WHERE login='$login' ";
			$result=mysql_query($query or die mysql_error());


		header("location: /admin.php");
		exit();
	}

	else {
		//Login failed
		header("location: login_failed.php");
		exit();
	}
}else {
	die("Query failed");
}



?>

giraffemedia · January 16, 2008

I get a blank page Adam. If I replace the line:

	$query="SELECT member_id FROM members WHERE login='$login' AND passwd=md5('$_POST['password']')";

with:

	$query="SELECT member_id FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";

I can log in.

adam291086 · January 16, 2008

ok, well if you can login then leave it like that, has the last login in updated?

giraffemedia · January 16, 2008

No. Another blank page i'm afraid. Is it a syntax error somewhere?

This is causing the problems:

		$query="INSERT INTO members (last_visit) VALUES (CURRENT_TIMESTAMP()) WHERE login='$login' ";
			$result=mysql_query($query or die mysql_error());

If I take it out I can log in ok

adam291086 · January 16, 2008

create a variable called

$date = date('Y-m-d');

then your insert query should look like this

query="INSERT INTO members (last_visit) VALUES ('$date') WHERE login='$login' ";

$result=mysql_query($query or die mysql_error());

giraffemedia · January 16, 2008

Does it matter where on the page the variable goes?

adam291086 · January 16, 2008

put it above the insert query.

revraz · January 16, 2008

Thats because you dont want to INSERT if the member id already exists, you want to UPDATE like I showed you above.

If this is the first time they have registered, then you can INSERT when the record is created.

Sounds like you are trying to just INSERT the new time.

Sign In

[SOLVED] User Login timestamp or datetime

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information