...

View Full Version : Checking Amount Of Logins



tomyknoker
07-16-2007, 09:12 AM
I have the following which works fine basically it gives me the details of the last time a member in my database logged in...


mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A' WHERE ID = '".$_SESSION['mid']."'");

At the moment this just updates the last loginDateTime, I want to tally the loginDateTimes so I'm thinking the easiest way to do this would be to create another table called 'tbllogins' and then enter the Members User ID and loginDateTime... I'm just not sure what I need to put to make it enter the correct user id?

daemonkin
07-16-2007, 10:29 AM
Simply add 'memberID' to your table 'tblmembers' and run the query:



mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A', memberID = {$_SESSION['mid']} WHERE ID = '".$_SESSION['mid']."'");


This way you will not have to create a new table but all your data collected so far will have a blank field for the member ID.

Does this help?

D.

NancyJ
07-16-2007, 11:58 AM
If you're running a tally then each time will be an insert not an update, so it will get the correct member id because you told it what the member id was.

so it would look something like



mysql_query("INSERT INTO tblLogins (mid, loginDateTime) VALUES({$_SESSION['mid']}, NOW()");

tomyknoker
07-16-2007, 12:01 PM
Hey Nancy yea that's why I was thinking I would need a sepearte table because it's going to get quite large as the member's login, would you agree?

NancyJ
07-16-2007, 12:06 PM
Yes it will get huge if you have a lot of members logging in a lot, is that a problem?
If you just want to keep track of the last date they logged in and the total number of times they've logged in then you just need to add an int field to their member record and increment it each time they log in.

eg.



mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A', num_of_logins = num_of_logins +1 WHERE ID = '".$_SESSION['mid']."'");

tomyknoker
07-16-2007, 12:32 PM
ahhhhh that's kind of brilliant! i liek that idea much better... the only thing is (and tell me if this could work without having to make another table) is i want to be able to search specifically by months, say like most active member in 'June' for example... would it work like this?

daemonkin
07-16-2007, 01:05 PM
The proposed solution will only record the last time a member has logged in.

You could create a separate table for total logins and this table could be searched for specific timeframes (June etc) and you can gather information on each members log activity that way. This table will get very large though so bear it in mind.

Missed the 'update' statement rather than 'insert' in my first reply. Sorry about that! :)

D.

tomyknoker
07-16-2007, 01:33 PM
Ok well I tried this... But nothing happened... the first query works still, but the secon doesn't get entered into the table...


mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A' WHERE ID = '".$_SESSION['mid']."'");
mysql_query("INSERT INTO tbllogins (MembersID, loginDate) VALUES({$_SESSION['mid']}, NOW()");

daemonkin
07-16-2007, 02:01 PM
Can you display the error you get if one exists?
What fields are in tbllogins?

tomyknoker
07-16-2007, 02:20 PM
Just two MembersID and loginDate... I made MembersID an index also... And no error appears which is wierd too...

daemonkin
07-16-2007, 03:08 PM
Try running an if else statement and place the mysql_error() in the else part. If the if (mysql_query) fails you should see the problem with printing out mysql_error

D.

tomyknoker
07-16-2007, 03:10 PM
Still no error :( It's like it just ignores that line completely...

daemonkin
07-16-2007, 03:41 PM
Can you post the entire PHP code around the MYSQL please?

NancyJ
07-16-2007, 04:13 PM
add or die(mysql_error()) after the second query
eg.



mysql_query("INSERT INTO tbllogins (MembersID, loginDate) VALUES({$_SESSION['mid']}, NOW()") or die(mysql_error());


Also - is $_SESSION['mid'] and integer? I assumed it was but if its not then it needs to be quoted

tomyknoker
07-16-2007, 04:45 PM
Ok here is the code...


$query = "select * from tblmembers where UserName='" . $_SESSION['strLoginName'] . "' AND Password='" . $strPassword . "'";
$query .= "AND (MemberApproved='A' OR MemberApproved='N')";
$rs = mysql_query($query);

if(!$rs)
{
mysql_close($dbConnection);
die("Failed to extract member information into database table - tblMembers: " . mysql_error());
exit();
}

if(mysql_num_rows($rs) == 0)
{
//the user cannot be found - close the database connection and redirect
mysql_close($dbConnection);
header("location:error.php?error=loginfailure");
exit();
}

else
{
//the user can be found. Authenticate and redirect
$_SESSION['firstName'] = mysql_result($rs,0,"FirstName");
$_SESSION['mid'] = mysql_result($rs,0,"ID");
$_SESSION['blnUserLoggedIn'] = true;

//get Info
$repNBR = mysql_result($rs,0,"rep_NBR");

$repInfo = mysql_query("SELECT * FROM `tblrepresentatives` WHERE `rep_NBR`='$repNBR'");
$rep = mysql_fetch_array($repInfo);

$_SESSION['rep'] = $rep['rep_Firstname']." ".$rep['rep_Lastname'];

mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A' WHERE ID = '".$_SESSION['mid']."'");
mysql_query("INSERT INTO tbllogins (MembersID, loginDate) VALUES({$_SESSION['mid']}, NOW()");

closeDatabase($rs, $dbConnection);
header("location:home.php");

}
}
?>

Yea that SESSION is the members ID so it's just a unique number in the database... Also when I added the or die, it gave me the following


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 '' at line 1

Which makes no sense on line one which has never been changed I have <?php

NancyJ
07-16-2007, 06:38 PM
its line 1 of your sql query not your php code - so its completely worthless as a debugging tool.
echo out the query and see what is being sent to the db. The only point of failure should be the $_SESSION['mid'], you could try quoting it anyway and see if that gets rid of the error. Have you checked the database that the record that should be being updated (1st query) is actually happening?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum