#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0

    Multi-Selection Search


    I have a small database that I want users to be able to search by experience type and skill level. I have a form already created that has all 32 experience types and all 6 skill levels. I'm stuck at being able to select multiple from either sections and getting results. I can to one experience type and one skill level and I get results, but I need for the users to be able to search for anything they want.

    For example if they pick ET1 and ET21 (experience type) plus they want SK4 and SK5 (skill level) for each how would I create something in PHP that'll happen dynamic regardless of which options they select.

    I tried messing around with the below script but I'm not able to get it to do what I want:
    Code:
    $sql="";
    $search_query=array();
    if(trim($_POST['experience1'])!=="")$search_query[]=" print_reading = '$_POST[skill1]' ";
    if(trim($_POST['experience2'])!=="")$search_query[]=" layout = '$_POST[experience2]' ";
    if(trim($_POST['experience3'])!=="")$search_query[]=" wood_framing = '$_POST[experience3]' ";
    if(trim($_POST['experience4'])!=="")$search_query[]=" hollow_metal_frame_install = '$_POST[experience4]' ";
    if(trim($_POST['experience5'])!=="")$search_query[]=" lead_lined_sheetrock = '$_POST[experience5]' ";
    if(trim($_POST['experience6'])!=="")$search_query[]=" ext_light_gauge_framing = '$_POST[experience6]' ";
    $boxes=count($search_query);
    if($boxes==1)
    {
            $detail=implode(" ",$search_query);
            $sql="Where $detail";
    }
    if($boxes>1)
    {
            $sql="Where ";
            $detail=implode(" and ",$search_query);
            $sql.=$detail;
    }
    
    $result = mysql_query("SELECT * FROM emp $sql ORDER By lname");
    any help would be greatly appreciated!
    Last edited by gremster; December 12th, 2012 at 09:20 AM. Reason: Code Tag
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,016
    Rep Power
    376
    thats a wrong way to go about it.

    the input forms should be called "experience[]" and skills forms should be called skills[].

    then on post, treat them as arrays.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by paulh1983
    thats a wrong way to go about it.

    the input forms should be called "experience[]" and skills forms should be called skills[].

    then on post, treat them as arrays.
    Thanks for that tip, I'm new at this so any and all help is greatly appreciated. I made the changes to my form you suggested and created the below just to test the output and everything is working properly.

    Code:
    foreach ($_POST['experience'] as $evalue)
       {
       echo $evalue . "<br>";
       }
    
    foreach ($_POST['skill'] as $svalue)
       {
       echo $svalue . "<br>";
       }
    I just don't know how to create the $result = mysql_query("SELECT * FROM emp $sql ORDER By lname"); statement since I don't know what the user will select. Each experience will be an AND and each skill an OR. I just don't know how to create that could you assist? Thanks in advance for an assistance you can offer.

    I got this going for me right now and it gives me the AND's that I need when selecting multiple experience selections:
    Code:
    $sql="";
    $post_exp=$_POST['experience'];
    $boxes=count($post_exp);
    if($boxes==1)
    {
            $detail=implode(" ",$post_exp);
            $sql="Where $detail";
    }
    if($boxes>1)
    {
            $sql="Where ";
            $detail=implode(" and ",$post_exp);
            $sql.=$detail;
    }
    
    echo $sql;
    Now I'm trying to figure out how to add the = SKILL for each $post_exp.

    Well this works perfectly for my one to one .. one EXP & one SKILL selected. Still trying to figure out the multi-selection.
    Code:
    // Multi-Variable Search **START**
    $sql="";
    $post_exp=$_POST['experience'];
    $post_skl=$_POST['skill'];
    $eboxes=count($post_exp);
    $sboxes=count($post_skl);
    if($eboxes==1 && $sboxes==1)
    {
    		$post_exp=implode(" ", $post_exp);
    		$post_skl=implode(" ", $post_skl);
            $sql="Where $post_exp = '$post_skl'";
    }
    if($eboxes>1)
    {
            $sql="Where ";
            $detail=implode(" and ",$post_exp);
            $sql.=$detail;
    }
    
    echo $sql;
    
    $result = mysql_query("SELECT * FROM emp $sql ORDER By lname");
    Last edited by gremster; December 12th, 2012 at 09:22 AM. Reason: Code Tag
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    Well, it looks your on the right track. As long as you have your array being built correctly, you can run a simple loop to build your SQL statement.

    The main thing I am wondering is your experience and skill level items. Are these related in any way? Can someone just select a skill level and everything with that skill level is select? Or is it more related to the experience type? i.e. wood framing with a skill level of 3.

    EDIT: Sorry. I now caught you relating the two, BUT MUST a skill level be selected? Or can that be left blank?
    Last edited by Triple_Nothing; December 11th, 2012 at 11:15 AM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Triple_Nothing
    Well, it looks your on the right track. As long as you have your array being built correctly, you can run a simple loop to build your SQL statement.

    The main thing I am wondering is your experience and skill level items. Are these related in any way? Can someone just select a skill level and everything with that skill level is select? Or is it more related to the experience type? i.e. wood framing with a skill level of 3.

    EDIT: Sorry. I now caught you relating the two, BUT MUST a skill level be selected? Or can that be left blank?
    Each EXP (experience) can have up to 6 different SKL (skills) and I want the user to be able to select any EXP and any SKL and get some results. So in theory they can select all 36 EXP and just 1 SKL or 1 EXP and 6 SKL and everywhere in between. I'm doing my best piecing things together and trying to get it to work.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    Perhaps something similare to the final statement will help:

    Code:
    SELECT DISTINCT * FROM emp WHERE (experience_col, skill_col) IN ( ($_POST['experience'],$_POST['skill']), ($_POST['experience'],$_POST['skill']), ... )
    You will just need to write the loop to build the part after the IN. I would probably go with a for() loop with an internal if statement to make first item listed normaly, and the else provides the same, but with a preceding comma and space.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    Ok. My statement won't work exactly for that, but the idea is still the same. I was just thinking skills were set per experience. What kind of array do you have built for your skill items?
    Last edited by Triple_Nothing; December 11th, 2012 at 06:04 PM.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Triple_Nothing
    Ok. My statement won't work exactly for that, but the idea is still the same. I was just thinking 1 skill per experience. What kind of array do you have built for your skill items?
    Below is my form I'm using:
    Code:
    <form id="search" name="search" method="post" action="db_search.php">
          <table width="100%" border="0" cellpadding="0" cellspacing="0">
            <tr>
              <td colspan="4"><font color="#EA0079">Select Skill Level(s):</font></td>
              </tr>
            <tr>
              <td width="4%"><input type="checkbox" name="skill[]" value="NO EXPERIENCE" /></td>
              <td width="29%">No Experience</td>
              <td width="4%"><input type="checkbox" name="skill[]" value="EXPERIENCED" /></td>
              <td width="63%">Experienced</td>
            </tr>
            <tr>
              <td><input type="checkbox" name="skill[]" value="SOME EXPERIENCE" /></td>
              <td>Some Experience</td>
              <td><input type="checkbox" name="skill[]" value="EXPERT" /></td>
              <td>Expert</td>
            </tr>
            <tr>
              <td><input type="checkbox" name="skill[]" value="INTERESTED IN CERT" /></td>
              <td>Interested in Cert</td>
              <td><input type="checkbox" name="skill[]" value="CERTIFIED" /></td>
              <td>Certified</td>
            </tr>
          </table>
          <br>
          <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td colspan="4"><font color="#EA0079">Select  Skill(s):</font></td>
              </tr>
            <tr>
              <td width="4%"><input type="checkbox" name="experience[]" value="print_reading" /></td>
              <td width="42%">Print Reading</td>
              <td width="5%"><input type="checkbox" name="experience[]" value="layout" /></td>
              <td width="49%">Layout</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="wood_framing" /></td>
              <td>Wood Framing</td>
              <td><input type="checkbox" name="experience[]" value="hollow_metal_frame_install" /></td>
              <td>Hollow Metal Frame Install</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="lead_lined_sheetrock" /></td>
              <td>Lead Lined Sheetrock</td>
              <td><input type="checkbox" name="experience[]" value="ext_light_gauge_framing" /></td>
              <td>Ext Light Gauge Framing</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="standing_seam_panels" /></td>
              <td>Standing Seam Panels</td>
              <td><input type="checkbox" name="experience[]" value="brick_ties" /></td>
              <td>Brick Ties</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="frp_wall_panels" /></td>
              <td>FRP Wall Panels</td>
              <td><input type="checkbox" name="experience[]" value="fabric_wall_ceiling_panels" /></td>
              <td>Fabric Wall Ceiling Panels</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="black_iron" /></td>
              <td>Black Iron</td>
              <td><input type="checkbox" name="experience[]" value="acoustical_ceilings" /></td>
              <td>Acoustical Ceilings</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="security_metal_ceilings" /></td>
              <td>Security Metal Ceilings</td>
              <td><input type="checkbox" name="experience[]" value="security_wall_panels_bulletproof" /></td>
              <td>Security Wall Panels Bulletproof</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="toilet_accessories_partitions" /></td>
              <td>Toilet Accessories Partitions</td>
              <td><input type="checkbox" name="experience[]" value="door_hardware" /></td>
              <td>Door Hardware</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="millwork" /></td>
              <td>Millwork</td>
              <td><input type="checkbox" name="experience[]" value="laser_operation" /></td>
              <td>Laser Operation</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="scissorlift" /></td>
              <td>Scissorlift</td>
              <td><input type="checkbox" name="experience[]" value="boomlift" /></td>
              <td>Boomlift</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="shop_steward" /></td>
              <td>Shop Steward</td>
              <td><input type="checkbox" name="experience[]" value="laser" /></td>
              <td>Laser</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="interior_framing_metal" /></td>
              <td>Interior Framing Metal</td>
              <td><input type="checkbox" name="experience[]" value="drywall" /></td>
              <td>Drywall</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="ext_panel_systems" /></td>
              <td>Ext Panel Systems</td>
              <td><input type="checkbox" name="experience[]" value="frg_and_cfrg_columns_moldings" /></td>
              <td>FRG &amp; CFRG Columns Moldings</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="core_board_systems" /></td>
              <td>Core Board Systems</td>
              <td><input type="checkbox" name="experience[]" value="concealing_spline_ceilings" /></td>
              <td>Concealing Spline Ceilings</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="fireproofing_hilti_type" /></td>
              <td>Fireproofing Hilti Type</td>
              <td><input type="checkbox" name="experience[]" value="cabinet_installation" /></td>
              <td>Cabinet Installation</td>
              </tr>
            <tr>
              <td><input type="checkbox" name="experience[]" value="signals_loading_boom_crane" /></td>
              <td>Signals Loading Boom Crane</td>
              <td><input type="checkbox" name="experience[]" value="welding" /></td>
              <td>Welding</td>
              </tr>
          </table>
          <br>
          <table width="100%" border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td width="17%"><input type="submit" name="search" value="Search"></td>
              <td width="83%"><input type="reset" name="reset" value="Reset"></td>
            </tr>
          </table>
          </form>
    
    and if I do the following:
    foreach ($_POST['experience'] as $evalue)
       {
       echo $evalue . "<br>";
       }
    
    foreach ($_POST['skill'] as $svalue)
       {
       echo $svalue . "<br>";
       }
    I see everything that I've select off the form. I just don't how to get that into the WHERE $_POST['experience'] = '$_POST['skill'] .. I'm very new to this so bare with me and any help is greatly appreciated.

    Manually I can get the results I want using below:
    Code:
    $result = mysql_query("SELECT * FROM emp WHERE print_reading IN ('SOME EXPERIENCE','EXPERIENCED','EXPERT') AND layout IN ('SOME EXPERIENCE','EXPERIENCED','EXPERT') AND drywall IN ('SOME EXPERIENCE','EXPERIENCED','EXPERT') ORDER By lname");
    How would I get the items before the IN and after the IN to be based on user selection. The example above is 3 items selected from the EXP (print_reading, layout, drywall) and 3 times from the SKL (some experience, experienced, expert)
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    This should be able to build your string in which you would place between your WHERE and ORDER BY items:

    PHP Code:
    for($i 1$skill ""count($_POST['skill']) <= $i$i++) {
      
    $comma = ($i == "" ",");
      
    $skill .= $comma "'" $_POST['skill'][$i 1] . "'";
    }
    for(
    $i 1$where ""count($_POST['experience']) <= $i$i++) {
      
    $and = ($i == "" " AND ");
      
    $where .= $and $_POST['experience'][$i 1] . " IN (" $skill ")";

    The first for() loop will grab the selected skill levels and build a single variable since that will always be the same. The second does pretty much the same, and uses the $skill variable in each selected experience.

    And, if you don't mind, could you edit your post, highlight your coding, and click the code tag? (#)
    Last edited by Triple_Nothing; December 11th, 2012 at 06:10 PM.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Triple_Nothing
    This should be able to build your string in which you would place between your WHERE and ORDER BY items:

    PHP Code:
    for($i 1$skill ""count($_POST['skill']) <= $i$i++) {
      
    $comma = ($i == "" ",");
      
    $skill .= $comma "'" $_POST['skill'][$i 1] . "'";
    }
    for(
    $i 1$where ""count($_POST['experience']) <= $i$i++) {
      
    $and = ($i == "" " AND ");
      
    $where .= $and $_POST['experience'][$i 1] . " IN (" $skill ")";

    The first for() loop will grab the selected skill levels and build a single variable since that will always be the same. The second does pretty much the same, and uses the $skill variable in each selected experience.

    And, if you don't mind, could you edit your post, highlight your coding, and click the code tag? (#)
    I made the code tag changes, sorry I didn't know I had to do that when I originally posted.

    Quick question if i
    Code:
    echo $where;
    shouldn't I see what the code you created compiled?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    Yes. I hope so. ^_^ What does it print out for you?


    EDIT: I'm sorry, but this is my mistake. I hav 2 items backwards.

    PHP Code:
    count($_POST['skill']) <= $i
    and
    count($_POST['experience']) <= $i 
    are to become

    PHP Code:
    $i <= count($_POST['skill'])
    and
    $i <= count($_POST['experience']) 
    So final edit should appear as follows:

    PHP Code:
    for($i 1$skill ""$i <= count($_POST['skill']); $i++) { 
      
    $comma = ($i == "" ",");
      
    $skill .= $comma "'" $_POST['skill'][$i 1] . "'"

    for(
    $i 1$where ""$i <= count($_POST['experience']); $i++) { 
      
    $and = ($i == "" " AND ");
      
    $where .= $and $_POST['experience'][$i 1] . " IN (" $skill ")"

    Last edited by Triple_Nothing; December 12th, 2012 at 11:02 PM.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Triple_Nothing
    Yes. I hope so. ^_^ What does it print out for you?


    EDIT: I'm sorry, but this is my mistake. I hav 2 items backwards.

    PHP Code:
    count($_POST['skill']) <= $i
    and
    count($_POST['experience']) <= $i 
    are to become

    PHP Code:
    $i <= count($_POST['skill'])
    and
    $i <= count($_POST['experience']) 
    So final edit should appear as follows:

    PHP Code:
    for($i 1$skill ""$i <= count($_POST['skill']); $i++) { 
      
    $comma = ($i == "" ",");
      
    $skill .= $comma "'" $_POST['skill'][$i 1] . "'"

    for(
    $i 1$where ""$i <= count($_POST['experience']); $i++) { 
      
    $and = ($i == "" " AND ");
      
    $where .= $and $_POST['experience'][$i 1] . " IN (" $skill ")"

    Thanks so much, I'm doing testing now and everything appears to be working properly. Can you recommend any books or websites that'll really guide me through learning PHP? Thanks!
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    695
    Rep Power
    7
    Here's a previous topic with some input:

    http://forums.devshed.com/php-develo...hp-935461.html
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by Triple_Nothing
    Here's a previous topic with some input:

    http://forums.devshed.com/php-develo...hp-935461.html
    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo