php - 使用来自复选框、下拉列表和文本字段的 HTML 表单输入来过滤数据库结果

Question

我已成功从数据库中提取所有记录并对它们进行排序，但现在我需要过滤结果。使用来自复选框、下拉列表和文本字段的表单输入。如果用户将任何输入字段留空，我显然不希望结果被该特定输入字段过滤。我还需要表单具有粘性，并在提交表单后记住复选框、选择项和文本输入字段。

我在每个表单输入之前留下评论，以帮助解释

这是将用于过滤 mysql 结果的表单:

    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
      <fieldset class="general">

      <p><input type="text" id="name" placeholder="Player Name"></p>

      <p>
      <!-- user can set a range (example 80 - 90), only players with an OVR rating from 80-90 will be returned in the results -->
      <input type="text" id="ovr-min" placeholder="OVR min"> -
      <input type="text" id="ovr-max" placeholder="OVR max">
      </p>


      <div class="styled">
        <select id="team">
          <option selected disabled>Select Team</option>
          <option value="Bears">Bears</option>
          <option value="Bengals">Bengals</option>
          <option value="Bills">Bills</option>
          <option value="Broncos">Broncos</option>
          <option value="Browns">Browns</option>
          <option value="Buccaneers">Buccaneers</option>
          <option value="Cardinals">Cardinals</option>
          <option value="Chargers">Chargers</option>
          <option value="Chiefs">Chiefs</option>
          <option value="Colts">Colts</option>
          <option value="Cowboys">Cowboys</option>
          <option value="Dolphins">Dolphins</option>
          <option value="Eagles">Eagles</option>
          <option value="Falcons">Falcons</option>
          <option value="49ers">49ers</option>
          <option value="Giants">Giants</option>
          <option value="Jaguars">Jaguars</option>
          <option value="Jets">Jets</option>
          <option value="Lions">Lions</option>
          <option value="Packers">Packers</option>
          <option value="Panthers">Panthers</option>
          <option value="Patriots">Patriots</option>
          <option value="Raiders">Raiders</option>
          <option value="Rams">Rams</option>
          <option value="Ravens">Ravens</option>
          <option value="Redskins">Redskins</option>
          <option value="Saints">Saints</option>
          <option value="Seahawks">Seahawks</option>
          <option value="Steelers">Steelers</option>
          <option value="Texans">Texans</option>
          <option value="Titans">Titans</option>
          <option value="Vikings">Vikings</option>
          <option value="Legends">Legends</option>
          <option value="Free Agents">Free Agents</option>
        </select>
      </div>

      <br />

      <div class="styled">
        <!-- gonna have to use the LIKE '$info%', because, this drop down list is filtering the info field in the DB, see my site link for example contents of the info field -->
        <select id="info">
          <option selected disabled>Select Group</option>
          <option value="TOTW">TOTW</option>
          <option value="SOTW">DirectTV SOTW</option>
          <option value="Football Outsiders">Football Outsiders</option>
          <option value="Limited Edition">Limited Edition</option>
          <option value="Next Generation Team">Next Generation Team</option>
          <option value="Top 100 Rookie Star">Top 100 Rookie Star</option>
          <option value="MUT Base Set">MUT Base Set</option>
        </select>
      </div>

      <br />

      <div class="styled">
        <!-- The list of Colleges is not complete yet -->
        <select id="college">
        <option selected disabled>Select College</option>
        <optgroup label="Big Ten">
          <option value="Illinois">Illinois</option>
          <option value="Indiana">Indiana</option>
          <option value="Iowa">Iowa</option>
          <option value="Michigan">Michigan</option>
          <option value="Michigan State">Michigan State</option>
          <option value="Minnesota">Minnesota</option>
          <option value="Nebraska">Nebraska</option>
          <option value="Northwestern">Northwestern</option>
          <option value="Ohio State">Ohio State</option>
          <option value="Penn State">Penn State</option>
          <option value="Purdue">Purdue</option>
          <option value="Wisconsin">Wisconsin</option>
        </optgroup>
        <optgroup label="Conference USA">
          <option value="East Carolina">East Carolina</option>
          <option value="Florida Atlantic">Florida Atlantic</option>
          <option value="Florida International">Florida International</option>
          <option value="Louisiana Tech">Louisiana Tech</option>
          <option value="Marshall">Marshall</option>
          <option value="Middle Tennessee">Middle Tennessee</option>
          <option value="North Texas">North Texas</option>
          <option value="Rice">Rice</option>
          <option value="Southern Miss">Southern Miss</option>
          <option value="Tulane">Tulane</option>
          <option value="Tulsa">Tulsa</option>
          <option value="UAB">UAB</option>
          <option value="UTEP">UTEP</option>
          <option value="UTSA">UTSA</option>
        </optgroup>
        </select>
      </div>

      <br />

      <div class="styled">
      <!-- Chemistry is either NULL or a numeric value in the DB field (only show results with a value...  not NULL) -->
      <select id="chemistry">
        <option selected disabled>Select Chemistry</option>
        <option value="sp">Short Pass</option>
        <option value="lp">Long Pass</option>
        <option value="gp">Ground and Pound</option>
        <option value="sr">Speed Run</option>
        <option value="zd">Zone Defense</option>
        <option value="md">Man Defense</option>
        <option value="pr">Pass Rush</option>
        <option value="rs">Run Stuff</option>
      </select>
      </div>

    <br/>

      <input type="submit" value="Filter">

      <input type="reset" value="Clear all">
    </fieldset>

    <fieldset class="tier">
    <legend><strong>Tier:</strong></legend>
    <ul>
      <li>
        <input type="checkbox" id="bronze" name="tier[]" value="Bronze" />
        <label class="bronze" for="bronze">Bronze</label>
      </li>
      <li>
        <input type="checkbox" id="silver" name="tier[]" value="Silver" />
        <label class="silver" for="silver">Silver</label>
      </li>
      <li>
        <input type="checkbox" id="gold" name="tier[]" value="Gold" />
        <label class="gold" for="gold">Gold</label>
      </li>
      <li>
        <input type="checkbox" id="elite" name="tier[]" value="Elite" />
        <label class="elite" for="elite">Elite</label>
      </li>
      <li> 
        <input type="checkbox" id="legendary" name="tier[]" value="Legendary" />
        <label class="legendary" for="legendary">Legendary</label>
      </li>
      <li>
        <input type="checkbox" id="fantasy" name="tier[]" value="Fantasy" />
        <label class="fantasy" for="fantasy">Fantasy</label>
      </li>
    </ul>
    </fieldset>

    <fieldset class="position">
    <legend><strong>Position:</strong></legend>
    <ul>
      <li>Offense</li>
      <li>
        <input type="checkbox" id="qb" name="position[]" value="QB" />
        <label for="qb">QB</label>
      </li>
      <li>
        <input type="checkbox" id="hb" name="position[]" value="HB" />
        <label for="hb">HB</label>
      </li>
      <li>
        <input type="checkbox" id="fb" name="position[]" value="FB" />
        <label for="fb">FB</label>
      </li>
      <li>
        <input type="checkbox" id="wr" name="position[]" value="WR" />
        <label for="wr">WR</label>
      </li>
      <li>
        <input type="checkbox" id="te" name="position[]" value="TE" />
        <label for="te">TE</label>
      </li>
      <li>
        <input type="checkbox" id="lt" name="position[]" value="LT" />
        <label for="lt">LT</label>
      </li>
      <li>
        <input type="checkbox" id="lg" name="position[]" value="LG" />
        <label for="lg">LG</label>
      </li>
      <li>
        <input type="checkbox" id="c" name="position[]" value="C" />
        <label for="c">C</label>
      </li>
      <li>
        <input type="checkbox" id="rg" name="position[]" value="RG" />
        <label for="rg">RG</label>
      </li>
      <li>
        <input type="checkbox" id="rt" name="position[]" value="RT" />
        <label for="rt">RT</label>
      </li>
    </ul>

    <ul>
      <li>Defense</li>
      <li>
        <input type="checkbox" id="le" name="position[]" value="LE" />
        <label for="le">LE</label>
      </li>
      <li>
        <input type="checkbox" id="re" name="position[]" value="RE" />
        <label for="re">RE</label>
      </li>
      <li>
        <input type="checkbox" id="dt" name="position[]" value="DT" />
        <label for="dt">DT</label>
      </li>
      <li>
        <input type="checkbox" id="lolb" name="position[]" value="LOLB" />
        <label for="lolb">LOLB</label>
      </li>
      <li>
        <input type="checkbox" id="mlb" name="position[]" value="MLB" />
        <label for="mlb">MLB</label>
      </li>
      <li>
        <input type="checkbox" id="rolb" name="position[]" value="ROLB" />
        <label for="rolb">ROLB</label>
      </li>
      <li>
        <input type="checkbox" id="cb" name="position[]" value="CB" />
        <label for="cb">CB</label>
      </li>
      <li>
        <input type="checkbox" id="fs" name="position[]" value="FS" />
        <label  for="fs">FS</label>
      </li>
      <li>
        <input type="checkbox" id="ss" name="position[]" value="SS" />
        <label for="ss">SS</label>
      </li>
    </ul>

    <ul>
      <li>Special Teams</li>
      <li>
        <input type="checkbox" id="k" name="position[]" value="K" />
        <label for="k">K</label>
      </li>
      <li>
        <input type="checkbox" id="p" name="position[]" value="P" />
        <label for="p">P</label>
      </li>
    </ul>
    </fieldset>

    </form>

这是我的 SQL 语句:(我没有编写 WHERE 子句...这就是我需要帮助的原因)。我也知道化学领域的 CONCAT 和操作应该在应用程序级别完成...我还没有那么好...哈哈

     $query = "SELECT
                CONCAT(first_name, ' ', last_name) AS name,
                tier,
                team,
                position,
                ovr,
                info,
                portrait,
                spd,
                str,
                agi,
                acc,
                awr,
                CONCAT(
                  IF (sp IS NULL, '', CONCAT('SP: ', sp, '<br />')),
                  IF (lp IS NULL, '', CONCAT('LP: ', lp, '<br />')),
                  IF (gp IS NULL, '', CONCAT('GP: ', gp, '<br />')),
                  IF (sr IS NULL, '', CONCAT('SR: ', sr, '<br />')),
                  IF (zd IS NULL, '', CONCAT('ZD: ', zd, '<br />')),
                  IF (md IS NULL, '', CONCAT('MD: ', md, '<br />')),
                  IF (pr IS NULL, '', CONCAT('PR: ', pr, '<br />')),
                  IF (rs IS NULL, '', CONCAT('RS: ', rs))
                ) AS chemistry
                FROM player
                ORDER BY ovr DESC, name ASC";
      $result = mysql_query($query);

以下是我如何格式化查询输出:

        <thead>
          <tr>
              <th></th>
              <th width="110px">Name</th>
              <th>Team</th>
              <th>Position</th>
              <th>OVR</th>
              <th>Info</th>
              <th>SPD</th>
              <th>STR</th>
              <th>AGI</th>
              <th>ACC</th>
              <th>AWR</th>
              <th>Chemistry</th>
          </tr>
      </thead>
      <tbody>
        <?php
        while ($row = mysql_fetch_array ($result)) {
        ?>
          <tr>
              <td background="img/icons/<?=$row ['tier'] ?>.png"></td>
              <td><img src="img/players/portraits/64x64/<?=$row ['portrait'] ?>" width="64" height="64" alt="<?=$row ['name'] ?>" /><br /> <?=$row ['name'] ?></td>
              <td><img src="img/teams/<?=$row ['team'] ?>.png" width="48" height="48" alt="<?=$row ['team'] ?>" /><br /><?=$row ['team'] ?></td>
              <td><?=$row ['position'] ?></td>
              <td><?=$row ['ovr'] ?></td>
              <td style="text-align:left"><?=$row ['info'] ?></td>
              <td><?=$row ['spd'] ?></td>
              <td><?=$row ['str'] ?></td>
              <td><?=$row ['agi'] ?></td>
              <td><?=$row ['acc'] ?></td>
              <td><?=$row ['awr'] ?></td>
              <td><?=$row ['chemistry'] ?></td>
          </tr>
        <?php
        }
        ?>
      </tbody>
  </table>

Answer 1

为了简单起见，我减少了您的问题，以说明如何生成动态查询。它仅使用文本输入name和复选框position[]。这应该允许您为不同的输入构建查询的其余部分。复选框填充 IN 的值。情况。

生成的查询示例:

SELECT  field1,field2,etc FROM table WHERE 1 AND name = 'Porter' 
      AND position IN ('QB','LE')

生成的查询不是PDO格式，只是为了显示查询。您需要参数化变量。

至于粘性，您可以使用AJAX或Sessions

?php
$name = isset($_POST['name']) ? $_POST['name'] : ''; 
$aPosition = isset($_POST['position']) ? $_POST['position'] : ''; 
$sql ="SELECT field1,field2,etc FROM table WHERE 1";//Query stub 
if($name != "")
{ 
    $sql.= " AND name = '$name' ";//Only 1 name here for simplicity
}
$positionIN = "";
if($aPosition != "")
{  
    $Num = count($aPosition);//Number of parameters for IN
    for($i=0; $i < $Num; $i++)
    {
        $positionIN = $positionIN."'".$aPosition[$i]."'". ",";//Add to IN 
    }
    $positionIN = substr($positionIN,0,-1);//Remove trailing ,
    $sql.= " AND position IN (".$positionIN .")";

  }
echo $sql."<br>";
?>
<form action=<?php echo $_SERVER['PHP_SELF']; ?> method="post">
<fieldset class="general">
<p><input type="text" id="name" name="name" placeholder="Player Name"></p>
 <fieldset class="position">
    <legend><strong>Position:</strong></legend>
    <ul>
      <li>Offense</li>
      <li>
        <input type="checkbox" id="qb" name="position[]" value="QB" />
        <label for="qb">QB</label>
      </li>
      <li>
        <input type="checkbox" id="hb" name="position[]" value="HB" />
        <label for="hb">HB</label>
      </li>
      <li>
        <input type="checkbox" id="fb" name="position[]" value="FB" />
        <label for="fb">FB</label>
      </li>
      <li>
        <input type="checkbox" id="wr" name="position[]" value="WR" />
        <label for="wr">WR</label>
      </li>
      <li>
        <input type="checkbox" id="te" name="position[]" value="TE" />
        <label for="te">TE</label>
      </li>
      <li>
        <input type="checkbox" id="lt" name="position[]" value="LT" />
        <label for="lt">LT</label>
      </li>
      <li>
        <input type="checkbox" id="lg" name="position[]" value="LG" />
        <label for="lg">LG</label>
      </li>
      <li>
        <input type="checkbox" id="c" name="position[]" value="C" />
        <label for="c">C</label>
      </li>
      <li>
        <input type="checkbox" id="rg" name="position[]" value="RG" />
        <label for="rg">RG</label>
      </li>
      <li>
        <input type="checkbox" id="rt" name="position[]" value="RT" />
        <label for="rt">RT</label>
      </li>
    </ul>

    <ul>
      <li>Defense</li>
      <li>
        <input type="checkbox" id="le" name="position[]" value="LE" />
        <label for="le">LE</label>
      </li>
      <li>
        <input type="checkbox" id="re" name="position[]" value="RE" />
        <label for="re">RE</label>
      </li>
      <li>
        <input type="checkbox" id="dt" name="position[]" value="DT" />
        <label for="dt">DT</label>
      </li>
      <li>
        <input type="checkbox" id="lolb" name="position[]" value="LOLB" />
        <label for="lolb">LOLB</label>
      </li>
      <li>
        <input type="checkbox" id="mlb" name="position[]" value="MLB" />
        <label for="mlb">MLB</label>
      </li>
      <li>
        <input type="checkbox" id="rolb" name="position[]" value="ROLB" />
        <label for="rolb">ROLB</label>
      </li>
      <li>
        <input type="checkbox" id="cb" name="position[]" value="CB" />
        <label for="cb">CB</label>
      </li>
      <li>
        <input type="checkbox" id="fs" name="position[]" value="FS" />
        <label  for="fs">FS</label>
      </li>
      <li>
        <input type="checkbox" id="ss" name="position[]" value="SS" />
        <label for="ss">SS</label>
      </li>
    </ul> 
<input type="submit" name="formSubmit" value="Submit" />
</form>