How to order a MySQL query by last word of string with PHP PDO?-CodePudding

I have a column in my database table called "name" and it stores the users names as "FirstName LastName". I want the query to return the members ordered alphabetically by their last names.

$sql = 'SELECT * FROM members ORDER BY name';
$stmt = $pdo->prepare($sql);
$stmt->execute();
$members = $stmt->fetchAll(PDO::FETCH_ASSOC);

This doesn't work since the result is ordered by the whole name field and not the last name only. How could I fix this?

Thank you.

CodePudding user response：

You may order using SUBSTRING_INDEX:

SELECT *
FROM members
ORDER BY SUBSTRING_INDEX(name, ' ', -1);

By the way, it is generally bad table design to store the first and last name in the same column. It would be much better to have two separate columns for the first and last names.