PHP/MySQLi: SET lc_time_names and DATE_FORMAT() into a mysqli query?
15,638
Solution 1
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli->query("SET lc_time_names = 'es_ES'");
$check_sql = 'SELECT personID, name, DATE_FORMAT(persons.birthdate, "%d de %M, %Y"), birthplace, countryID FROM persons WHERE personID = ?';
if ($stmt = $mysqli->prepare($check_sql)) {
$stmt->bind_param('i', $pid);
$stmt->bind_result($personDB, $name, $birthdate, $birthplace, $countryID);
$stmt->execute();
$stmt->fetch();
}
Solution 2
Another option is to return the raw date/time and then format it in PHP.
Before you format the date, you can use the setlocale() function in PHP to get Spanish output.
This has the added benefit of not running a function in mysql, which can be slower.
Author by
Jonathan
Updated on June 28, 2022Comments
-
Jonathan almost 2 years
I use the next code to retrieve data from a table in the database:
$check_sql = 'SELECT personID, name, DATE_FORMAT(persons.birthdate, "%d de %M, %Y"), birthplace, countryID FROM persons WHERE personID = ?'; if ($stmt->prepare($check_sql)) { $stmt->bind_param('i', $pid); $stmt->bind_result($personDB, $name, $birthdate, $birthplace, $countryID); $stmt->execute(); $stmt->fetch(); }
Like you can see, at the same time I format the date from the 'birthdate' column to a more friendly display using the DATE_FORMAT() MySQL function. Now, I want to display the month full names in Spanish, so I want to insert
SET lc_time_names = 'es_ES'
into the query..How can I do it??? Can I add
SET lc_time_names
to the $check_sql variable??Thanks!!