PHP Order by last name with middlename

2 Views

I have a database field called last_name.
I want to order the list by last name.

Here is an example:
Name
Jan DSmit
Jan van de WSmit
Jan van ASmit
Jan de FSmit

Read More

I tried doing this with SUBSTRING INDEX but that only searches a space. I have multiple.

I have a query that orders the last name field like this 

$query = "SELECT user_id, meta_value as name 
          FROM $wpdb->usermeta 
          WHERE meta_key 
          LIKE 'last_name' 
          AND user_id 
          IN (SELECT user_id 
              FROM $wpdb->usermeta 
              WHERE meta_key='company' 
              AND meta_value IN ('".$imploded_brands."')) 
              ORDER BY name";

$implode_brands holds an array of brandnames.
This works but when the ordering kicks in it places all the people who start with ‘van’ or ‘de’ next to eachother like the script tells it to do. I would like to order at the name behind the ‘van’ and ‘de’.

The expected result should be:
Name
Jan van ASmit
Jan DSmit
Jan de FSmit
Jan van de WSmit

—- EDIT—-
After some reading I found: this and this

After applying it didn’t work but maybe I’m doing something wrong here 

$query = "SELECT user_id, meta_value as name 
          FROM $wpdb->usermeta 
          WHERE meta_key 
          LIKE 'last_name' 
          AND user_id 
          IN (
             SELECT user_id 
             FROM $wpdb->usermeta 
             WHERE meta_key='company' 
             AND meta_value IN ('".$imploded_brands."')) 
             ORDER BY REPLACE (REPLACE (last_name, ' van ', ' '), ' de ', ' ')";
Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Your goal if I got it correctly has no sense to me.

    But just in case you really need that try:

    $query = "SELECT user_id, meta_value as name 
          FROM $wpdb->usermeta 
          WHERE meta_key 
          LIKE 'last_name' 
          AND user_id 
          IN (SELECT user_id 
              FROM $wpdb->usermeta 
              WHERE meta_key='company' 
              AND meta_value IN ('".$imploded_brands."')) 
          ORDER BY REPLACE (REPLACE (name, ' van ', ' '), ' de ', ' ')";

    You can try replace last line in query to:

    ORDER BY REPLACE (REPLACE (last_name, ' van ', ' '), ' de ', ' ')";

  2. Well in my opinion it would be better to sort the resulting array in php and not sort the data directly from the query. So something like this should work:

    function cmp($a, $b)
{
  $a = str_replace("van", "", $a);
  $a = str_replace("de", "",$a);
  $b = str_replace("van", "", $b);
  $b = str_replace("de", "",$b);
  return strnatcmp($a, $b);
}

usort($result_without_order, 'cmp');

    Not tested but i assume it should work fine and give you the expected results.