Hi All,
I'm experiencing some serious mental discomfort and was hoping someone may help me out.
I've got 5 variable to add into a sql search path and would like to simplfy it if I can.
Here's a rough interpretation of what I want to do :
if (isset($1)) $query .= "paperId = $1";
if (isset($1 & 2)) $query .= "paperId = $1 && stockId = $2";
1 & 2 & 3 (etc)
1 & 2 & 3 & 4
1 & 2 & 3 & 4 & 5
1 & 2 & 4
1 & 2 & 4 & 5
1 & 3
1 & 3 & 4
1 & 3 & 4 & 5
1 & 4
1 & 4 & 5
1 & 5
I'm sure there are more combinations.
The question is, is there a more eficient way to do this?
Or am I up for about 80 lines of semi repeating code?
TA TIA, Wil
[SOLVED] IF Statement Matrix - Too many options
Moderator: General Moderators
[SOLVED] IF Statement Matrix - Too many options
Last edited by facets on Tue Sep 13, 2005 5:49 pm, edited 1 time in total.
Sure.. Sorry for the confusion..
I'm attempting to build a search engine for my mysql db.
The form has 14 fields to search on but I though i'd start with 5 to "work out the logic" :
Paper Category, Manufactured Name, Computer Lookup Prefix, Face Stock, Adhesive
I want ot be able to search on any, all or some of these fields.
So i'm guessing that some kind of IF matrix is needed to to state..
if (isset($PaperCategoryId)) then $query .= "db.paperId = $PaperCategoryId";
if (isset($PaperCategoryId) && ($ManufacturedName)) then $query .= "db.paperId = $PaperCategoryId"; $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName)) then $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName) && $ComputerLookupPrefix) then $query .= "db.manufacturedId = $ManufacturedName"; $query .= "db.ComputerLookupPrefixId = $ComputerLookupPrefix
etc.. is that alittle clearer??
I'm attempting to build a search engine for my mysql db.
The form has 14 fields to search on but I though i'd start with 5 to "work out the logic" :
Paper Category, Manufactured Name, Computer Lookup Prefix, Face Stock, Adhesive
I want ot be able to search on any, all or some of these fields.
So i'm guessing that some kind of IF matrix is needed to to state..
if (isset($PaperCategoryId)) then $query .= "db.paperId = $PaperCategoryId";
if (isset($PaperCategoryId) && ($ManufacturedName)) then $query .= "db.paperId = $PaperCategoryId"; $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName)) then $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName) && $ComputerLookupPrefix) then $query .= "db.manufacturedId = $ManufacturedName"; $query .= "db.ComputerLookupPrefixId = $ComputerLookupPrefix
etc.. is that alittle clearer??
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
what i have done when trying to search multiple tables at once is just do a array of all the tables with a left join so if there are tables 1 and 2 then you would always start with a base table that links them all together assuming you have one of those then just add up the left joins joining the table on the linking field. then just start plugging in the things for the where part of the query based on the same sytle of thing. i posted this code a while back
viewtopic.php?t=35714&highlight=database+search
its easy to use if you have a base table. otherwise it might take a little to tweak but its handy becuase its very easy to expand upon
viewtopic.php?t=35714&highlight=database+search
its easy to use if you have a base table. otherwise it might take a little to tweak but its handy becuase its very easy to expand upon
Thanks for the reply.
I've got the Joins all happening nicely it's just the WHERE clauses.
Obviously I really only want to display the relevant 'requested' search options.
So if the liner checkbox is ticked. My WHERE statement sends in :
$query .= "AND ausapapersummary.linerId = '$PaperCategoryId)'";
but if I had three other 'search criteria' how do I code for this?
Starting to think I need to pay someone for this feature
I forgot to mention that most data to search on is either a drop down populated by DB or text input.
I've got the Joins all happening nicely it's just the WHERE clauses.
Obviously I really only want to display the relevant 'requested' search options.
So if the liner checkbox is ticked. My WHERE statement sends in :
$query .= "AND ausapapersummary.linerId = '$PaperCategoryId)'";
but if I had three other 'search criteria' how do I code for this?
Starting to think I need to pay someone for this feature
I forgot to mention that most data to search on is either a drop down populated by DB or text input.
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
just make a array of all the possible where clauses and then just use the script i showed you. just do like
then after you are done with adding all the search options things (your where statements to put in the search) just do somtin like
then just put that $query_where into the query where you want it...sorry if that was confusing im hungry and trying to hurry to grab a early dinner.
Code: Select all
$where = "
'water_faucents' =>
array(
'table' => 'water faucet table',
'operators' => 'and, or, like', //i did this in my code
'field_name' => 'water_field';
),
'light_switches' =>
array(
'table' => 'light switches table',
'operators' => 'and, or, like', //i did this in my code
'field_name' => 'light_field';
),
";Code: Select all
foreach ($_POST['where_statements'] as $val)
{
$query_where .= $where[$val]['field_name'].' '.$operator.' '.$_POST[$val]['value'];
}this appear to be working ok.
How would I set the $search array to only pick up variables that where if(isset()) ?
Code: Select all
if (isset($paperCategoryId))
if (isset($stockId))
$searchDb = array('ausapapersummary.paperCategoryId', 'ausapapersummary.stockId');
$search = array($paperCategoryId, $stockId);
for($x = 0; $x<count($search); $x++) {
echo ($query .= " AND ".$searchDb[$x]." = ".$search[$x]."");
}