how to retreive the last login date (the required sql command)

Question

Post reply

how to retreive the last login date (the required sql command)

goshcontacts

SSC Rookie

Points: 46
More actions
November 19, 2011 at 10:17 am

#390907

hi everybody.
really sorry for this silly question, but i am still quite new to sql.
i have just build a php/mysql fuction that records the exact date and time that a user to a site logs in.
it all works probably. the problem now however is that i dont know how to retrive the data. i.e the SQL command that will enable me to retreive the last login date of the user (i.e the row containing the data of the users last login to the site).
Full Texts user_id logindate url location
Edit 17 1321710320
Edit 17 1321710400 127.0.0.1 (Private Address) (XX)
Edit 17 1321720776 127.0.0.1 (Private Address) (XX)
Edit 17 500835843 127.0.0.1 (Private Address) (XX)
//i tried using the command below to retrive the last login date, but it returns the first date of login, not the last //actual login.
$select = "SELECT
logindate
";
$from = "FROM
logindate ";
$where = sprintf("WHERE
user_id = '%s' ",
mysqli_real_escape_string($dbc, $user_id)
);
$order = " ORDER BY user_id DESC LIMIT 1";
$queryL = $select.$from.$where.$order;
$resultL = mysqli_query($dbc,$queryL);
once again i thank everyone for their kind assistance nd apologise for this rather simple question. (but what is simple to others often turns out to be a nightmare for the unsure!)
warm regards
Andreea

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You'll probably get a better and faster answer at a MySQL site like http://forums.mysql.com or http://dbforums.com

This is specifically a Microsoft SQL Server site and most people here aren't going to be that familiar with MySQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JonFox SSCrazy Points: 2157 More actions · Answer 2

MySQL probably has a MAX() function, or something similar. It looks like you want to SELECT MAX(logindate), which would return the most recent value of the logindate column that matches your WHERE clause. But yes, definitely try to get further assistance from a MySQL forum instead, there are likely to be very significant differences between query syntax for it vs. for Microsoft SQL Server...

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Agree with Gail but let’s see if we can attempt it.

$queryL = $select.$from.$where.$order;

Based on your code (specifically above line) I believe you are building query at runtime (Dynamic SQL). Can you please post the value $queryL variable. It would be the SQL query & many guys here would be able to correct it.

JonFox SSCrazy Points: 2157 More actions · Answer 4

Dev, the variable values are in the previous section of code the OP posted:

//i tried using the command below to retrive the last login date, but it returns the first date of login, not the last //actual login.
$select = "SELECT
logindate
";
$from = "FROM
logindate ";
$where = sprintf("WHERE
user_id = '%s' ",
mysqli_real_escape_string($dbc, $user_id)
);
$order = " ORDER BY user_id DESC LIMIT 1";

Looks like the whole thing should evaluate to:

SELECT logindate

FROM logindate

WHERE user_id = @user_id

The @user_id param is implied of course, the OP is actually just concatenating it into dynamic SQL. I suspect MySQL is close enough to T-SQL that what is needed here is MAX(logindate). I would also recommend using actual parameterization of course, but I don't know if MySQL supports that; I assume it does, but not sure.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Actually, looking at this again, all you need to do is change this:

$order = " ORDER BY user_id DESC LIMIT 1";

to this

$order = " ORDER BY logindate DESC LIMIT 1";

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JonFox SSCrazy Points: 2157 More actions · Answer 6

GilaMonster (11/20/2011)
Actually, looking at this again, all you need to do is change this:
$order = " ORDER BY user_id DESC LIMIT 1";
to this
$order = " ORDER BY logindate DESC LIMIT 1";

I totally missed that bit!

goshcontacts SSC Rookie Points: 46 More actions · Answer 7

hi guys

thank you so much for looking at my problem. i corrected the query and it now works.

i am so grateful for your help, it was a silly error on my part

warm regards

Andreea

Bipinkumar Yadav SSC-Addicted Points: 443 More actions · Answer 8

Try This Select Statement IN MYSQL

SELECT MAX(logindate) as Login_DATE

FROM UserLoginTable

WHERE user_id = @user_id