DATETIME format problem [SOLVED]

[johnphpnewb]

I’m trying to implement the DATETIME in various columns for documenting user action. For example, LastLogin, PasswordChangedAt, UsernameChangedAt.

The DATETIME format appears to b en-US, a default of mysql? 2019.03.22 plus timestamp.
I want to format this as %W, %d. %M %Y Friday, 22. March 2019

I’ve tried DATE_FORMAT() but it didn’t work for me.

SELECT DATE_FORMAT (column, '%W, %d. %M %Y') column FROM table WHERE ...;

should i try the following command?

SELECT DATE_FORMAT (column, '%W, %d. %M %Y') AS column FROM table WHERE ...;

or this not possible?
is it better for me to use php when adding a datetime instead of the SQL DATE_TIME command?

[astonecipher]

It’s better to let the database do the database work. What didn’t work for you, trying to understand the issue.

[johnphpnewb]

The issue is pdo/php related. Perhaps i do not understand this command (DATE_FORMAT()).

if i run the select statements directly into a console, then mysql returns the column’s datetime stamp with the format that i specified in the query. When i add this query to my pdo login script, i cannot login to my site. I get an internal 500 error. I suspect that i cannot fetch() this output. I want to show a member when they logged in and last login datetime but i want the date formatted for Germany.

i don’t understand why i get formatted output at the console but not in my pdo query. do i still use execute and fetch? what is the method for formatting a date using pdo so that one can capture this output in a session variable?

[astonecipher]

What’s the entire query you are attempting?

[johnphpnewb]

i select the username and password for comparison
if username and password match a user in the db
update the loginDateTime as Now()
then i try to fetch this column for a session variable

$loginQuery = 'SELECT loginDateTime FROM user_accounts WHERE username = :PostUser';
$stmt = $dbh->prepare($loginQuery);
$stmt->execute(array(':PostUser' => $username));
$result = $dbh->fetch();
$_SESSION['loginDateTime'] = $result['loginDateTime'];

then i select user display name and background photo

and my login process is handed over to the session and the member pages

now when i modify the $loginQuery i get an internal 500 error:

$loginQuery = 'SELECT (loginDateTime, '%W %d %M %Y %H %i %S') AS loginDateTimeDE FROM user_accounts WHERE username = :PostUser';

if i type this command into the console, i get the expected output.
Thus, i wonder if it has to do with :PostUser placeholder, execute() or fetch().
I really do not know why i cannot capture the output into a session variable.
any ideas?

[JimL]

You can actually see the problem in the way the code is highlighted here. The way you quote it means this is actually interpreted as code:

%W %d %M %Y %H %i %S

Try this where we don’t close the quotes

$loginQuery = 'SELECT (loginDateTime, "%W %d %M %Y %H %i %S") AS loginDateTimeDE FROM user_accounts WHERE username = :PostUser';

[johnphpnewb]

I’ve tried it again and i still get a 500 error.

Good work spotting the quotes, Jim. It never dawned on me that the query is double quoted. I’ll keep trying to spot the problem(s).

Thank you, Jim

[JimL]

what does the 500 error say?

[johnphpnewb]

Hi Jim, I got it to work today. yay! I changed the command:

$loginQuery = "SELECT (loginDateTime, '%W, %d. %M %Y %H:%i:%S') loginDateTime FROM user_accounts WHERE username = :PostUser";

However, i have a new question for you or anyone that may have an answer:
I want the output to be in German language. I connected to MySQL via console and ran the SET lc_time_names = ‘de_DE’; but the date still shows as en-US. How can i change the locale so that the correct language is displayed in my page? am i not doing it correctly?

[johnphpnewb]

Problem solved! Thank You Jim and astonecipher.

I added a separate query to set the time name to German. Works perfectly!
I am happy now. All i have to do is change login scripts based upon language, so that i can continue with a multi language app.

I hope that everyone has a pleasant day and a pleasant weekend.

[chorn]

On 500 HTTP error your first look should always be the servers logfile, as the source of this error is totally random and useless to guess for other people.

[johnphpnewb]

I’m happy that everything is working.

however, i seem to have forgotten the fundamentals of programming. I didn’t notice the double quoted statement and i forgot to check the logs. Maybe i am pushing too hard to complete this project. I need to relax more often.

Thank you, Christian.