PHP/MySQLi: SET lc_time_names and DATE_FORMAT() in

问题:

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

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

回答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.

回答3:

SET lc_time_names = 'es_PE';
SELECT *, DATE_FORMAT(fecha,'%d de') AS dia,
DATE_FORMAT(fecha,'%M  %Y') AS mes
 FROM t_eventos4