Sort List After Last Name, then First Name?

Question

I'm planning on building a mySQL table that requires a student's name which looks like this:

Last Name + Father's First Name Initials + First Name

Example:

Beavers L. Scott James (father has one first name, son has two first names) 
Beavers L. D. Scott (father has two first names)

Instead of requiring to fill in 3 input fields, I only want one. But the question is, how do I sort it? I want to sort by "Last Name" first, then by "First Name" and then by the "Father's Initials". So two students with similar names would sort like so:

1. Beavers F. Christian
2. Beavers V. Scott James
3. Beavers L. Scott Paul
4. Beavers K. Sean
5. Beavers Q. Sean

Any ideas?

Answer 1

If this is all in one column then it sounds like you're going to have to return 2 columns in the query. The first is the name as-is, the second is a complex string transformation of the name (could get ugly). Order by the second column, display the first in the result application.

Answer 2

Don't munge your data.

Store three separate fields: last name, first name, paternal initials.

Then catenate the output:

select concat( last_name, ' ',  paternal_initials, ' ', first_name) as displayed_name
from table 
order by last_name, paternal_initials, first_name;

Update:

I want to make it easier for the one adding the info and use one input field. – Norbert

But people don't think that way: last name, parental initials, first name.

They'll tend to think of two separate entities: a student, with a name, and a student's father's name.

If your users are in a culture where surnames come last, you could have a field Student's Name, and field Father's Name, and assume that the last word in each is the last name. Of course this fails if surnames are followed by suffixes like "Jr", "Senior", or "3rd", and for Spanish speaking countries which follow the surname with the mother's surname.

Safest of course is separate, unambiguous fields. The additional "cost" to the user of having to tab to another field may in fact be less than the cognitive "cost" of having to figure out how to use an ambiguous field.

Answer 3

Store the values in separate columns. It's easy enough to concatenate it together for display purposes, and not a real hardship to fill in separate fields when populating a form... but it will make any searches and other data manipulation a whole lot easier for you.