comparing date with last login date

crawfd

I'm trying to setup alerts for my profile page when there are either new profile comments, blog comments, or connections. Soooo, the alert works. When a comment on a profile is made my queries recognize a new addition and echo the appropriate variable. The problem that I've been having is that when I login again the second time around, my queries are seemingly still outputting that there is a new entry when there is not. Logically the second time I login as the same user the new alerts shouldn't show. Below in each query I compare the date of comment to the date of their last login (both of which are stored as datetime columns). Anyone have any ideas? Thanks


//login code here where I initiate session variables, etc.

//new profile comments?
$SQL= "SELECT usrcomments.MemberID, class_members.llogin, class_members.MemberID, usrcomments.dateadd FROM usrcomments, class_members where usrcomments.MemberID = class_members.MemberID and usrcomments.profid = '$a1[MemberID]' 
AND usrcomments.dateadd < class_members.llogin";
if(mysql_num_rows(mysql_query($SQL))>0){   

$message = "pcomments=true&";
}

//new blog comments?
$SQL2= "SELECT usrcomments.MemberID, usrcomments.oid, usrcomments.id, class_members.MemberID, class_members.llogin, usrcomments.dateadd, usrcomments.sect from usrcomments, class_members, blog where usrcomments.MemberID = class_members.MemberID AND usrcomments.sect = blog.id and usrcomments.oid = '$a1[MemberID]' 
AND usrcomments.dateadd < class_members.llogin";
if(mysql_num_rows(mysql_query($SQL2))>0){   

$message .= "bcomments=true&";
}

//pending connections?
$SQL3= "SELECT connections.usr, class_members.MemberID, connections.connection, connections.status, connections.dateadd, class_members.MemberID from connections, class_members where connections.usr = class_members.MemberID AND connections.connection = '$a1[MemberID]' AND connections.status = '0' 
AND connections.dateadd < class_members.llogin";
if(mysql_num_rows(mysql_query($SQL3))>0){   

$message .= "connections=true";
}


header("location:profile.php?id=$_SESSION[MemberID]&$message");
exit();

Roger_Ramjet

Well there is a logical inconsistency here. What you want are the comments entered AFTER the last login/logout, not BEFORE it. So change the queries to > not <.

You have to be recording both the last login and the current login, or running the queries before you update the login timestamp to get this to work. Cookies would be one way to do this: get cookie for last login, set cookie for new login. Otherwise, query for last login then update timestamp for this login.

crawfd

I changed the operator in the queries but still have the same problem. The queries ARE executing before the update of llogin ....or isn't it? It appears to be....I'm' stumped.





//login code here where I initiate session variables, etc. 

//new profile comments? 
$SQL= "SELECT usrcomments.MemberID, class_members.llogin, class_members.MemberID, usrcomments.dateadd FROM usrcomments, class_members where usrcomments.MemberID = class_members.MemberID and usrcomments.profid = '$a1[MemberID]' 
AND usrcomments.dateadd > class_members.llogin"; 
if(mysql_num_rows(mysql_query($SQL))>0){    

$message = "pcomments=true&"; 
} 

//new blog comments? 
$SQL2= "SELECT usrcomments.MemberID, usrcomments.oid, usrcomments.id, class_members.MemberID, class_members.llogin, usrcomments.dateadd, usrcomments.sect from usrcomments, class_members, blog where usrcomments.MemberID = class_members.MemberID AND usrcomments.sect = blog.id and usrcomments.oid = '$a1[MemberID]' 
AND usrcomments.dateadd > class_members.llogin"; 
if(mysql_num_rows(mysql_query($SQL2))>0){    

$message .= "bcomments=true&"; 
} 

//pending connections? 
$SQL3= "SELECT connections.usr, class_members.MemberID, connections.connection, connections.status, connections.dateadd, class_members.MemberID from connections, class_members where connections.usr = class_members.MemberID AND connections.connection = '$a1[MemberID]' AND connections.status = '0' 
AND connections.dateadd > class_members.llogin"; 
if(mysql_num_rows(mysql_query($SQL3))>0){    

$message .= "connections=true"; 
} 

$qllogin = "update class_members set llogin = '".Date("Y-m-d H:i:s")."', age = '".floor($ageYears)."' where MemberID = '$_SESSION[MemberID]'";
mysql_query($qllogin);


header("location:profile.php?id=$_SESSION[MemberID]&$message"); 
exit();

crawfd

I still haven't been able to solve this issue. Does anyone have any input? Thanks

crawfd

Oh Lord, please help me with this post. Please let thy masses find this post and may a response be provided. Amen

Sxooter

I'm guessing it's something to do with:

d = '$a1[MemberID]'

being in a string. I'm pretty sure PHP doesn't interpret arrays and vars with _ in them inside of strings. Try adding them in like:

where memberid = '".a1[MemberID]."' .....

crawfd

Nah, it's updating the date via the member ID fine ...the php is good.

Justin

Sxooter

OK. I guess you're on your own then. I'm out of ideas.

Sxooter

What type of field is usrcomments.dateadd ?

Roger_Ramjet

OK, since we don't know your data I can only guess that your error is because

usrcomments.dateadd > class_members.llogin

is the wrong date column to compare to. Presumably every member has one and you are comparing to them ALL, NOT to the last login of THIS user.

Make life easy for yourself.

During login check when you compare login credentials to those stored in the db, also grab the last login for THIS user and store it in a session var.
Use that session var in your queries.

[edit]
PS, I am surprised that '$a[memberid]' is working at all. It should always be $a['memberid']
[/edit]