Help with complex query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
charlielha
Forum Newbie
Posts: 5
Joined: Mon Jun 19, 2006 1:59 pm

Help with complex query

Post by charlielha »

Hello.

I have a site which has a table that stores people information, Ei: Name, age, zip code, sex, etc. This site has different forms which people can filled out. When a form is filled out, the perosn gets tagged on the table with an id for the form they filled out and a sub id for which domain they came from, (the site is hosted on different domains and this is needed to track different advertising methods).

My clients needs to be able to look at these people by tag, sub tag and refin the serach by sex, zip code, etc. They can have any combination of tags and subtag. For example we have form 1, 2, 3 and 4 and domains 1, 2, 3, 4. The client then can search al forms 1, 2, and 3, from domains 3 and 4

After several tries I came up with a query like this:

select from * table name where tag='form1' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form2' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form3' and sub_tag='domain3' and sex='sex' and zip_code='zip code' OR tag='form1' and sub_tag='domain4' and sex='sex' and zip_code='zip code' OR tag='form2' and sub_tag='domain4' and sex='sex' and zip_code='zip code' OR tag='form3' and sub_tag='domain4' and sex='sex' and zip_code='zip code'

This works fine most of the time but it's a little slow and I sometimes get an error, depending on how complex I make the query, remember there are still more options like date range, etc.

I am trying to look for a way to do this search better.

Any ideas?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

Post by TheMoose »

Use the IN() syntax for MySQL:

Code: Select all

SELECT * FROM tablename WHERE tag IN ("form1", "form2", "form3") AND sub_tag IN ("domain3", "domain4") AND sex='sex' AND zip_code='zip code'
A warning, however. The more items you add to the IN part, the slower the query gets.
charlielha
Forum Newbie
Posts: 5
Joined: Mon Jun 19, 2006 1:59 pm

Post by charlielha »

The In() worked perfectly

Thanks
Post Reply