Tuesday, 1 October 2013

Search a table based on multiple rows in another table

Search a table based on multiple rows in another table

Basically I have three MySQL tables:
Users - contains base information on users
Fields - describes additional fields for said users (e.g. location, dob etc.)
Data - Contains user data described via links to the fields table
With the basic design as follows (the below is a stripped down version)
Users:
ID | username | password | email | registered_date
Fields
ID | name | type
Data:
ID | User_ID | Field_ID | value
what I want to do is search Users by the values for the fields they have,
e.g. example fields might be:
Full Name
Town/City
Postcode
etc.
I've got the following, which works when you're only wanting to search by
one field:
SELECT `users`.`ID`,
`users`.`username`,
`users`.`email`,
`data`.`value`,
`fields`.`name`
FROM `users`,
`fields`,
`data`
WHERE `data`.`Field_ID` = '2'
AND `data`.`value` LIKE 'london'
AND `users`.`ID` = `data`.`User_ID`
AND `data`.`Field_ID` = `fields`.`ID`
GROUP BY `users`.`ID`
But what about if you want to search for Multiple fields? e.g. say I want
to search for Full Name "Joe Bloggs" With Town/City set to "London"? This
is the real sticking point for me.
Is something like this possible with MySQL?

No comments:

Post a Comment