Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [SOLVED] IF Statement Matrix - Too many options

    Hi All,

    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". Each of the following is either text input of drop down
    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

    that's just a smidge of what I need to cover the 5 x 5 x 5 x 5 options.
    does anyone have a simpler way to do this?

    tia, will
    Last edited by facets; 09-18-2005 at 04:46 AM.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    An idea (not tested or anything):
    Store the data names and stuff in a hash, 2 dim array, or other construct, then loop.

    Pseudocode for associative array (hash in php?)

    store all $varname and $sqlstring pairs in hash

    for each element in hash do
    -- if $varname is set then append $sqlstring
    loop

    You might need some sort of eval or something to check for the $varname defined thing, since it will be stored as a string. Not sure...

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    excellent. i was thinking something like that, with some outside help

    so something like :

    if (isset($paperCategoryId)) $var1[] = $paperCategoryId
    if (isset($stockId)) $var2[] = $stockId
    if (isset($linerId)) $var3[] = $linerId)
    if (isset($supplierId)) $var4[] = $supplierId

    $myarray = myarray($var1, $var2, $var3, $var4);
    $setup_sql_statement = implode(",", $myarray);

    foreach i (echo $setup_sql_statement)
    $query .= "AND ausapapersummary.paperCategoryId = '$i' ";
    done

    obviously the above won;t work because of the mixed languages but is that what you mean?

  • #4
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this appear to be working ok.
    but it's filling in both option even though only one maybe set.

    PHP Code:
    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()

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Where are these values comming from? As a search engine of sorts, I'd say from a form correct?
    Instead, create your form as an array of fields. Here's what I mean:
    Code:
    <!-- blah blah blah, to the form -->
    <input type="text" name="search[paperCategoryId]" value="" />
    <input type="text" name="search[stockId]" value="" />
    <input type="text" name="search[linerId]" value="" />
    <!-- etc -->
    Then with your script, something like so:
    PHP Code:

    $table_alias 
    'ausapapersummery.';
    $condition '';
    if (
    is_array($_POST['search']))
    {
         
    $condition ' WHERE 1=1';

    foreach (
    $_POST['search'] AS $key => $val)
    {
          
    // Here's the tricker, text fields are always set, even if its null:
         
    if (!empty($val))
         {
              
    $condition .= ' AND `' $table_alias $key '` = "' mysql_real_escape_string($val) . '"';
         }
    }
    }
    $query "SELECT $table_alias.* FROM table AS ausapapersummery" $condition
    Or something of the sorts. I'd recommend an array of acceptable fields and datatypes to help prevent sql injections as well. If your data is not from a form, this method can still be used, but you would need to alter your array to choose from. This is untested as well, btw.
    Offhand, if I need to take a stab at your full problem, I'm guessing your using a form method, and passing text fields. Your method searches for an isset() on the field => text fields are always sent, if empty it will send null. This is why I use !empty() instead, as it ignores whether or not the variable exists or not, it mearly checks to ensure it has a value.

    Edit:
    Oh, thought I'd mention as well, with a search engine you may find some use with a LIKE syntax instead of '='. This depends of course on how specific you want the user to be, and how much data you want to return.
    Cheers.
    Last edited by Fou-Lu; 09-13-2005 at 04:14 PM.

  • #6
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your ideas!
    I'm so close with this coding that i'm hesitant to change it.

    currentl if the variable is not set all that prints out is ' AND ='
    any idea on how I would stop that?

    PHP Code:

    if (isset($paperCategoryId)) $var1 $paperCategoryId
    if (
    $paperCategoryId 0$var3 'ausapapersummary.paperCategoryId';
    if (isset(
    $stockId)) $var2 $stockId
    if (
    $stockId$var4 'ausapapersummary.stockId';

    $searchDb = array($var3$var4);
    $search = array($var1$var2);

    for(
    $x 0$x<count($searchDb); $x++) {
        
    $query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
        } 

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Where is it that your variables are comming from? Is it a form text field?
    If it is, the problem is here:
    PHP Code:
     if (isset($paperCategoryId)) $var1 $paperCategoryId;  
    if (
    $paperCategoryId 0$var3 'ausapapersummary.paperCategoryId'
    if (isset(
    $stockId)) $var2 $stockId;  
    if (
    $stockId$var4 'ausapapersummary.stockId'
    Where you use isset() functions, change them to !empty() instead. As mentioned, text fields send a variable when empty, but no value.
    Like so:
    PHP Code:
    if (!empty($paperCategoryId))
    {
         
    $var1 $paperCategoryId;
         
    $var3 'ausapapersummary.paperCategoryID';
    }
    if (!empty(
    $stockId))
    {
         
    $var2 $stockId;
         
    $var4 'ausapapersummary.stockId';

    I've also removed the if ($paperCategoryId > 0) section. It won't hurt to send it, as it will ignore it within the SQL.
    This should fix the problem for you, stressing still that I'm expecting this data from a form. Should this be true, and you still experience similar/no output, please ensure that $paperCategoryId and $stockId exist, and have been extracted from the _POST superglobal (register_globals off).
    Then get back to me if you have problems.
    I'd still recommend a roadmap route like mine. This will allow you to create as many fields as you want, without adding additional script code for hardcoding. After you have yours working, give mine a shot but add all 14 fields to your form -> you'll see that it works peachy

  • #8
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks again.
    the data is coming from a form.
    more precisely many drop down menu's.
    the data echo's back ok and all variables are set etc.

    the whole code looks more like :

    PHP Code:

    $query 
    "SELECT ausapapersummary.summaryId, ausapapersummary.paperCategoryId, aupapercategory.paperCategory, ausapapersummary.colloPaperName, ausapapersummary.manufacturerName, 
    ausapapersummary.cpl, ausapapersummary.stockId, austock.stockDescription, ausapapersummary.adhesiveId, auadhesive.adhesiveDescription, auliner.linerDescription, 
    ausapapersummary.linerId, ausapapersummary.supplierId, ausupplier.supplier FROM ausapapersummary "
    ;

    $query .="LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
    LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId
    LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId
    LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId
    LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId WHERE 1=1 "
    ;

    $var1 0;
    $var2 0;
    $var3 0;
    $var4 0;
    $var5 0;
    $var6 0;

    if (!empty(
    $paperCategoryId)) {
         
    $var1 $paperCategoryId;
         
    $var2 'ausapapersummary.paperCategoryID';
        }

    if (!empty(
    $stockId)) {
         
    $var3 $stockId;
         
    $var4 'ausapapersummary.stockId';
        }

    if (!empty(
    $adhesiveId)) {
        
    $var5 $adhesiveId;
        
    $var6 'ausapapersummary.adhesiveId';
        }

    $searchDb = array($var2$var4$var6);
    $search = array($var1$var3$var5);

    for(
    $x 0$x<count($searchDb); $x++) {
        
    $query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
        } 
    so now my rendered SQL command looks like :

    SELECT ausapapersummary.summaryId, ausapapersummary.paperCategoryId, aupapercategory.paperCategory, ausapapersummary.colloPaperName, ausapapersummary.manufacturerName, ausapapersummary.cpl, ausapapersummary.stockId, austock.stockDescription, ausapapersummary.adhesiveId, auadhesive.adhesiveDescription, auliner.linerDescription, ausapapersummary.linerId, ausapapersummary.supplierId, ausupplier.supplier FROM ausapapersummary LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId WHERE 1=1 AND 0 = 0 AND 0 = 0 AND ausapapersummary.adhesiveId = 25

    AND 0 = 0 sin't a bad line to have in a statement??

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yeah it is, sorry I thought you meant that it was searching for field = '0', which would be ok (so long as if you have values under '0' your happy with adding them )
    Simply alter which ever ones you need here:
    PHP Code:
    if (!empty($paperCategoryId) AND $paperCategoryId 0) {
         
    $var1 $paperCategoryId
         
    $var2 'ausapapersummary.paperCategoryID'
        } 
    If $paperCategoryId is being defined before this, you can actually drop the !empty() section completely, and evaluate its value instead. This only works (without notice) if $paperCategoryId is defined before:
    eg:
    $paperCategoryId = $_POST['paperCategoryId']; for example.
    Make sure as well that you clean values using mysql_real_escape_string() function prior to running the query.
    If you want to use my example however, we'll need to alter the way the information is gathered into a more indepth multidimensional array to make good use of the table aliasing and JOINS. Sorry, never realized these were on seperate tables before -> mine won't work 'peachy'
    Give that a shot.

  • #10
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The 0=0 at the end of my SQL is working ok.
    So could you inform me as to why this is not good?

    Also the 0=0 is getting added because I can't work out how to stop the line from being created. Any ideas?

    PHP Code:

    $var1 
    0;
    $var2 0;
    $var3 0;
    $var4 0;
    $var5 0;
    $var6 0;

    if (!empty(
    $paperCategoryId) AND $paperCategoryId 0) { 
         
    $var1 $paperCategoryId;
         
    $var2 'ausapapersummary.paperCategoryID';
        }

    $searchDb = array($var2$var4$var6);
    $search = array($var1$var3$var5);

    for(
    $x 0$x<count($searchDb); $x++) {
        
    $query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
        } 

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Hmm, I guess that 0=0 does evalutate to (bool) true, so I suppose it won't be a trouble to leave them in there. I've just always used 1=1 for my initial conditions, I assumed that 0=0 would be problematic.

    I should be paying more attention to your variable initialization, thats where its sitting. The zero's I thought were comming from the form, so you probably won't need to make use of the $variable > 0 section either.
    Try initializing your variables to an empty string and see how that works:
    PHP Code:
    $var1 ''
    $var2 ''
    $var3 ''
    $var4 ''
    $var5 ''
    $var6 ''
    If that doesn't clear it up, also add this:
    PHP Code:
    for($x 0$x<count($searchDb); $x++) { 
        if (!empty(
    $searchDb[$x]))
        {
            
    $query .= " AND ".$searchDb[$x]." = ".$search[$x].""
        }

    Or, you can leave your variables initialized with zeros, and check for that:
    PHP Code:
    for($x 0$x<count($searchDb); $x++) { 
        if (
    $searchDb[$x] > 0)
        {
            
    $query .= " AND ".$searchDb[$x]." = ".$search[$x].""
        }

    Still recommend the dynamic route
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #12
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again for you assistance.

    I've taken up your idea of a more dynamic structure.
    Now the only thing I can't work out is how to get the $statement variable to set correctly. It displays

    AND Array = 1 rather than
    AND ausapapersummary.paperCategoryId = 1

    Any ideas?

    PHP Code:
    $query "SELECT ausapapersummary.summaryId, ausapapersummary.paperCategoryId, aupapercategory.paperCategory, ausapapersummary.colloPaperName, ausapapersummary.manufacturerName, 
    ausapapersummary.cpl, ausapapersummary.stockId, austock.stockDescription, ausapapersummary.adhesiveId, auadhesive.adhesiveDescription, auliner.linerDescription, 
    ausapapersummary.linerId, ausapapersummary.supplierId, ausupplier.supplier FROM ausapapersummary "
    ;

    $query .="LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
    LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId
    LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId
    LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId
    LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId WHERE 1=1 "
    ;

    $fields = array('paperCategoryId''manufacturerName''cpl''stockId''adhesiveId''linerId''supplierId''suitabilityFoil''suitabilityYellowLight''suitabilityLabel''suitabilityOpacity''suitabilityBronze''suitabilityScreen''suitabilityIceBucket'); 

    $statements = array('ausapapersummary.paperCategoryId''ausapapersummary.manufacturerName''ausapapersummary.cpl''ausapapersummary.stockId''ausapapersummary.adhesiveId''ausapapersummary.linerId''ausapapersummary.supplierId''ausapapersummary.suitabilityFoil''ausapapersummary.suitabilityYellowLight''''ausapapersummary.suitabilityLabel''ausapapersummary.suitabilityOpacity''ausapapersummary.suitabilityBronze''ausapapersummary.suitabilityScreen''ausapapersummary.suitabilityIceBucket');

    foreach (
    $fields as $field) { if (!empty($_POST[$field]) && $_POST[$field] > 0) { 
        
        
    $query .= " AND ".$statements." = ".$_POST[$field][$x]."";
        
      }
    }

    echo 
    $query

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Ok lets see...
    Instead of using a foreach loop, still use a for loop.
    However, this route may be easier for you in the long run. Its the same principle as my initial code, but with alterations for the use of the table alias within the security checks:
    PHP Code:
    $statement_check = array(
         
    'ausapapersummary' => array ('paperCategoryId''manufacturerName''cpl''stockId''ahesiveId''linerId''supplierId''suitabilityFoil''suitabilityYellowLight''suitabilityLabel''suitabilityOpacity''suitabilityBronze''suitabilityScreen''suitabilityIceBucket'),
    );
    // I noticed that all are under ausapapersummary alias.  Thats ok, I'd still do it this way as it allows easy manipulation to if the alias changes.

    if (is_array($_POST['search'])) 

        foreach (
    $_POST['search'] AS $key => $val
        { 
            
    // Here's the tricker, text fields are always set, even if its null: 
            
    foreach ($statement_check AS $table_alias => $value_check)
            {
                if (!empty(
    $val) AND in_array($key$value_check)) 
                { 
                    
    $query .= ' AND `' $table_alias '.' $key '` = "' mysql_real_escape_string($val) . '"'
                } 
            }
        } 

    Now, this route requires that $_POST['search'] be an array. Which would be like so:
    Code:
    <select name="search[]">
      <options />
    </select>
    <select name="search[]">
      <options />
    </select>
    Or textfields or whatever else you want to use.
    As well, this isn't quite a dynamic query since you've hardcoded so much into the script already - which is fine. This method will also eliminate any notices you may get from uninialized variables or undefined index's as well.
    This method is also untested, so you may want to throw it onto a seperate file to test it first.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #14
    New Coder
    Join Date
    Jul 2005
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've gone down another path.. i hope you don;t mind so much.
    Just one small glitch now. With a switch option.
    Do the bolded lines look right. They don;t appear to be reading the statement.

    PHP Code:
    $fields = array(
        
    'ausapapersummary.paperCategoryId' => 'paperCategoryId',
        
    'ausapapersummary.manufacturerName' => 'manufacturerName',
        
    'ausapapersummary.cpl' => 'cpl',
        
    'ausapapersummary.stockId' => 'stockId',
        
    'ausapapersummary.adhesiveId' => 'adhesiveId',
        
    'ausapapersummary.linerId' => 'linerId',
        
    'ausapapersummary.supplierId' =>  'supplierId',
        
    'ausapapersummary.suitabilityFoil' => 'suitabilityFoil',
        
    'ausapapersummary.suitabilityYellowLight' => 'suitabilityYellowLight',
        
    'ausapapersummary.suitabilityLabel' => 'suitabilityLabel',
        
    'ausapapersummary.suitabilityOpacity' =>  'suitabilityOpacity',
        
    'ausapapersummary.suitabilityBronze' =>  'suitabilityBronze',
        
    'ausapapersummary.suitabilityScreen' =>  'suitabilityScreen',
        
    'ausapapersummary.suitabilityIceBucket' => 'suitabilityIceBucket',
    );

    foreach (
    $fields as $statement => $field) { if (!empty($_POST[$field]) && $_POST[$field] > 0) {     

        
    $action = isset($_GET['action']) ? $_GET['action'] : '';
        
        switch(
    $action) {
    [
    B]    case "ausapapersummary.manufacturerName" $query .= " AND ".$statement." LIKE '%".$_POST[$field]."%'"; break;
        case 
    "ausapapersummary.cpl"$query .= " AND ".$statement." = ".$_POST[$field].""; break;
    [/
    B]    default: $query .= " AND ".$statement." = ".$_POST[$field].""; break;
       }
      
      }


  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    The switch as a function itself is correct, you don't need to break your default, but I don't think that will cause any problems. I'm not certain offhand from looking at it if it will work the way you want to, but in my head its right.

    Escape these ones though:
    $_POST[$field]
    with
    mysql_real_escape_string($_POST[$field]);
    SQL injections are not fun.
    As well, you may want to consider changing this one:
    isset($_GET['action']) ? $_GET['action'] : '';
    to:
    isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
    depending on if action can come from either a URL or through a form.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •