如何根据用户在PHP / HTML表单中的输入在MySQL Select子句中包含特定的列元素？

Question

I've a simple Inset List (jQuery Mobile) of several items with a Flip switch, Drop down, check box and Slider elements.

I have wrapped this Inset List inside a HTML Form.

<div data-role="fieldcontain">
      <ul data-role="listview" data-inset="true">
        <li data-role="list-divider"> Inhouse capabilities </li>
        <li> <label for="flip-b">Project Manager</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
         </li>   
        <li> <label for="flip-b">Site Supervisor</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
        </li>
         <li><label for="flip-b">Architect</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
        <li><label for="flip-b">Builderworks</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
          <li><label for="flip-b">Electrical</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
          <li><label for="flip-b">Mechanical</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
          <li><label for="flip-b">Hydraulics</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
          <li><label for="flip-b">Joinery</label>
            <select name="slider" id="flip-b" data-role="slider">
                <option value="no">No</option>
                <option value="yes">Yes</option>
            </select>
          </li>
          <li> 
                <label for="select-choice-1" class="select">Main Location:</label>
                   <select name="select-choice-1" id="select-choice-1">
                      <option value="NSW">NSW</option>
                      <option value="ACT">ACT</option>
                      <option value="VIC">VIC</option>
                      <option value="SA">SA</option>
                      <option value="QLD">QLD</option>
                      <option value="NT">NT</option>
                      <option value="WA">WA</option>
                      <option value="TAS">TAS</option>
                    </select>                     
          </li> <!-- Select Menus: Main Location-->

            <li>     
                <div  data-role="fieldcontain">
                <fieldset data-role="controlgroup">
                    <legend>CBA Reference</legend>
                    <input type="checkbox" name="checkbox-1a" id="checkbox-1a" class="custom" />
                    <label for="checkbox-1a">Commercial</label>
                    <input type="checkbox" name="checkbox-2a" id="checkbox-2a" class="custom" />
                    <label for="checkbox-2a">Retail</label>
                </fieldset>
                </div>
            </li>                          
 </ul>             
    </div> <!-- End of Field Contain div tag -->   
 <a href="#additionalinfo" data-role="button" data-inline="true" data-iconpos="left" data-theme="b">Additional Info</a>
<a href="./search2.html" rel="external" data-ajax="false" data-role="button" data-inline="true" data-icon="search" data-iconpos="left" data-theme="e">Search</a>

`

I want to know, how I can include only the elements the user selected in my SQL query.

Lets say, User wants to find the Electrical workers in QLD, then he will select Yes for Electrical and QLD as Main Location and the other items he would not touch.

Now, how can I include only those two values in the select clause.

Say, Select Electrical, Main Location from table where Main location='QLD'.

I used POST method to gather users input. I use PHP, MySQL for this project.

Any ideas on how to achieve this?

Answer 1

I didn't read the fields so you'll have to adapt but it's more of a php question than a mysql question. You'd have to build a list of criterias and then implode them together.

$criterias = array();

if(isset($_POST['option1']) && $_POST['option1'] == 'yes'){
    $criterias[] = '(mytable.myfield1 = 1)';
}

if(isset($_POST['option2']) && $_POST['option2'] == 'yes'){
    $criterias[] = '(mytable.myfield2 = 1)';
}

$sql = 'SELECT * FROM mytable'.(count($criterias) > 0 ? ' WHERE '.implode(' OR ', $criterias) : '').' ORDER BY mytable.sortkey';

This will help you build any kind of criteria list that you want. Note that i used OR in the implode because these criterias seem to be inclusive, so you show workers based on their type based on the user's choice. But i aint sure exactly if thats what you wanted.

Note also that you can setup your criterias to be much more complex and combine two information together such as:

if(isset($_POST['option2']) && $_POST['option2'] == 'yes'){
    $criterias[] = '(mytable.myfield2 = 1 AND mytable.location = "'.$_POST['location'].'")
}

That way, you could have many types that pertain to a specific location or even to different locations at once if you want.

Good luck