Jump to content

Perform Advanced Search from Multiple field Selections

n1concepts

By n1concepts
in MySQL Help

 Share

Recommended Posts

n1concepts Regular Member

n1concepts

Posted
Posted

I need some help building a combined subquery based on a mix of fields (see below and exclude the CSS tags during review - this is the actual form showing all the fields).

 

Issue: b/c some of the fields are "checkboxes", I'm not sure how to handle those using the "IN" operator.

Note: I'm not sure how to pass any of the previous results into the next query using the "IN" operator - that's what I'm not understanding.

 

Any help building a search function (for a PHP script) to query based on all fields appreciated as I'm completely lost!

Let me know if you need any additional info and I'll provide - in private message if access required.

 

thanks!

 

* Form code is below and the objective of the form is to query a "Jobs" database and display the title and description from each record:

 

<div id="advQueryForm"><form method="post" action="work-at-home-jobs-test.php"  id="searchform" enctype="multipart/form-data" name="advsearch">
<div id="topSearchbtn"><input name="submit" class="searchBtn" type="submit" value="Search" /></div>

<div class="bold14s25">Job Title<br />
   <input name="job_title" type="text" tabindex="5" class="advInput" />
</div>

  <div class="bold14s25">Total Years of Experience<br />
   <select name="exp_yrs[]" class="advInput" tabindex="10">
     <option value="Select One" selected="selected">Select One</option>
     <option value="0">none</option>
     <option value="1">1 year</option>
     <option value="2">2 years</option>
     <option value="3">3 years</option>
     <option value="4">4 years</option>
     <option value="5">5 years</option>
     <option value="6">6 years</option>
     <option value="7">7 years</option>
     <option value="8">8 years</option>
     <option value="9">9 years</option>
     <option value="10">10 years</option>
     <option value="11">11 years</option>
     <option value="12">12 years</option>
     <option value="13">13 years</option>
     <option value="14">14 years</option>
     <option value="15">15+years</option>
   </select>
</div>


   <div class="bold14s25">Skills / Keywords<br />
   <input name="skills" type="text" class="advInput" tabindex="15" />
</div>


   <div class="bold14s25">Location<br />
   <input name="location" type="text" class="advInput" tabindex="20" />
</div>


   <div class="bold14s25">Company<br />
   <input name="company" type="text" class="advInput" tabindex="25" />
</div>


   <div class="bold14s15">Industries<br />
   <select name="industries1[]" class="advInput" tabindex="30">
     <option value="select" selected="selected">1st Industry</option>
     <option value="accounting1">Accounting & Auditing Services</option>
     <option value="advertise1">Advertising & PR Services</option>
     <option value="aerospace1">Aerospace & Defense</option>
     <option value="agricult1">Agriculture/Forestry/Fishing</option>
     <option value="architect1">Architectural & Design Services</option>
     <option value="autoparts1">Automotive & Parts Mfg</option>
     <option value="autosales1">Automotive Sales & Repair</option>
     <option value="Banking1">Banking</option>
     <option value="biotech1">Biotechnology/Pharmaceuticals</option>
     <option value="broadcast1">Broadcasting, Music, & Film</option>
     <option value="other1">Business Service - Other</option>
     <option value="chemicals1">Chemicals/Petro-Chemicals</option>
     <option value="clothing1">Clothing & Textile Manufacturing</option>
     <option value="hardware1">Computer Hardware</option>
     <option value="software1">Computer Software</option>
     <option value="computers1">Computer/IT Services</option>
     <option value="industrial1">Construction - Industrial Facilities & Infrastructure</option>
     <option value="resconstruct1">Construction - Residential & Commercial/Office</option>
     <option value="manufact1">Consumer Packaged Goods Manufacturing</option>
     <option value="education1">Education</option>
     <option value="electronics1">Electronics, Components, & Semiconductor</option>
     <option value="utils1">Energy & Utilities</option>
     <option value="engineering1">Engineering Services</option>
     <option value="entertain1">Entertainment Venues & Theaters</option>
     <option value="financial1">Financial Services</option>
     <option value="food1">Food & Beverage Production</option>
     <option value="govmil1">Government & Military</option>
     <option value="healthcare1">Healthcare Services</option>
     <option value="hotels1">Hotels & Lodging</option>
     <option value="insurance1">Insurance</option>
     <option value="internet1">Internet Services</option>
     <option value="legal1">Legal</option>
     <option value="consult1">Management Consulting Services</option>
     <option value="manufacture1">Manufacturing - Other</option>
     <option value="marine1">Marine Mfg & Services</option>
     <option value="medsupplies1">Medical Devices & Supplies</option>
     <option value="metals1">Metals & Minerals</option>
     <option value="nonprofit1">Nonprofit Charitable Organizations</option>
     <option value="notclass1">Other/Not Classified</option>
     <option value="finearts1">Fine Arts</option>
     <option value="household1">Household Services</option>
     <option value="print1">Printing & Publishing</option>
     <option value="realestate1">Real Estate/Property Management</option>
     <option value="rental1">Rental Services</option>
     <option value="foodser1">Restaurant/Food Services</option>
     <option value="retail1">Retail</option>
     <option value="security1">Security & Surveillance</option>
     <option value="sports1">Sports & Physical Recreation</option>
     <option value="employment1">Employment Agencies</option>
     <option value="telecom1">Telecommunications Services</option>
     <option value="transport1">Transport & Storage - Materials</option>
     <option value="travel1">Travel, Transportation & Tourism</option>
     <option value="wastemgt1">Waste Management</option>
     <option value="trade1">Wholesale Trade/import-Export</option>
   </select><br />
   <div class="spacer3">
     <select name="industries2[]" class="advInput" tabindex="30">
       <option value="select" selected="selected">2nd Industry</option>
       <option value="accounting2">Accounting & Auditing Services</option>
       <option value="advertise2">Advertising & PR Services</option>
       <option value="aerospace2">Aerospace & Defense</option>
       <option value="agricult2">Agriculture/Forestry/Fishing</option>
       <option value="architect2">Architectural & Design Services</option>
       <option value="autoparts2">Automotive & Parts Mfg</option>
       <option value="autosales2">Automotive Sales & Repair</option>
       <option value="Banking2">Banking</option>
       <option value="biotech2">Biotechnology/Pharmaceuticals</option>
       <option value="broadcast2">Broadcasting, Music, & Film</option>
       <option value="other2">Business Service - Other</option>
       <option value="chemicals2">Chemicals/Petro-Chemicals</option>
       <option value="clothing2">Clothing & Textile Manufacturing</option>
       <option value="hardware2">Computer Hardware</option>
       <option value="software2">Computer Software</option>
       <option value="computers2">Computer/IT Services</option>
       <option value="industrial2">Construction - Industrial Facilities & Infrastructure</option>
       <option value="resconstruct2">Construction - Residential & Commercial/Office</option>
       <option value="manufact2">Consumer Packaged Goods Manufacturing</option>
       <option value="education2">Education</option>
       <option value="electronics2">Electronics, Components, & Semiconductor</option>
       <option value="utils2">Energy & Utilities</option>
       <option value="engineering2">Engineering Services</option>
       <option value="entertain2">Entertainment Venues & Theaters</option>
       <option value="financial2">Financial Services</option>
       <option value="food2">Food & Beverage Production</option>
       <option value="govmil2">Government & Military</option>
       <option value="healthcare2">Healthcare Services</option>
       <option value="hotels2">Hotels & Lodging</option>
       <option value="insurance2">Insurance</option>
       <option value="internet2">Internet Services</option>
       <option value="legal2">Legal</option>
       <option value="consult2">Management Consulting Services</option>
       <option value="manufacture2">Manufacturing - Other</option>
       <option value="marine2">Marine Mfg & Services</option>
       <option value="medsupplies2">Medical Devices & Supplies</option>
       <option value="metals2">Metals & Minerals</option>
       <option value="nonprofit2">Nonprofit Charitable Organizations</option>
       <option value="notclass2">Other/Not Classified</option>
       <option value="finearts2">Fine Arts</option>
       <option value="household2">Household Services</option>
       <option value="print2">Printing & Publishing</option>
       <option value="realestate2">Real Estate/Property Management</option>
       <option value="rental2">Rental Services</option>
       <option value="foodser2">Restaurant/Food Services</option>
       <option value="retail2">Retail</option>
       <option value="security2">Security & Surveillance</option>
       <option value="sports2">Sports & Physical Recreation</option>
       <option value="employment2">Employment Agencies</option>
       <option value="telecom2">Telecommunications Services</option>
       <option value="transport2">Transport & Storage - Materials</option>
       <option value="travel2">Travel, Transportation & Tourism</option>
       <option value="wastemgt2">Waste Management</option>
       <option value="trade2">Wholesale Trade/import-Export</option>
     </select>
   </div>
   <div class="spacer3">
   <select name="industries3[]" class="advInput" tabindex="30">
     <option value="select" selected="selected">3rd Industry</option>
     <option value="accounting3">Accounting & Auditing Services</option>
     <option value="advertise3">Advertising & PR Services</option>
     <option value="aerospace3">Aerospace & Defense</option>
     <option value="agricult3">Agriculture/Forestry/Fishing</option>
     <option value="architect3">Architectural & Design Services</option>
     <option value="autoparts3">Automotive & Parts Mfg</option>
     <option value="autosales3">Automotive Sales & Repair</option>
     <option value="Banking3">Banking</option>
     <option value="biotech3">Biotechnology/Pharmaceuticals</option>
     <option value="broadcast3">Broadcasting, Music, & Film</option>
     <option value="other3">Business Service - Other</option>
     <option value="chemicals3">Chemicals/Petro-Chemicals</option>
     <option value="clothing3">Clothing & Textile Manufacturing</option>
     <option value="hardware3">Computer Hardware</option>
     <option value="software3">Computer Software</option>
     <option value="computers3">Computer/IT Services</option>
     <option value="industrial3">Construction - Industrial Facilities & Infrastructure</option>
     <option value="resconstruct3">Construction - Residential & Commercial/Office</option>
     <option value="manufact3">Consumer Packaged Goods Manufacturing</option>
     <option value="education3">Education</option>
     <option value="electronics3">Electronics, Components, & Semiconductor</option>
     <option value="utils3">Energy & Utilities</option>
     <option value="engineering3">Engineering Services</option>
     <option value="entertain3">Entertainment Venues & Theaters</option>
     <option value="financial3">Financial Services</option>
     <option value="food3">Food & Beverage Production</option>
     <option value="govmil3">Government & Military</option>
     <option value="healthcare3">Healthcare Services</option>
     <option value="hotels3">Hotels & Lodging</option>
     <option value="insurance3">Insurance</option>
     <option value="internet3">Internet Services</option>
     <option value="legal3">Legal</option>
     <option value="consult3">Management Consulting Services</option>
     <option value="manufacture3">Manufacturing - Other</option>
     <option value="marine3">Marine Mfg & Services</option>
     <option value="medsupplies3">Medical Devices & Supplies</option>
     <option value="metals3">Metals & Minerals</option>
     <option value="nonprofit3">Nonprofit Charitable Organizations</option>
     <option value="notclass3">Other/Not Classified</option>
     <option value="finearts3">Fine Arts</option>
     <option value="household3">Household Services</option>
     <option value="print3">Printing & Publishing</option>
     <option value="realestate3">Real Estate/Property Management</option>
     <option value="rental3">Rental Services</option>
     <option value="foodser3">Restaurant/Food Services</option>
     <option value="retail3">Retail</option>
     <option value="security3">Security & Surveillance</option>
     <option value="sports">Sports & Physical Recreation</option>
     <option value="employment3">Employment Agencies</option>
     <option value="telecom3">Telecommunications Services</option>
     <option value="transport3">Transport & Storage - Materials</option>
     <option value="travel3">Travel, Transportation & Tourism</option>
     <option value="wastemgt3">Waste Management</option>
     <option value="trade3">Wholesale Trade/import-Export</option>
   </select></div>
   <!--- End of "bold14s15" Class for Industries --->
</div><br />

    <div class="bold14s25">Job Post Dates<br/>
    <select name="job_dates[]" tabindex="35" class="advInput">
      <option value="today" selected="selected">Today</option>
      <option value="yesterday">Yesterday</option>
      <option value="3days">Last 3 Days</option>
      <option value="7days">Last 7 Days</option>
      <option value="14days">Last 14 Days</option>
      <option value="30days">Last 30 Days</option>
      <option value="60days">Last 60 Days</option>
    </select>
    </div>

   <div class="bold14s25">Job Type<br />

     <div class="column1">
       <label>
         <input type="checkbox" name="job_type[]" value="Full Time" id="job_type1" />
         Full Time</label>
       <br />
       <label>
         <input type="checkbox" name="job_type[]" value="Contract" id="job_type2" />
         Contract</label>
       <br />
       <label>
         <input type="checkbox" name="job_type[]" value="Part Time" id="job_type3" />
         Part Time</label>
       </div>
       
       <div class="column2">
       <label>
         <input type="checkbox" name="job_type[]" value="Internship" id="job_type4" />
         Internship</label>
       <br />
       <label>
           <input type="checkbox" name="job_type[]" value="Temp" id="job_type5" />
         Temp</label>
       <br />
       <label>
         <input type="checkbox" name="job_type[]" value="Other" id="job_type6" />
         Other</label>
       </div>
     
   </div>




   <div class="bold14s25">Education Level<br />

     <div class="edu1">
       <label>
         <input type="checkbox" name="edu[]" value="Advanced Degree" id="education1" />
         Advanced Degree</label>
       <br />
       <label>
         <input type="checkbox" name="edu[]" value="Bachelor's Degree" id="education2" />
         Bachelor's Degree</label>
       </div>
       
       <div class="edu2">
       <label>
         <input type="checkbox" name="edu[]" value="Internship" id="education3" />
         Associciate Degree</label>
       <br />
       <label>
           <input type="checkbox" name="edu[]" value="Temp" id="education4" />
           High School
       </label>
       </div>
     
   </div>


   <div class="bold14s25">Career Level<br />

     <div class="career1">
       <label>
         <input type="checkbox" name="career[]" value="highSchool" id="career1" />
         Student(High School)</label>
       <br />
       <label>
         <input type="checkbox" name="career[]" value="underGrad" id="career2" />
         Student (Undergraduate/Graduate)</label>
       <br />
       <label>
         <input type="checkbox" name="career[]" value="entryLevel" id="career3" />
         Entry Level</label>
       </div>
       
       <div class="career2">
       <label>
         <input type="checkbox" name="career[]" value="mgr" id="cereer4" />
         Manager (Manager/Supervisor of Staff)</label>
       <br />
       <label>
           <input type="checkbox" name="career[]" value="exec1" id="career5" />
         Executive (SVP, VP, Department Head, etc)</label>
       <br />
       <label>
         <input type="checkbox" name="career[]" value="exec2" id="career6" />
         Senior Executive (President, CFO, etc)</label>
       </div>
     
   </div>


   <div class="bold14s25">Career Categories<br />
   <select name="categories[]" class="advInput">
     <option value="select" selected="selected">1st Category</option>
     <option value="accounting1">Accounting/Finance/Insurance</option>
     <option value="admin1">Administrative/Clerical</option>
     <option value="banking1">Banking/Real Estate/Mortgage Professional</option>
     <option value="biotech1">Biotech/R&D/Science</option>
     <option value="construct1">Building Construction/Skilled Trades</option>
     <option value="business1">Business/Strategic Management</option>
     <option value="design1">Creative Design</option>
     <option value="support1">Customer Support/Client Care</option>
     <option value="writing1">Editorial/Writing</option>
     <option value="education1">Education/Training</option>
     <option value="engineer1">Engineering</option>
     <option value="foodser1">Food Services/Hospitality</option>
     <option value="humanres">Human Resources</option>
     <option value="maint">Installation/Maintenance/Repair</option>
     <option value="software1">IT/Software Development</option>
     <option value="legal1">Legal</option>
     <option value="logistics1">Logistics/Transportation</option>
     <option value="manufact1">Manufacturing/Production/Operations</option>
     <option value="marketing1">Marketing/Product</option>
     <option value="medical1">Medical/Health</option>
     <option value="other1">Other</option>
     <option value="prjtmgt1">Project/Program Management</option>
     <option value="qasafety1">Quality Assurance/Safety</option>
     <option value="bizdevelop1">Sales/Retail/Business Development</option>
     <option value="security1">Security/Protective Services</option>
   </select> 
   
   
   <br />
   <div class="spacer3">
   <select name="categories[]" class="advInput">
     <option value="select" selected="selected">2nd Category</option>
     <option value="accounting2">Accounting/Finance/Insurance</option>
     <option value="admin2">Administrative/Clerical</option>
     <option value="banking2">Banking/Real Estate/Mortgage Professional</option>
     <option value="biotech2">Biotech/R&D/Science</option>
     <option value="construct2">Building Construction/Skilled Trades</option>
     <option value="business2">Business/Strategic Management</option>
     <option value="design2">Creative Design</option>
     <option value="support2">Customer Support/Client Care</option>
     <option value="writing2">Editorial/Writing</option>
     <option value="education2">Education/Training</option>
     <option value="engineer2">Engineering</option>
     <option value="foodser2">Food Services/Hospitality</option>
     <option value="humanres">Human Resources</option>
     <option value="maint">Installation/Maintenance/Repair</option>
     <option value="software2">IT/Software Development</option>
     <option value="legal2">Legal</option>
     <option value="logistics2">Logistics/Transportation</option>
     <option value="manufact2">Manufacturing/Production/Operations</option>
     <option value="marketing2">Marketing/Product</option>
     <option value="medical2">Medical/Health</option>
     <option value="other2">Other</option>
     <option value="prjtmgt2">Project/Program Management</option>
     <option value="qasafety2">Quality Assurance/Safety</option>
     <option value="bizdevelop2">Sales/Retail/Business Development</option>
     <option value="security2">Security/Protective Services</option></select>
   </div>
   <div class="spacer3">
     <select name="categories[]" class="advInput">
     <option value="select" selected="selected">3rd Category</option>
     <option value="accounting2">Accounting/Finance/Insurance</option>
     <option value="admin2">Administrative/Clerical</option>
     <option value="banking2">Banking/Real Estate/Mortgage Professional</option>
     <option value="biotech2">Biotech/R&D/Science</option>
     <option value="construct2">Building Construction/Skilled Trades</option>
     <option value="business2">Business/Strategic Management</option>
     <option value="design2">Creative Design</option>
     <option value="support2">Customer Support/Client Care</option>
     <option value="writing2">Editorial/Writing</option>
     <option value="education2">Education/Training</option>
     <option value="engineer2">Engineering</option>
     <option value="foodser2">Food Services/Hospitality</option>
     <option value="humanres">Human Resources</option>
     <option value="maint">Installation/Maintenance/Repair</option>
     <option value="software2">IT/Software Development</option>
     <option value="legal2">Legal</option>
     <option value="logistics2">Logistics/Transportation</option>
     <option value="manufact2">Manufacturing/Production/Operations</option>
     <option value="marketing2">Marketing/Product</option>
     <option value="medical2">Medical/Health</option>
     <option value="other2">Other</option>
     <option value="prjtmgt2">Project/Program Management</option>
     <option value="qasafety2">Quality Assurance/Safety</option>
     <option value="bizdevelop2">Sales/Retail/Business Development</option>
     <option value="security2">Security/Protective Services</option></select>
   </div>
     <!--- End of "bold14s15" Class for Job Category --->
</div>

<div id="bottomSearchbtn"><input name="submit" class="searchBtn" type="submit" value="Search" /></div>

<!--- End "advQueryForm" DIV tag --->
</form></div>

 

 

Link to comment
Share on other sites
n1concepts Regular Member

n1concepts

Posted
  • Author
Posted

FYI: I think I can build the query with one REALLY long "SELECT" statement if I could just figure out how to insert the "checked" options from the checkbox fields into the "IN" operator.

 

I tried IMPLODING the captured array variables into the variable assignment as shown below, but get errors:

 

   $jsearch = $_POST['job_title'];
    echo "<strong>Search Results for:</strong> ".$jsearch."<br /></br />";

// Break up string to search individual words
$search_array = explode(" ", $jsearch);
$fword = $search_array[0];
$fcheck = ucfirst($fword);
$sword = $search_array[1];
$scheck = ucfirst($sword);

  //-query the database table on First word
  $title_search="SELECT title FROM jobs WHERE title LIKE '%$fcheck%';";
//-run  the query against the mysql query function
  $title_results=mysql_query($title_search) or die("<br>Query string: $title_results<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($title_results)){
          $title=$row['title'];
          echo $title."<br />";

          $title_array[] = $title;
}

// Filter based on Years of experience
$experience = $_POST['exp_yrs'];
    echo "<strong>Years of Experience from form:</strong> ".$exp_yrs."<br /><br />";

    // Structure title results to pass into below query
    $title_list = implode(",",$title_array);
    echo "The title variables are: ".$title_list."<br /><br />";
    // query database based on above title results then filtered on years of experience
    $yr_search = "SELECT title, exp_yrs from jobs WHERE title IN ($title_list);";
    //-run  the query against the mysql query function
  $yr_results=mysql_query($yr_search) or die("<br>Query string: $yr_results<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($yr_results)){
          $title=$row['title'];
          echo $title."<br />";
          $exp_yrs = $row['exp_yrs'];
          echo $exp_yrs."<br />";

}

Link to comment
Share on other sites
n1concepts Regular Member

n1concepts

Posted
  • Author
Posted

BTW: Here's the section of the form that relates to the code I just posted - previously:

----

 

<div class="bold14s25">Job Title<br />
   <input name="job_title" type="text" tabindex="5" class="advInput" />
</div>

  <div class="bold14s25">Total Years of Experience<br />
   <select name="exp_yrs[]" class="advInput" tabindex="10">
     <option value="Select One" selected="selected">Select One</option>
     <option value="0">none</option>
     <option value="1">1 year</option>
     <option value="2">2 years</option>
     <option value="3">3 years</option>
     <option value="4">4 years</option>
     <option value="5">5 years</option>
     <option value="6">6 years</option>
     <option value="7">7 years</option>
     <option value="8">8 years</option>
     <option value="9">9 years</option>
     <option value="10">10 years</option>
     <option value="11">11 years</option>
     <option value="12">12 years</option>
     <option value="13">13 years</option>
     <option value="14">14 years</option>
     <option value="15">15+years</option>
   </select>
</div>

 

If I can figure out the subquery to combine these two together to produce the desired results I think I can figure out the rest.

Q: how to combine the two fields into subquery to search db based on "text" and "checkbox" field where not all checkboxes may be selected.

 

Note: I also have some dropdown menu fields but those can be handled as each only pass one piece of data for each.

Again, I'm not sure how to pass the results of previous query into the next subquery using the "IN" operator.

 

Any advise surely appreciated to defined a search based on all the fields in the form - see initial post.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.