Anyways, I have a table:
Code: Select all
profiles:
--------
pkid, fkid, show, only, fname, lname, regions, etcRegions 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";
}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