SQL query to display users matching 2 criteria

Solutions for other advanced phplisters

SQL query to display users matching 2 criteria

Postby ph2010 » 10:21pm, Wed 24 Mar, 2010

Hello, I need a SQL query to display or export all email addresses which match two or more criterias.

Values of different types of criteria (radio, list, text) are stored in "phplist_user_user_attribute" .
If the criteria is of type 'select from a list' it only stores the value as an ID and the real name of each ID is stored for example in "phplist_listattr_countries" (value 19 in phplist_user_user_attribute)
If I add a criteria of type text, it stores the value as it is (some_text)
Lets call second attribute Hobby with attributeid = 2 (see phplist_user_attribute)

Now how do I find all email address which match Country = England (19) AND Hobby = some_text ?

table: phplist_user_user_attribute

attributeid___userid___value
1___________1_______19
2___________1_______some_text
ph2010
phpList newbie
 
Posts: 2
Joined: 6:30pm, Wed 24 Mar, 2010

Re: SQL query needed please

Postby szucsati » 12:03am, Fri 26 Mar, 2010

Code: Select all
SELECT
 email,
 t1.userid,
 t1.value AS Country,
 t2.value AS Hobby
FROM phplist_user_user_attribute t1,
 phplist_user_user_attribute t2,
 phplist_user_user
WHERE t1.userid = t2.userid
 AND t1.attributeid = 1
 AND t1.value = 3
 AND t2.attributeid = 2
 AND t2.value = "some_text"
 AND t1.userid = phplist_user_user.id;


This will give something like:

Code: Select all
email    userid    Country    Hobby
user00005@domain.com    5    3    some_text
szucsati
phpLister
 
Posts: 7
Joined: 8:38am, Fri 12 Mar, 2010


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest