title: using HTML form input from check boxes, drop down lists, and text fields to filter database results
body: <p>I have been successful with pulling all records from the database, and ordering them, but now I need to filter the results. Using form input from check boxes, drop down lists, and text fields. If the user leaves any of the input fields blank, I obviously don't want the results to be filtered by that particular input field. I also need the form to be sticky, and remember the checkboxes, selections, and text input fields after the form has been submitted.</p>
  
  <p>I left comments before each form input, to help explain</p>
  
  <p>Here's the Form that will be used to Filter the mysql results:</p>
  
  <pre><code>    &lt;form method="post" action="&lt;?php echo $_SERVER['PHP_SELF']; ?&gt;"&gt;
        &lt;fieldset class="general"&gt;
  
        &lt;p&gt;&lt;input type="text" id="name" placeholder="Player Name"&gt;&lt;/p&gt;
  
        &lt;p&gt;
        &lt;!-- user can set a range (example 80 - 90), only players with an OVR rating from 80-90 will be returned in the results --&gt;
        &lt;input type="text" id="ovr-min" placeholder="OVR min"&gt; -
        &lt;input type="text" id="ovr-max" placeholder="OVR max"&gt;
        &lt;/p&gt;
  
  
        &lt;div class="styled"&gt;
          &lt;select id="team"&gt;
            &lt;option selected disabled&gt;Select Team&lt;/option&gt;
            &lt;option value="Bears"&gt;Bears&lt;/option&gt;
            &lt;option value="Bengals"&gt;Bengals&lt;/option&gt;
            &lt;option value="Bills"&gt;Bills&lt;/option&gt;
            &lt;option value="Broncos"&gt;Broncos&lt;/option&gt;
            &lt;option value="Browns"&gt;Browns&lt;/option&gt;
            &lt;option value="Buccaneers"&gt;Buccaneers&lt;/option&gt;
            &lt;option value="Cardinals"&gt;Cardinals&lt;/option&gt;
            &lt;option value="Chargers"&gt;Chargers&lt;/option&gt;
            &lt;option value="Chiefs"&gt;Chiefs&lt;/option&gt;
            &lt;option value="Colts"&gt;Colts&lt;/option&gt;
            &lt;option value="Cowboys"&gt;Cowboys&lt;/option&gt;
            &lt;option value="Dolphins"&gt;Dolphins&lt;/option&gt;
            &lt;option value="Eagles"&gt;Eagles&lt;/option&gt;
            &lt;option value="Falcons"&gt;Falcons&lt;/option&gt;
            &lt;option value="49ers"&gt;49ers&lt;/option&gt;
            &lt;option value="Giants"&gt;Giants&lt;/option&gt;
            &lt;option value="Jaguars"&gt;Jaguars&lt;/option&gt;
            &lt;option value="Jets"&gt;Jets&lt;/option&gt;
            &lt;option value="Lions"&gt;Lions&lt;/option&gt;
            &lt;option value="Packers"&gt;Packers&lt;/option&gt;
            &lt;option value="Panthers"&gt;Panthers&lt;/option&gt;
            &lt;option value="Patriots"&gt;Patriots&lt;/option&gt;
            &lt;option value="Raiders"&gt;Raiders&lt;/option&gt;
            &lt;option value="Rams"&gt;Rams&lt;/option&gt;
            &lt;option value="Ravens"&gt;Ravens&lt;/option&gt;
            &lt;option value="Redskins"&gt;Redskins&lt;/option&gt;
            &lt;option value="Saints"&gt;Saints&lt;/option&gt;
            &lt;option value="Seahawks"&gt;Seahawks&lt;/option&gt;
            &lt;option value="Steelers"&gt;Steelers&lt;/option&gt;
            &lt;option value="Texans"&gt;Texans&lt;/option&gt;
            &lt;option value="Titans"&gt;Titans&lt;/option&gt;
            &lt;option value="Vikings"&gt;Vikings&lt;/option&gt;
            &lt;option value="Legends"&gt;Legends&lt;/option&gt;
            &lt;option value="Free Agents"&gt;Free Agents&lt;/option&gt;
          &lt;/select&gt;
        &lt;/div&gt;
  
        &lt;br /&gt;
  
        &lt;div class="styled"&gt;
          &lt;!-- 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 --&gt;
          &lt;select id="info"&gt;
            &lt;option selected disabled&gt;Select Group&lt;/option&gt;
            &lt;option value="TOTW"&gt;TOTW&lt;/option&gt;
            &lt;option value="SOTW"&gt;DirectTV SOTW&lt;/option&gt;
            &lt;option value="Football Outsiders"&gt;Football Outsiders&lt;/option&gt;
            &lt;option value="Limited Edition"&gt;Limited Edition&lt;/option&gt;
            &lt;option value="Next Generation Team"&gt;Next Generation Team&lt;/option&gt;
            &lt;option value="Top 100 Rookie Star"&gt;Top 100 Rookie Star&lt;/option&gt;
            &lt;option value="MUT Base Set"&gt;MUT Base Set&lt;/option&gt;
          &lt;/select&gt;
        &lt;/div&gt;
  
        &lt;br /&gt;
  
        &lt;div class="styled"&gt;
          &lt;!-- The list of Colleges is not complete yet --&gt;
          &lt;select id="college"&gt;
          &lt;option selected disabled&gt;Select College&lt;/option&gt;
          &lt;optgroup label="Big Ten"&gt;
            &lt;option value="Illinois"&gt;Illinois&lt;/option&gt;
            &lt;option value="Indiana"&gt;Indiana&lt;/option&gt;
            &lt;option value="Iowa"&gt;Iowa&lt;/option&gt;
            &lt;option value="Michigan"&gt;Michigan&lt;/option&gt;
            &lt;option value="Michigan State"&gt;Michigan State&lt;/option&gt;
            &lt;option value="Minnesota"&gt;Minnesota&lt;/option&gt;
            &lt;option value="Nebraska"&gt;Nebraska&lt;/option&gt;
            &lt;option value="Northwestern"&gt;Northwestern&lt;/option&gt;
            &lt;option value="Ohio State"&gt;Ohio State&lt;/option&gt;
            &lt;option value="Penn State"&gt;Penn State&lt;/option&gt;
            &lt;option value="Purdue"&gt;Purdue&lt;/option&gt;
            &lt;option value="Wisconsin"&gt;Wisconsin&lt;/option&gt;
          &lt;/optgroup&gt;
          &lt;optgroup label="Conference USA"&gt;
            &lt;option value="East Carolina"&gt;East Carolina&lt;/option&gt;
            &lt;option value="Florida Atlantic"&gt;Florida Atlantic&lt;/option&gt;
            &lt;option value="Florida International"&gt;Florida International&lt;/option&gt;
            &lt;option value="Louisiana Tech"&gt;Louisiana Tech&lt;/option&gt;
            &lt;option value="Marshall"&gt;Marshall&lt;/option&gt;
            &lt;option value="Middle Tennessee"&gt;Middle Tennessee&lt;/option&gt;
            &lt;option value="North Texas"&gt;North Texas&lt;/option&gt;
            &lt;option value="Rice"&gt;Rice&lt;/option&gt;
            &lt;option value="Southern Miss"&gt;Southern Miss&lt;/option&gt;
            &lt;option value="Tulane"&gt;Tulane&lt;/option&gt;
            &lt;option value="Tulsa"&gt;Tulsa&lt;/option&gt;
            &lt;option value="UAB"&gt;UAB&lt;/option&gt;
            &lt;option value="UTEP"&gt;UTEP&lt;/option&gt;
            &lt;option value="UTSA"&gt;UTSA&lt;/option&gt;
          &lt;/optgroup&gt;
          &lt;/select&gt;
        &lt;/div&gt;
  
        &lt;br /&gt;
  
        &lt;div class="styled"&gt;
        &lt;!-- Chemistry is either NULL or a numeric value in the DB field (only show results with a value...  not NULL) --&gt;
        &lt;select id="chemistry"&gt;
          &lt;option selected disabled&gt;Select Chemistry&lt;/option&gt;
          &lt;option value="sp"&gt;Short Pass&lt;/option&gt;
          &lt;option value="lp"&gt;Long Pass&lt;/option&gt;
          &lt;option value="gp"&gt;Ground and Pound&lt;/option&gt;
          &lt;option value="sr"&gt;Speed Run&lt;/option&gt;
          &lt;option value="zd"&gt;Zone Defense&lt;/option&gt;
          &lt;option value="md"&gt;Man Defense&lt;/option&gt;
          &lt;option value="pr"&gt;Pass Rush&lt;/option&gt;
          &lt;option value="rs"&gt;Run Stuff&lt;/option&gt;
        &lt;/select&gt;
        &lt;/div&gt;
  
      &lt;br/&gt;
  
        &lt;input type="submit" value="Filter"&gt;
  
        &lt;input type="reset" value="Clear all"&gt;
      &lt;/fieldset&gt;
  
      &lt;fieldset class="tier"&gt;
      &lt;legend&gt;&lt;strong&gt;Tier:&lt;/strong&gt;&lt;/legend&gt;
      &lt;ul&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="bronze" name="tier[]" value="Bronze" /&gt;
          &lt;label class="bronze" for="bronze"&gt;Bronze&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="silver" name="tier[]" value="Silver" /&gt;
          &lt;label class="silver" for="silver"&gt;Silver&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="gold" name="tier[]" value="Gold" /&gt;
          &lt;label class="gold" for="gold"&gt;Gold&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="elite" name="tier[]" value="Elite" /&gt;
          &lt;label class="elite" for="elite"&gt;Elite&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt; 
          &lt;input type="checkbox" id="legendary" name="tier[]" value="Legendary" /&gt;
          &lt;label class="legendary" for="legendary"&gt;Legendary&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="fantasy" name="tier[]" value="Fantasy" /&gt;
          &lt;label class="fantasy" for="fantasy"&gt;Fantasy&lt;/label&gt;
        &lt;/li&gt;
      &lt;/ul&gt;
      &lt;/fieldset&gt;
  
      &lt;fieldset class="position"&gt;
      &lt;legend&gt;&lt;strong&gt;Position:&lt;/strong&gt;&lt;/legend&gt;
      &lt;ul&gt;
        &lt;li&gt;Offense&lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="qb" name="position[]" value="QB" /&gt;
          &lt;label for="qb"&gt;QB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="hb" name="position[]" value="HB" /&gt;
          &lt;label for="hb"&gt;HB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="fb" name="position[]" value="FB" /&gt;
          &lt;label for="fb"&gt;FB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="wr" name="position[]" value="WR" /&gt;
          &lt;label for="wr"&gt;WR&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="te" name="position[]" value="TE" /&gt;
          &lt;label for="te"&gt;TE&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="lt" name="position[]" value="LT" /&gt;
          &lt;label for="lt"&gt;LT&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="lg" name="position[]" value="LG" /&gt;
          &lt;label for="lg"&gt;LG&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="c" name="position[]" value="C" /&gt;
          &lt;label for="c"&gt;C&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="rg" name="position[]" value="RG" /&gt;
          &lt;label for="rg"&gt;RG&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="rt" name="position[]" value="RT" /&gt;
          &lt;label for="rt"&gt;RT&lt;/label&gt;
        &lt;/li&gt;
      &lt;/ul&gt;
  
      &lt;ul&gt;
        &lt;li&gt;Defense&lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="le" name="position[]" value="LE" /&gt;
          &lt;label for="le"&gt;LE&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="re" name="position[]" value="RE" /&gt;
          &lt;label for="re"&gt;RE&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="dt" name="position[]" value="DT" /&gt;
          &lt;label for="dt"&gt;DT&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="lolb" name="position[]" value="LOLB" /&gt;
          &lt;label for="lolb"&gt;LOLB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="mlb" name="position[]" value="MLB" /&gt;
          &lt;label for="mlb"&gt;MLB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="rolb" name="position[]" value="ROLB" /&gt;
          &lt;label for="rolb"&gt;ROLB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="cb" name="position[]" value="CB" /&gt;
          &lt;label for="cb"&gt;CB&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="fs" name="position[]" value="FS" /&gt;
          &lt;label  for="fs"&gt;FS&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="ss" name="position[]" value="SS" /&gt;
          &lt;label for="ss"&gt;SS&lt;/label&gt;
        &lt;/li&gt;
      &lt;/ul&gt;
  
      &lt;ul&gt;
        &lt;li&gt;Special Teams&lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="k" name="position[]" value="K" /&gt;
          &lt;label for="k"&gt;K&lt;/label&gt;
        &lt;/li&gt;
        &lt;li&gt;
          &lt;input type="checkbox" id="p" name="position[]" value="P" /&gt;
          &lt;label for="p"&gt;P&lt;/label&gt;
        &lt;/li&gt;
      &lt;/ul&gt;
      &lt;/fieldset&gt;
  
      &lt;/form&gt;
  </code></pre>
  
  <p>Here's my SQL statement: (I haven't written the WHERE clause... that's why I need help). I also know that the CONCAT and manipulation of the Chemistry fields should be done at the application level... I'm not that good yet...lol</p>
  
  <pre><code>     $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, '&lt;br /&gt;')),
                    IF (lp IS NULL, '', CONCAT('LP: ', lp, '&lt;br /&gt;')),
                    IF (gp IS NULL, '', CONCAT('GP: ', gp, '&lt;br /&gt;')),
                    IF (sr IS NULL, '', CONCAT('SR: ', sr, '&lt;br /&gt;')),
                    IF (zd IS NULL, '', CONCAT('ZD: ', zd, '&lt;br /&gt;')),
                    IF (md IS NULL, '', CONCAT('MD: ', md, '&lt;br /&gt;')),
                    IF (pr IS NULL, '', CONCAT('PR: ', pr, '&lt;br /&gt;')),
                    IF (rs IS NULL, '', CONCAT('RS: ', rs))
                  ) AS chemistry
                  FROM player
                  ORDER BY ovr DESC, name ASC";
        $result = mysql_query($query);
  </code></pre>
  
  <p>Here's how I formatted the query output:</p>
  
  <pre><code>        &lt;thead&gt;
            &lt;tr&gt;
                &lt;th&gt;&lt;/th&gt;
                &lt;th width="110px"&gt;Name&lt;/th&gt;
                &lt;th&gt;Team&lt;/th&gt;
                &lt;th&gt;Position&lt;/th&gt;
                &lt;th&gt;OVR&lt;/th&gt;
                &lt;th&gt;Info&lt;/th&gt;
                &lt;th&gt;SPD&lt;/th&gt;
                &lt;th&gt;STR&lt;/th&gt;
                &lt;th&gt;AGI&lt;/th&gt;
                &lt;th&gt;ACC&lt;/th&gt;
                &lt;th&gt;AWR&lt;/th&gt;
                &lt;th&gt;Chemistry&lt;/th&gt;
            &lt;/tr&gt;
        &lt;/thead&gt;
        &lt;tbody&gt;
          &lt;?php
          while ($row = mysql_fetch_array ($result)) {
          ?&gt;
            &lt;tr&gt;
                &lt;td background="img/icons/&lt;?=$row ['tier'] ?&gt;.png"&gt;&lt;/td&gt;
                &lt;td&gt;&lt;img src="img/players/portraits/64x64/&lt;?=$row ['portrait'] ?&gt;" width="64" height="64" alt="&lt;?=$row ['name'] ?&gt;" /&gt;&lt;br /&gt; &lt;?=$row ['name'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;img src="img/teams/&lt;?=$row ['team'] ?&gt;.png" width="48" height="48" alt="&lt;?=$row ['team'] ?&gt;" /&gt;&lt;br /&gt;&lt;?=$row ['team'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['position'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['ovr'] ?&gt;&lt;/td&gt;
                &lt;td style="text-align:left"&gt;&lt;?=$row ['info'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['spd'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['str'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['agi'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['acc'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['awr'] ?&gt;&lt;/td&gt;
                &lt;td&gt;&lt;?=$row ['chemistry'] ?&gt;&lt;/td&gt;
            &lt;/tr&gt;
          &lt;?php
          }
          ?&gt;
        &lt;/tbody&gt;
    &lt;/table&gt;
  </code></pre>