title: PHP searching multiple database fields via separate form fields
body: <p>So I have a question, a pretty basic yet noobie looking one! I have been developing in PHP for years and seem to progress in knowledge very slowely.</p>
  
  <p>I am building a job site and I am having trouble with the search vacancies query..</p>
  
  <p>my form is set out like this..</p>
  
  <pre><code>&lt;form class="form-search-list" action="search" method="get"&gt;
              &lt;div class="row"&gt;
                &lt;div class="col-sm-5 col-xs-6"&gt;
                  &lt;div class="form-group"&gt;
                    &lt;label class="color-white"&gt;What&lt;/label&gt;
                    &lt;input type="hidden" name="type" value="basic"&gt;
                    &lt;input class="form-control" name="keywords" style="text-transform: capitalize;"&gt;
                  &lt;/div&gt;
                &lt;/div&gt;
                &lt;div class="col-sm-5 col-xs-6"&gt;
                  &lt;div class="form-group"&gt;
                    &lt;label class="color-white"&gt;Where&lt;/label&gt;
                    &lt;input class="form-control" name="location" style="text-transform: capitalize;"&gt;
                  &lt;/div&gt;
                &lt;/div&gt;
                &lt;div class="col-sm-2 col-xs-12"&gt;
                  &lt;div class="form-group"&gt;
                    &lt;label class="hidden-xs"&gt;&amp;nbsp;&lt;/label&gt;
                    &lt;button class="btn btn-block btn-theme  btn-success"&gt;Search&lt;/button&gt;
  
                  &lt;/div&gt;
                &lt;/div&gt;
              &lt;/div&gt;
              &lt;p class="text-right"&gt;&lt;a href="#modal-advanced" data-toggle="modal" class="link-white"&gt;Advanced Search&lt;/a&gt;&lt;/p&gt;
            &lt;/form&gt;
  </code></pre>
  
  <p>As you can see I am using <code>$_GET['']</code> to fetch the data from my selected database table, which in this case would be <code>vacancies</code></p>
  
  <p><strong>so onto search.php</strong></p>
  
  <pre><code>    if(!isset($_GET['type'])){
      header("Location: jobsearch");
  }
  
  if($_GET['type']=='basic'){
  
          $keywords = $_GET['keywords'];
          $location = $_GET['location'];
  
  $query = mysql_query("SELECT * FROM vacancies WHERE UNIX_TIMESTAMP() &lt; `expires` AND jobtitle LIKE '%" .$keywords. "%' AND location LIKE '%" .$location. "%' AND active='1' ORDER BY id DESC");
  $suffix = "SELECT COUNT(id) FROM vacancies WHERE UNIX_TIMESTAMP() &lt; `expires` AND jobtitle LIKE '%" .$keywords. "%' AND location LIKE '%" .$location. "%' AND active='1'";  
  $result = mysql_query($suffix) or die(mysql_error()); 
  foreach(mysql_fetch_array($result) as $totjobs);
  $suffixFT = ($totjobs != 1) ? 's' : '';
  
  if(mysql_num_rows($query) == 0){
       $resultsterm = "Sorry, we couldn't find any job listings based on your search!"; 
      } else {
          if($_GET['keywords'] == "" AND $_GET['location'] == ""){
              $resultsterm = "We found &lt;strong&gt;" . mysql_num_rows($query) . "&lt;/strong&gt; job listing" . $suffixFT . " throughout the UK.";
          } else {
              if($_GET['keywords'] == TRUE AND $_GET['location'] == ""){
                  $resultsterm = "We found &lt;strong&gt;" . mysql_num_rows($query) . "&lt;/strong&gt; job listing" . $suffixFT . " for &lt;strong&gt;$keywords&lt;/strong&gt; throughout the UK.";
              } else {
                  if($_GET['keywords'] == "" AND $_GET['location'] == TRUE){
                      $resultsterm = "We found &lt;strong&gt;" . mysql_num_rows($query) . "&lt;/strong&gt; job listing" . $suffixFT . " in &lt;strong&gt;$location&lt;/strong&gt;.";
                  } else {
                      if($_GET['keywords'] == TRUE AND $_GET['location'] == TRUE){
                          $resulsterm = "We found &lt;strong&gt;" . mysql_num_rows($query) . "&lt;/strong&gt; job listing" . $suffixFT . " for &lt;strong&gt;$keywords&lt;/strong&gt; in &lt;strong&gt;$location&lt;/strong.";
                      }
                  }
              }
          }
      }
  
  
  
  }
  </code></pre>
  
  <p>my database is set out as (<code>jobtitle</code> and <code>location</code>) I need to be able to compare my search against both of those fields using the explode function to search multiple phrases..</p>
  
  <p>for example, I have existing in my table, a vacancy named '<strong>Test Vacancy</strong>' which when I search for either '<strong>Test</strong>' or '<strong>Vacancy</strong>' it shows the count as one, meaning it has found a result, however if I search 'Test Job' it will not show a result!</p>
  
  <p>This is really confusing me. I have spent a good 8 hours trying to figure this one out and I can't get my head around it!</p>
  
  <p>I'm trying to figure our how to use the explode function with both <code>$keywords</code> and <code>$location</code> I know how to use the explode function and put it into a foreach loop with just the one variable but haven't got a clue where to start when trying to use the two variables.</p>
  
  <p>If one of you lovely people would be so kind as to help me out so I get an idea of what to do, It would be much appreciated.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have constructed the following block of code which kind of works..</p>
  
  <pre><code>$keywords = $_GET['keywords'];
  $jobkeywords = explode(' ',$keywords);
  
  $location = $_GET['location'];
  $lockeywords = explode(' ',$location);
  
  $searchColumns1 = array("jobtitle");
  $searchCondition1 = '';
  $searchColumns2 = array("location");
  $searchCondition2 = '';
    for($i = 0; $i &lt; count($searchColumns1); $i++)
      {
          $searchFieldName1 = $searchColumns1[$i];
          $searchCondition1 .= "$searchFieldName1 LIKE '%" . implode("%' OR $searchFieldName1 LIKE '%", $jobkeywords) . "%'";
          if($i+1 &lt; count($searchColumns1)) $searchCondition1 .= " OR ";
       }
      for($i = 0; $i &lt; count($searchColumns2); $i++)
      {
          $searchFieldName2 = $searchColumns2[$i];
          $searchCondition2 .= "$searchFieldName2 LIKE '%" . implode("%' OR $searchFieldName2 LIKE '%", $lockeywords) . "%'";
          if($i+1 &lt; count($searchColumns2)) $searchCondition2 .= " OR ";
       }
  
  $query = mysql_query("SELECT * FROM vacancies WHERE $searchCondition1 AND $searchCondition2");
  $suffix = "SELECT COUNT(id) FROM vacancies WHERE $searchCondition1 AND $searchCondition2";  
  </code></pre>
  
  <p>however it is not very strict in terms of checking and fetching from the table..
  I,e.. If I have a job for example name 'Test Vacancy' and its location is 'London, United Kingdom' but I search for 'Other Job' in 'London, United Kingdom' it still shows a result, being 'Test Vacancy' because that is located in London! I do not want it to do that, instead if a job title does not match then it will show 'No results found' its as if it is working with the locations field on its own instead of keywords and location working together to check if there is a match.</p>
  
  <p>Any ideas?</p>
  
  <p><strong>FURTHER UPDATE</strong></p>
  
  <p>Thank you for the input and help LinesofCode, I appreciate your efforts in helping me allot!</p>
  
  <p>Lets see if I can make it a little clearer as to what I am trying to achieve, my previous question seems a little vague and off topic after reading through it.</p>
  
  <p>I have two fields on my form, first is keywords.. second is location.. 
  so a user would type in 'Test Vacancies' in the keywords box and then their desired location. In my database table for vacancies It includes both jobtitle which the keywords in the form are being compared to, and then the location field which for the test vacancy I have in their, looks like this <code>jobtitle</code>='Test Vacancy' <code>location</code>='Southport Merseyside United Kingdom' so ideally what I want to do is use the explode function for the job title (keywords on the form) to match either 'Test' or 'Vacancy' with jobs alike located in 'Southport Merseyside United Kingdom', now for the location the user can type anything they like.. i can't control that, they could search 'Test Job' in 'Southport United Kingdom' so I would need the script to grab the 'Test Vacancy' by using the explode function to match the words 'Test' but at the same time be very strict on searching the location. I know from experience that using the explode function on both keywords and location it will result in allot of unwanted data, a user could search for 'Admin' in 'London' and instead of showing just the Admin jobs for London it would show all listings in London.</p>
  
  <p>It is a pretty confusing and complex script I need to write, I just don't know where to start!</p>
  
  <p><strong>Further Update</strong></p>
  
  <p>@Linesofcode this block seems to achieve what I want it to, however there is a small problem.. please see the block then I will explain the problem</p>
  
  <pre><code>$keywords = explode(" ", $_GET['keywords']);
  $location = explode(" ", $_GET['location']);
  
  if (!empty($keywords) &amp;&amp; count($keywords) &gt; 0) {
  
      $query = "SELECT COUNT(id) FROM vacancies WHERE";
  
  foreach($keywords as $keyword) {
      $keyword = mysql_real_escape_string($keyword);
      $query .= " jobtitle LIKE '%{$keyword}%' OR";
  }
  
  foreach($location as $loc) {
      $loc = mysql_real_escape_string($loc);
      $query .= " location LIKE '%{$loc}%' OR";
  }
  
  $query = rtrim($query, 'OR');
  
      }
  
  $result = mysql_query($query) or die(mysql_error());
  foreach (mysql_fetch_array($result) as $totrows);
  
  echo $query;
  </code></pre>
  
  <p>This block will output </p>
  
  <pre><code>SELECT COUNT(`id`) FROM vacancies WHERE jobtitle LIKE '%test%' OR jobtitle LIKE '%vacancy%' **OR** location LIKE '%southport%' OR location LIKE '%pr9%' OR location LIKE '%0tl%'
  </code></pre>
  
  <p>as you can see in the sql output, I have made bold an OR statement which needs to be changed to AND to look something like this</p>
  
  <pre><code>SELECT COUNT(`id`) FROM vacancies WHERE jobtitle LIKE '%test%' OR jobtitle LIKE '%vacancy%' **AND** location LIKE '%southport%' **OR** location LIKE '%pr9%' OR location LIKE '%0tl%'
  </code></pre>
  
  <p>is that possible with the block I have constructed?</p>
  
  <p><strong>Further Updates</strong></p>
  
  <p>So the constructed block kind of works..</p>
  
  <pre><code>$keywords = explode(" ", $_GET['keywords']); $location = explode(" ", $_GET['location']);
  
  if (!empty($keywords)) {        $query = "SELECT * FROM vacancies WHERE";
  
  foreach($keywords as $keyword) {
      $keyword = mysql_real_escape_string($keyword);
      $query .= " jobtitle LIKE '%{$keyword}%' OR"; }
  
  $query = preg_replace('/OR$/', 'AND', $query);
  
  foreach($location as $loc) {
      $loc = mysql_real_escape_string($loc);
      $query .= " location LIKE '%{$loc}%' OR"; }
  
  $query = rtrim($query, 'OR');
  
  echo $query;
  
  echo "&lt;p&gt;&lt;/p&gt;";
  
  $result = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)){     echo "" . $row['jobtitle'] . ""; }
  
      }
  </code></pre>
  
  <p>However when I think my foreach loops are confusing my while loop to display the results from the database..</p>
  
  <p>I search for 'Test Vacancy' in 'London' (the actual location in my database is 'Southport' but it still displays however when I search just for 'Test in London' it does not display which is what it should do, It seems as though when two keywords are entered it is playing up, but when only one is entered it works correctly.. any ideas? does that code look okay?</p>