[wp-hackers] Extending the user search

[John Blackbourn]

Oh that's spot on Scribu. Many thanks!

John

2009/3/12 scribu <scribu at gmail.com>:
> Ah, I ran into the same problem for postmeta values. This is the what I came
> up with:
>
> WHERE CASE meta_key
> WHEN 'key1' then meta_value = 'value1'
> WHEN 'key2' then meta_value = 'value2'
> END
> GROUP BY ID
> HAVING COUNT(ID) = 2
>
> On Thu, Mar 12, 2009 at 9:52 AM, John Blackbourn
> <johnbillion+wp at gmail.com<johnbillion%2Bwp at gmail.com>
>> wrote:
>
>> I guess this is really an SQL question but I thought I'd ask here.
>>
>> I'm writing a plugin which extends the user search/filter
>> functionality on the Users admin screen. It allows a user to filter
>> the results based on two custom user meta values. The [simplified] SQL
>> which retrieves the users is:
>>
>> SELECT * FROM wp_users, wp_usermeta WHERE wp_usermeta.meta_key =
>> 'key1' AND wp_usermeta.meta_value = 'value1'
>>
>> That's all well and good for one meta value, but I am absolutely
>> stumped about how I can test for a second meta key/value pair which
>> must also be present. The problem is that the second meta_key will
>> have a different value to the first, and adding something like:
>>
>> AND wp_usermeta.meta_key = 'key2' AND wp_usermeta.meta_value = 'value2'
>>
>> will always return zero rows because you're trying to retrieve a row
>> with two different values for the same field (meta_key = 'key1' AND
>> meta_key = 'key2').
>>
>> Any SQL experts out there that could give me a pointer?
>>
>> John
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
>
>
>
> --
> http://scribu.net
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>