CODEWITHSUNDEEP
mysql
Walrus
Walrus

Reputation: 20444

My first SQL Query - OrderBy error

I am using a MySQL query to get information from two tables and then ordering them by one field. Something about the last line is not working and the AJAX function that loads the content returns an error.

$list = mysql_query("SELECT    id, 
           user_email, 
           meta_lastname.meta_value lastname,  
           meta_firstname.meta_value firstname 
 FROM      wp_users 
 LEFT JOIN wp_usermeta meta_lastname 
 ON        meta_lastname.user_id = wp_users.id 
 LEFT JOIN wp_usermeta meta_firstname 
 ON        meta_firstname.user_id = wp_users.id 
 WHERE     meta_lastname.meta_key = 'last_name' 
 AND       meta_firstname.meta_key = 'first_name'
    WHERE user_email LIKE '%$searchpattern%' 
    ORDER BY lastname;");

Something about this last bit is not right and is returning an error.

WHERE user_email LIKE '%$searchpattern%' 
        ORDER BY lastname;");

Any ideas on whats gone wrong?

Marvellous

Upvotes: 1

Views: 112

Answers (4)

nobody
nobody

Reputation: 20174

You also need to change the second "WHERE" to an "AND".

Upvotes: 1

user657127
user657127

Reputation:

I guess it's the semicolon. Try ORDER BY lastname"); Else try without the order statement.

Upvotes: 1

Femaref
Femaref

Reputation: 61437

You have two WHERE in your query:

 WHERE     meta_lastname.meta_key = 'last_name' 
 AND       meta_firstname.meta_key = 'first_name'
    WHERE user_email LIKE '%$searchpattern%'

You need to use a logical operator like AND:

 WHERE     meta_lastname.meta_key = 'last_name' 
 AND       meta_firstname.meta_key = 'first_name'
    AND user_email LIKE '%$searchpattern%'

Also, you probably have to order by the original name meta_lastname.meta_value not the alias lastname.

Upvotes: 5

Paul Creasey
Paul Creasey

Reputation: 28834

order by the full column name, not the alias.

ORDER BY meta_lastname.meta_value

Upvotes: 1

Related Questions