Select date_format

MySQL database
#1

Hello, I am working on updating my login script. I am implementing login date time and last login date time records for members of my site. I have no problem inserting a login date, then retrieving and formatting the login date time. However, today i’ve added the last login datetime, which works by selecting the login date time at login then updating last login with this value. I then run a select query to get the login date time and now i want to add the last login time to the query.

the query is not working unless i run two queries and format both queries. For example,

$Query = "SELECT DATE_FORMAT (loginDatetime, '%W, %d. %M %Y %H:%i:%S') loginDatetime FROM user_accounts WHERE id = :userID";

before i run this query, i have to set the lc to get the correct format. I’ve tried to add lastloginDatetime to the above query but it results in a 500 error. I am nescient about this command so i guess that i am either doing it incorrectly or one may only format one column at-a-time. Or some other reason.

I am wondering how to format two date time stamps simultaneously with one query. is it possible?

#2

Trying to figure out the purpose.

To do the update it is as simple as,

UPDATE user_accounts SET loginDatetime  = NOW() WHERE id = 123

Now if you want to use that timestamp later, you can store it in a variable instead and use it twice?

#3

yes, this is how i programmed the script. Let me fetch my code and return for a clearer picture. I suppose that a variable does make sense but how to format this variable in php? isn’t it now a string to php? it seems like overkill to format the string dt using php versus recalling this info from the database and formatting it with sql. I will return…

#4

It all depends how you want to do it.

I would do something like this if necessary,

$date = new DateTime();
$sql = 'UPDATE user_account SET last_login = ? WHERE id = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$date->format('Y-m-d H:i:s'), $userID]);
echo $date->format('Y-m-d H:i:s');
1 Like
#5

okay, so let me try this again: if we place this data into a variable, then it becomes a string and php datetime objects will choke because it is not a datetime object. We have a string. Why waste code doing what sql will allready do for us? i’d rather format the string as it comes out of the database. Fast, easy, efficient. However, it seems that two DATE_FORMAT commands cannot be used in a single query or the command cannot act upon two columns with one command:

$selQuery = "SELECT DATE_FORMAT (loginDatetime, lastloginDatetime, '%W, %d. %M %Y %H:%i:%S') loginDatetime, lastloginDatetime FROM user_accounts WHERE id = :userID";

this results in a server 500. I don’t know how to use this command effectively on two columns. In fact, i didn’t know how to do this in the first place. I had to answer my own question about setting the region AND formatting the data. I am happy that i figured it out and a bit proud of myself but no back patting. Just happy. Now i want to format both column values but i cannot get it to work.

The login process works as such:

$selQuery = 'SELECT id, username, password, loginDatetime FROM user_accounts WHERE username = :User';
$dbLoginDT = $result['loginDatetime']; //here we store the logintime for entry into the last login time column

if (hash_equals($dbUser, $username) && password_verify($password, $dbPassword)) {
//bla bla user is valid or not

//now we get the hammer out and get to work:
$UpdateDB = 'UPDATE user_accounts SET user_session_id = :userSid, loginDatetime = NOW(), last_loginDatetime = :dbLoginDT WHERE id = :userID';
//dbLoginDT is the variable above: $dbLoginDT = $result['loginDatetime'];

first SET lc_time_names for the following format command or you will get the mysql en-us default
$selQuery = "SELECT DATE_FORMAT (loginDatetime, '%W, %d. %M %Y %H:%i:%S') loginDatetime FROM user_accounts WHERE id = :userID";
$_SESSION['LoginDT'] = $result['loginDatetime'];

**now i need to do the same for last login datetime but i cannot get it to work in one query.**
**i have it working fine with two queries but i'd like to optimize this code to mimize db activity.**

is there a way to execute a date format command on two column values simultaneously? or do i continue with two separate set lc and date format queries?

#6

as the function expects one date, you have to use it for every column separately.

1 Like
#7

Thank you for clarifying this matter, Sir Christian. I truly appreciate you.
I am still a newbie to sql and mysql database. I have alot to learn.

Also, i often have to switch my mental state from programmer John to analytical John in order to answer a question about my methods. I tell the truth: i usually turn on Motorhead I am The Sword repeated and hack away at my app. I get into a grrove and i have to snap out of it in order to justify an action.

So, why do i not enter a formatted date? because the default is en-us for mysqldb. What if i need to check the dates of all users? i would have to format the date just to run a query. I prefer to let sql/mysql handle the matter in default mode for quicker access. I prefer to only format a date for a user friendly view, such as my user settings page of my web site. Here i prefer to see a formatted date. Programmatically speaking, i find it better to not format the date for storage in the column making date operations much easier on anyone coming behind me. I’d hate for a programmer to look at my code and lay an egg. What is this garbage? lol.

I will keep my two queries and be on my way. Thank you astonecipher and chorn. I hope that you have a splendid day :smile:

#8

Hello Christian, I’ve optimized the code to one query with two date formats for each column and it works perfectly. Thank you.

This is the result that i was looking for because i don’t want formatted columns as this is not scalable architecture. I seek scalability for this app because i wish to offer multiple language support. Now i am able to format for each language smoothly.

Super! Danke :slight_smile:

#9

There are a few ways to handle compatability formatting. Doing it in the code is faster, but if you have a cross reference table, you can do something like,

SELECT DATE_FORMAT (loginDatetime, (SELECT value from Parameter WHERE Name = 'date_format' and lang = 'uk') loginDatetime
2 Likes
Sponsor our Newsletter | Privacy Policy | Terms of Service