PHP/MySQLi: SET lc_time_names and DATE_FORMAT() into a mysqli query??

Question

Hi, 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!!

Answer 1

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.

Answer 2

$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();
        }