Selection from MySQL - only the initial from the Last Name

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.

Relying on the space to designate the first and last name is not reliable. What do you do for Mary Jo De Rossi? — TunaMaxx, Aug 29 '14 at 07:51
@GeorgeGarchagudashvili Mary Jo is a double name, Mary J wouldn't be correct looking at the specification. — Thomas Lindvall, Aug 29 '14 at 08:02

KIKO Software · Accepted Answer · 2014-08-29T08:01:59.943

4

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:

Split name into words.
Change the last word.
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.

edited Aug 29 '14 at 08:01

answered Aug 29 '14 at 07:55

KIKO Software

15,283
3
18
33

1

PHP can do this a lot shorter: `$fullName = substr($fullName,0,strrpos($fullName,' ')+1).'.';` but that would be more difficult to understand? Many more variants are possible. – KIKO Software Aug 29 '14 at 08:11
1

Note that this approach is not multibyte-aware, so you may get corrupted output when using UTF-8 input. – CamaroSS Aug 29 '14 at 08:23

George G · Answer 2 · 2014-08-29T08:07:27.560

It's better to manipulate data in PHP (leaving original data untouched, it's always good practice):

$name = "John Sephre";
$expl = explode(' ', $name);

echo $expl [0].' '.$expl[1][0];

Output:

John S

If you gonna have long names:

$name = "John Sephre More";
$expl = explode(' ', $name);

$last = end($expl);

echo $expl[0].' '.$last[0];

Output:

John M

Also I would recommend to create function for this purpose:

function makeShortName($fullName)
{
    $expl = explode(' ', $fullName);

    $shortened = $expl[0];

    if(count($expl) > 1)
    {
        $last = end($expl);
        $shortened .= ' '.$last[0].'.';
    }

    return $shortened;
}

So if you pass just John it will return John and if you pass John nickname Surname result will be John S.

score 0 · Answer 3 · answered Jun 09 '20 at 11:15

I find this is the easiest way of dealing with this. It simply creates an initial on the last word of the string. This allows for double barrel surnames like 'van Dyke', which becomes 'van D'. Note that hyphenated names will still be reduced to the first character, but it's unlikely that this would be an issue. If there is only one word in the string, it will not be initialized. Three simple lines of code:

$nameparts = explode(' ',$fullname);
if(count($nameparts) > 1) $nameparts[count($nameparts)-1] = substr($nameparts[count($nameparts)-1],0,1);
$fullname = implode(' ',$nameparts);

Selection from MySQL - only the initial from the Last Name

3 Answers3