How to Order WordPress User Tables Numerically Instead of Alphabetically
November 1, 2018 By Andrew Gehman
Recently, we had a client whose project required that the WordPress usernames be numbers. On the surface, this seems easy, but it’s not as straightforward as you might think. WordPress does allow for alphanumeric characters, so you can set the Usernames to 1 or 2 or 20, etc. However, the order of table will be determined alphanumerically, reading left to right, and will not show up in numerical order. If you want your numerical Usernames to be listed in order, there are a few extra steps you can take to make it happen. Here’s how to order WordPress user tables numerically instead of alphabetically:
How the Typical User Table Sort Order Works in WordPress
WordPress uses the WP_User_Query class to query the user table. WP_User_Query allows ordering to be ascending (ASC) or descending (DESC), but all sorting is done alphanumerically. The usernames are evaluated left to right in the same manner as alphabetical ordering, so all the usernames that begin with ‘1’ will come before all usernames that begin with ‘2’, etc. When displaying numerical usernames, they are ordered like 1, 10, 100, 2, 20, 3, etc.
How to Tweak the Sort Order of a WordPress User Table
We needed to alter the “ORDER BY” portion of the Users query to return the desired results in the desired sort order. We can use the pre_user_query hook and the PHP str_replace function to change the user query. The pre_user_query hook runs after the WP_User_Query has been parsed but before the query has been executed which will allow us to alter the output. We will be adding this code to the functions.php file of your WordPress theme.
Using the PHP str_replace Function
The PHP str_replace function allows us to change the ‘ORDER BY’ portion of the query and replace the user_login with user_login*1. By multiplying the user_login by 1, it forces the “numbers” to be treated like actual numbers.
function reorder_pre_user_query( $query ) {
global $pagenow;
if ('users.php' == $pagenow && current_user_can( 'edit_user' ) ) {
$query->query_orderby = str_replace(
'ORDER BY user_login',
'ORDER BY user_login*1',
$query->query_orderby
);
}
return $query;
}
add_action( 'pre_user_query', 'reorder_pre_user_query' );
Now, the resulting user table will display the usernames in numerical order. (Note: If you have names and numbers, the numbers will be listed first using this method)
We could get the same result by replacing user_login with user_login+0. This follows the same concept as above. Adding zero forces the “numbers” to be handled as actual numbers. Unlike the previous method, alphabetical names will appear before the numbers.
function reorder_pre_user_query( $query ) {
global $pagenow;
if ('users.php' == $pagenow && current_user_can( 'edit_user' ) ) {
$query->query_orderby = str_replace(
'ORDER BY user_login',
'ORDER BY user_login+0',
$query->query_orderby
);
}
return $query;
}
add_action( 'pre_user_query', 'reorder_pre_user_query' );
Using the MySQL CAST Function
Another way to achieve the same result is to use the MySQL CAST function, which allows for the conversion of one data type to another. Cast allows for 2 data types that will give us the desired result. Unsigned will give us a positive integer value, and decimal will give a decimal value. Under the hood, MySQL is casting a varchar field to an integer or double field with the previous two methods as well.
function reorder_pre_user_query( $query ) {
global $pagenow;
if ('users.php' == $pagenow && current_user_can( 'edit_user' ) ) {
$query->query_orderby = str_replace(
'ORDER BY user_login',
'ORDER BY IF(user_login RLIKE \'^[a-z]\', 2, 1), user_login*1',
'ORDER BY CAST(user_login AS UNSIGNED)',
$query->query_orderby
);
}
return $query;
}
add_action( 'pre_user_query', 'reorder_pre_user_query' );
With these three examples, you can alter the way the way numbers are listed in the WordPress User table. Have questions? Do you have an alternate solution? Let us know!
View Comments