Page 1 of 1

Combining queries into a single statement

Posted: Tue Dec 30, 2008 1:15 am
by alex.barylski
I'm not even sure this is technically possible in a single SQL statement, I swear the problem is that one expression is negating the other and thus making the evaluation mutsually expressive...

Anyways, I have a table:

Code: Select all

profiles:
--------
pkid, fkid, show, only, fname, lname, regions, etc
The fields of interest are show, only and regions

Regions is a CSV field of states which the profile is to be associated with -- I did this for simplicity to avoid introducing yet another table, I know it's best to be stored in a seperate tables, but but I just want this working as it's already caused me delay after delay.

only and show are boolean fields.

show = The profile is meant to listed publically
only = The profile should *only* be shown when certain regions are selected in the search filter

I have tried everything and grown so frustrated I cannot think about this problem anymore...

[sql]SELECT * FROM profiles WHERE SHOW = 1 AND only != 1[/sql]

Great but the minute someone selects a region filter, the query has to change to something like:

[sql]SELECT * FROM profiles WHERE SHOW = 1 AND only = 1[/sql]

Part of the problem is the way I consecutively build the SQL statement, which is needed due to the dynamic nature of the search filters.

[sql]SELECT * FROM profiles WHERE SHOW = 1[/sql]

Is the base query, if a user selects a 'segement' filter I check for that condition and ADD to the statement

[sql]SELECT * FROM profiles WHERE SHOW = 1[/sql]

Code: Select all

if(!empty($segment)){
  $query = "$query AND segment = $segment";
}
By the time regions are constructed the queries are potentially complex, so using conditionals inside regions construction would require me to effective rebuild the entire damn query with all the conditions.

Ideally I want to use a single SQL query, constructed in a waterfall approach, but I because the show and only fields conflict it's causing me serious headache. Ugh!

Can I use a sub-select or something for the regions? Pull in all the normal properties, get a resultset and remove all the records which essnetially do not want to be listed when no region is selected but only shown when their region of interest is selected.

Make sense?

Any ideas?

Cheers,
Alex

Re: I give up...need help

Posted: Tue Dec 30, 2008 2:31 am
by VladSun
I think you should have a rest for a while - it doesn't sound to be so complex and I'm sure you are able to solve it by yourself :)

Build all the queries for every case you have and use an if-elseif structure or a look-up table (which I prefer more) to choose the right query.

Example of a look-up table:

Code: Select all

define('NOCASE', 0);
define('CASE1', 1 );
define('CASE2', 2 );
define('CASE3', 4 );
define('CASE4', 8 );
 
$case1 = empty($_REQUEST['case1']) ? 0 : CASE1;
$case2 = empty($_REQUEST['case2']) ? 0 : CASE2;
$case3 = empty($_REQUEST['case3']) ? 0 : CASE3;
$case4 = empty($_REQUEST['case4']) ? 0 : CASE4;
 
$LT = array();
 
$LT[ NOCASE ]           = '';
$LT[ CASE1 + CASE2 ]    = 'case1 and case2';
$LT[ CASE1 + CASE3 ]    = 'case1 and case3';
...
$LT[ CASE2 + CASE4 ]    = 'case2 and case4';
I think, the look-up table is clearer and easier to be used compared to if-else structures.

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 3:17 am
by alex.barylski
I'm not sure...certainly I'm frustrated...and another set of eyes wouldn't hurt. :)

The problem is being caused by mutual exclusivity...that is...one part of query says select all profiles which are marked as public (always exists), but another part (the last part) only selects profiles when a specific region is selected from a drop down.

How do you select all public records but also only select "some" public records when a certain region is selected?

The problem is, there are two options in the profile form:

1. Checkbox to indicate the profile is public
2. Checkbox to indicate the profile should only be shown when one of X number of regions is selected

I keep thinking, two queries are needed, one to pull the profiles initially and another to filter the results based on region.

Cheers,
Alex

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 3:24 am
by VladSun
PCSpectra wrote:How do you select all public records but also only select "some" public records when a certain region is selected?

The problem is, there are two options in the profile form:

1. Checkbox to indicate the profile is public
2. Checkbox to indicate the profile should only be shown when one of X number of regions is selected
Like this:
[sql]SELECT    *FROM     profileWHERE    SHOW = 1    OR    (        only = 1        AND        region LIKE '%$region%'    )[/sql]

?

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 3:32 am
by VladSun
And yeah - you should create another, or better 2 tables for regions:
[sql]SELECT    *FROM     profileLEFT JOIN    (        SELECT             *        FROM            profile_region        INNER JOIN             region ON                 region.id = profile_region.region_id                AND                 region IN ($regions)    )     AS sub_profile_region ON        profile.id = sub_profile_region.profile_idWHERE    SHOW = 1    OR    (        only = 1        AND        profile_region IS NOT NULL    )[/sql]

It's because playing with CSV data in SQL queries is a hard work and the query will be slower.

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 3:51 am
by alex.barylski
I finally figured it out:

[sql]SELECT * FROM profiles WHERE industry = 'computers' AND `show` = 1 AND `only` = 0 AND practice = 'programming' AND regions LIKE '%PE%' OR (regions LIKE '%PE%' AND `only` = 1)[/sql]

Such a trivial mistake but what a headache. :banghead:

I just hope I haven't over-looked anything and have an issue in the future...

Cheers,
Alex

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 4:00 am
by VladSun
How will you handle multiple choices in the region filter ?

Code: Select all

regions LIKE '%PE%'
will not work in such case.

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 8:26 am
by alex.barylski
Simple. I Won't. :P

It's a single selection drop down, so only one region at a time. I'll change it in the future if need be.

EDIT | Chri$t...apparently I didn't test this very well cause it doesn't work properly when no regions are selected. :banghead:

Re: Combining queries into a single statement

Posted: Tue Dec 30, 2008 11:55 am
by John Cartwright
Simply omit the OR clause and anything thereafter if no region is selected :)