从MySQL中选择 - 只有姓氏的首字母

Question

I have a question when doing the selection from a database:

A column from one of my tables contains the name from persons. Let's say it looks like this:

 1. John Smith
 2. William Thorne
 3. Mark Johanson

Ok, I have shown you some examples of some random names.

The thing is that both, the first name, and the last name are in the same column, and the result I want to come to, when I'm doing the selection in my php file is this:

1. John S.
2. William T.
3. Mark J.

I want that from the last name of each person, to be shown only the initial of the last name. Is there a possibility to do this? If there is, any help would be great.

My code selection example is here:

$result = mysql_query("SELECT * FROM feedback_rate 
                       WHERE accept=1 
                       ORDER BY date DESC 
                       LIMIT $start,$rows_per_page",$con);

Thank you for the help in advance.

Answer 1

You have to realise that this is extremely difficult to do right. Computers don't know about names, they have no idea what's a first name and what's a last name. You therefore will have to make assumptions which aren't always true. I make the assumption that there's always only one first name. Then it is easy to solve in PHP with the following steps:

1. Split name into words.
2. Change the last word.
3. put them back together again.

This is how that is done:

$parts = explode(' ',$fullName);
$firstName = array_pop($parts);
$initial = substr($firstName,0,1);
array_push($parts,$initial.'.');
$fullName = implode(' ',$parts);

And now $fullName will contain what you wanted. The code look a bit long but that's because I try to show it step by step.