[SOLVED] IF Statement Matrix - Too many options

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

[SOLVED] IF Statement Matrix - Too many options

Post by facets »

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
Last edited by facets on Tue Sep 13, 2005 5:49 pm, edited 1 time in total.
127.0.0.1
Forum Newbie
Posts: 22
Joined: Mon Sep 12, 2005 8:59 pm

Post by 127.0.0.1 »

You will need to word your question better as to what you are trying to do?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

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??
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

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
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

just make a array of all the possible where clauses and then just use the script i showed you. just do like

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';
    ),
";
then after you are done with adding all the search options things (your where statements to put in the search) just do somtin like

Code: Select all

foreach ($_POST['where_statements'] as $val)
{
  $query_where .= $where[$val]['field_name'].' '.$operator.' '.$_POST[$val]['value'];
}
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.
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

this appear to be working ok.

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]."");
    }
How would I set the $search array to only pick up variables that where if(isset()) ?
Post Reply