title: PHP - how to search a single column specified in a drop down menu
body: <p>I have a SQL database which is implemented onto a HTML webpage. I want the user to be able to type in a value into a searchbox and search a column of the table specified in a drop down menu.</p>
  
  <p>For example: there are 3 columns on the table - 'first name', 'second name' and 'surname'. The user can choose one of these column names in a drop down menu and then type into a searchbox what they want to search for in the column specified. Let's say they choose the column 'surname' and want to search for 'Johnson' in that column. </p>
  
  <p>The results should show all surnames with 'Johnson' in them and show the rest of the row for each found. My database is a bit different and larger than this but it applies the same. My current code can be found below:</p>
  
  <pre><code>&lt;head&gt;
      &lt;link rel="stylesheet" type="text/css" href="stylesheet.css" /&gt;
      &lt;meta charset="utf-8" /&gt;
  
      &lt;title&gt;The 24 Hour Race&lt;/title&gt;
      &lt;div id="title"&gt;
          &lt;a href="index.php"&gt;&lt;img src="images/title.png"&gt;&lt;/img&gt;&lt;/a&gt;
      &lt;/div&gt;
      &lt;p id="subtitle"&gt;For the toughest of the tough&lt;/p&gt;
  &lt;/head&gt;
  &lt;body&gt;
      &lt;div id="nav"&gt;
          &lt;form method="post" action="index.php?go"  id="searchform"&gt;
              &lt;br&gt;
              &lt;h3&gt;Search in:&lt;/h3&gt;&lt;select name="field"&gt;
              &lt;option value="rider_no."&gt;Rider No.&lt;/option&gt;
              &lt;option value="category"&gt;Category&lt;/option&gt;
              &lt;option value="name"&gt;Name&lt;/option&gt;
              &lt;option value="team_name"&gt;Team Name&lt;/option&gt;
              &lt;option value="nationality"&gt;Nationality&lt;/option&gt;
              &lt;option value="year_participated"&gt;Year Participated&lt;/option&gt;
              &lt;option value="total_laps"&gt;Total Laps&lt;/option&gt;
              &lt;option value="fastest_lap"&gt;Fastest Lap&lt;/option&gt;
              &lt;/select&gt;
              &lt;input type="text" name="key"&gt;
              &lt;input type="submit" name="submit" value="Search"&gt;&lt;br&gt;
          &lt;/form&gt;
          &lt;br&gt;
          &lt;h3&gt;Sort by years:&lt;/h3&gt;            
          &lt;ul&gt;
              &lt;li&gt;&lt;a href="2007.php"&gt;2007&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2008.php"&gt;2008&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2009.php"&gt;2009&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2010.php"&gt;2010&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2011.php"&gt;2011&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2012.php"&gt;2012&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2013.php"&gt;2013&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2014.php"&gt;2014&lt;/a&gt;&lt;/li&gt;
              &lt;li&gt;&lt;a href="2015.php"&gt;2015&lt;/a&gt;&lt;/li&gt;
          &lt;/ul&gt;
      &lt;/div&gt;
      &lt;div id="form"&gt;            
          &lt;table id="t01" align="right" cellspacing="2"&gt;
      &lt;thead&gt;
          &lt;tr&gt;
              &lt;td bgcolor="#ccc"&gt;Rider No.&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Category&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Name&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Team Name&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Nationality&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Year Participated&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Total Laps&lt;/td&gt;
              &lt;td bgcolor="#ccc"&gt;Fastest Lap&lt;/td&gt;
          &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
          &lt;?php
          $connect = mysql_connect("localhost","root", "Highland5");
          if (!$connect) {
              die(mysql_error());
          }
          mysql_select_db("the24hourrace");
          if(isset($_POST[field]) and isset($_POST[key])) {
              $results = mysql_query("SELECT ".[field]." FROM riders WHERE ".[field]." = ".[key], $connect);
          } else {
              $results = mysql_query("SELECT * FROM riders", $connect); 
          }
  
          while($row = mysql_fetch_array($results)) {
     ?&gt;
              &lt;tr&gt;
                  &lt;td&gt;&lt;?php echo $row['rider_no']?&gt;&lt;/td&gt;                    
                  &lt;td&gt;&lt;?php echo $row['category']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['name']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['team_name']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['nationality']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['year_participated']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['total_laps']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['fastest_lap']?&gt;&lt;/td&gt;
              &lt;/tr&gt;
          &lt;?php
          }
          ?&gt;
       &lt;/tbody&gt;
          &lt;/table&gt;
      &lt;/div&gt;
  &lt;/body&gt;
  </code></pre>
  
  <p></p>
  
  <p>The important bits which I am asking about include div id="nav" and the form below it:</p>
  
  <pre><code>&lt;form method="post" action="index.php?go"  id="searchform"&gt;
              &lt;br&gt;
              &lt;h3&gt;Search in:&lt;/h3&gt;&lt;select name="field"&gt;
              &lt;option value="rider_no."&gt;Rider No.&lt;/option&gt;
              &lt;option value="category"&gt;Category&lt;/option&gt;
              &lt;option value="name"&gt;Name&lt;/option&gt;
              &lt;option value="team_name"&gt;Team Name&lt;/option&gt;
              &lt;option value="nationality"&gt;Nationality&lt;/option&gt;
              &lt;option value="year_participated"&gt;Year Participated&lt;/option&gt;
              &lt;option value="total_laps"&gt;Total Laps&lt;/option&gt;
              &lt;option value="fastest_lap"&gt;Fastest Lap&lt;/option&gt;
              &lt;/select&gt;
              &lt;input type="text" name="key"&gt;
              &lt;input type="submit" name="submit" value="Search"&gt;&lt;br&gt;
          &lt;/form&gt;
  </code></pre>
  
  <p>And the PHP section:</p>
  
  <pre><code>&lt;?php
          $connect = mysql_connect("localhost","root", "Highland5");
          if (!$connect) {
              die(mysql_error());
          }
          mysql_select_db("the24hourrace");
          if(isset($_POST[field]) and isset($_POST[key])) {
              $results = mysql_query("SELECT ".[field]." FROM riders WHERE ".[field]." = ".[key], $connect);
          } else {
              $results = mysql_query("SELECT * FROM riders", $connect); 
          }
  
          while($row = mysql_fetch_array($results)) {
     ?&gt;
              &lt;tr&gt;
                  &lt;td&gt;&lt;?php echo $row['rider_no']?&gt;&lt;/td&gt;                    
                  &lt;td&gt;&lt;?php echo $row['category']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['name']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['team_name']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['nationality']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['year_participated']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['total_laps']?&gt;&lt;/td&gt;
                  &lt;td&gt;&lt;?php echo $row['fastest_lap']?&gt;&lt;/td&gt;
              &lt;/tr&gt;
          &lt;?php
          }
          ?&gt;
  </code></pre>
  
  <p>If you have any questions about my code or what I am asking then please do not hesitate to ask! I found this a bit tricky to explain :)</p>