PHP SQL Order By from a field in a different table

I want to sort the returned results from a PHP query with a field from another table that it collects once the first is returned. To explain...

    $args1 = array('order' => 'ASC');
    $list = get_users($args1);

        foreach ($list as $post) {
            $email = $post->user_email

;
        $id =  $post->ID;

        $user = get_userdata($id);
        $firstname = $user->user_firstname;

To explain... get_users gets all users from the users database. The problem is that the usersdata is another table that contains the surnames of users. We need the ID from the first query to get the correct surname. It is then this surname that we want to order the entire data by.

The Userdata table uses meta_keys with the ID of the corresponding user next to each key.

So what I am after is to sort the user data by the usermeta key 'lastname'. Here is the full code.

$args1 = array('order' => 'ASC');
$list = get_users($args1);

for开发者_如何学编程each ($list as $post) {
    $email = $post->user_email;
    $id =  $post->ID;

    $user = get_userdata($id);
    $firstname = $user->user_firstname;
    $lastname = $user->user_lastname;
    $return .= '<li>
                <a href="#sidebar2" onClick="slide(this); return false" rel="clients_list_page" style="line-height:16px;">'.$firstname.' '.$lastname.'<br /><span style="font-size:10px; color:#555;">'.$email.'</span></a>
            </li>';

}
    $return .= '</div>';

Any ideas?

Marvellous

---

It seems overly complicated the way you are doing it. Can´t you just use a JOIN to get the data from both tables in one query?

In plain sql that would be something like:

SELECT users.email, userdata.firstname, userdata.lastname 
    FROM users LEFT JOIN userdata
        ON users.id=userdata.user_id
    ORDER BY userdata.lastname

---

If I understand the problem correctly then this not a php problem.

You can solve this with a simple join and order everything on the db side. Even if you're using some kind of ORM then you'll probably be able to run a query that will return fields only from one table and with the correct order.

---

@jeroen is right about using a JOIN. There are two types of JOIN, a LEFT JOIN, and INNER JOIN. The difference between the two, is with an LEFT JOIN, if your table on the left (in this case, the users table) contains records that don't have corresponding records in the righthand table (e.g. your users table has records with ids that don't match up with records in your userdata table), it will return those rows from the left table, with NULL for the value that should have come from the righthand table. Using an INNER JOIN these rows would simply not be returned.

The syntax for an INNER JOIN would look something like this:

SELECT users.email, userdata.firstname, userdata.lastname
    FROM (users, userdata)
        WHERE (users.id = userdata.user_id)
    ORDER BY userdata.lastname ASC

If you wanted to add any additional conditions to the query, you would simply put them in the WHERE clause.