title: Querying muliple columns in MySQL table (PHP)
body: <p>I have a table with columns named: "ID, Track Name, City, County" among others. I'm looking for a way for a user to query the database and get results back relating to those 4 columns, with the most relevant results appearing at the top.</p>
  
  <p>Say there was a row with: "1, Track Number One, Taunton, Somerset"
  and a row with: "2, Circuit Number Two, Taunton, Somerset"
  and the user searched "circuit in taunton"</p>
  
  <p>Obviously I would like both rows to show, but I would like the second row to appear at the top because it has matched 2 keywords.</p>
  
  <p>This is the current code I am using but it is only working on one column or another and if the user queried "karting in taunton" it would not work.</p>
  
  <pre><code>$searchterm = htmlspecialchars($getArray['searchterm']);
  $searchterm = $mysqli-&gt;real_escape_string($searchterm);
  
  $terms = explode(' ', $searchterm);
  $bits = array();
  $cityBits = array();
  $countyBits = array();
  
  foreach ($terms as $term) {
      $bits[] = "name LIKE '%".mysql_real_escape_string($term)."%'";
  }
  
  foreach ($terms as $cityTerms) {
      $cityBits[] = "city LIKE '%".mysql_real_escape_string($cityTerms)."%'";
  }
  
  foreach ($terms as $countyTerms) {
      $countyBits[] = "county LIKE '%".mysql_real_escape_string($countyTerms)."%'";
  }
  
  $this-&gt;query = "SELECT * FROM tracks WHERE (".implode(' AND ', $bits).")
                  union SELECT * FROM tracks WHERE (".implode(' AND ', $cityBits).")
                  union SELECT * FROM tracks WHERE (".implode(' AND ', $countyBits).")";
  </code></pre>
  
  <p>I know this code is not ideal but it all I've managed to get working at the moment.</p>