PHP sort an array by lastname separated by whitespace in modx

Question

I have a mysql table that looks like this:

id author public image1 image2 image3 bio media1 media2 media3 media4 media5 media6

The Field "author" normaly has Firstname (Secondname) Lastname seperated by whitespaces. How can I sort the array after the Lastname and if just one name is present after this one.

This is the modx query I use to sort after the author but obviously it doesn't use the lastname.

  $c = $modx->newQuery('AuthorDe');
  $c->sortby('author','ASC');
  $authors = $modx->getCollection('AuthorDe',$c);

Answer 1

You're shooting yourself in the foot right now, for a couple of reasons:

• When there is only one word in the string, the sorting is hard to predict.
• You have indexes for your data for a reason. They make it a lot faster. Using string functions force a table scan. Good enough for 100 data units, slow for 10000 rows and 'database went for a vacation" at 1000000.
• Next time you have to use the author field and you realize you have to split it up to words you also have to understand and fix this code snippet on top of the old ones.

That said - I haven't tested it - but try this:

$c->sortby('substring_index(author," ",-1)','ASC');