title: mysql query in a loop using left join with columns the same name
body: <p>I am building a search form with multiple fields to search. Data is in 2 different tables depending on what you are searching for in each field.</p>
  
  <p>I have a column that has the same name in each table. Because of this it obviously gives me the sql error of: Column 'state' in where clause is ambiguous</p>
  
  <p>It would not be a problem if the form fields were not in a loop. I am new to php and this is trying me.</p>
  
  <p>I have tried to use aliases in the SELECT for mysql but now i cant target them because it doesnt come through correctly.</p>
  
  <p>Here is what i have for my form:</p>
  
  <pre><code> &lt;form method="post" action="index.php"&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;Name:&lt;/td&gt;
                                      &lt;td&gt;
                                          &lt;input type="text" name="display_name" id="display_name" class="display_name tt-query" autocomplete="off" spellcheck="false" placeholder="Type name of person"&gt;&lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;Service Type:&lt;/td&gt;
                                      &lt;td&gt;&lt;select name="service_name"&gt;
                                            &lt;option&gt;
                                           &lt;?php
              $query1 = "SELECT service_type FROM nfw_service_type ORDER BY id_num";
              $result1 = mysqli_query($conn, $query1); @$num_results1 = mysqli_num_rows($result1);
          ?&gt;
  
          &lt;?php /*Loop through each row and display records */
              for($i=0; $i&lt;$num_results1; $i++) { $row1 = mysqli_fetch_assoc($result1);
          ?&gt;  
  
          &lt;?php // echo 'Name' .$row['ssfullname'] . 'email' . $row['ssemail'] . "\n"; ?&gt;
  
              &lt;option value="&lt;?php print $row1['service_type']; ?&gt;"&gt;&lt;?php print $row1['service_type']; ?&gt;&lt;/option&gt;
  
          &lt;?php // end loop
      } ?&gt;
                                          &lt;/select&gt;&lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;Suburb:&lt;/td&gt;
                                          &lt;input type="text" name="suburb" id="suburb" class="suburb tt-query" autocomplete="off" spellcheck="false" placeholder="Type name of person"&gt;&lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;State:&lt;/td&gt;
                                      &lt;td&gt;
                                          &lt;select name="usersstate2"&gt;
                                            &lt;option&gt;
                                            &lt;option value="qld"&gt;QLD&lt;/option&gt;
                                            &lt;option value="sa"&gt;SA&lt;/option&gt;
                                            &lt;option value="nt"&gt;NT&lt;/option&gt;
                                            &lt;option value="wa"&gt;WA&lt;/option&gt;
                                            &lt;option value="vic"&gt;VIC&lt;/option&gt;
                                            &lt;option value="tas"&gt;TAS&lt;/option&gt;
                                            &lt;option value="act"&gt;ACT&lt;/option&gt;
                                          &lt;/select&gt;
                                      &lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;Type:&lt;/td&gt;
                                      &lt;td&gt;
                                          &lt;select name="user_type"&gt;
                                            &lt;option&gt;
                                            &lt;option value="franchise"&gt;Franchisee&lt;/option&gt;
                                            &lt;option value="regional"&gt;Regional&lt;/option&gt;
                                            &lt;option value="state"&gt;State&lt;/option&gt;
                                            &lt;option value="national"&gt;National&lt;/option&gt;
                                            &lt;option value="office"&gt;Headoffice Staff&lt;/option&gt;
                                          &lt;/select&gt;
                                      &lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;Active:&lt;/td&gt;
                                       &lt;td&gt;
                                          &lt;select name="active1"&gt;
                                            &lt;option&gt;&lt;/option&gt;
                                            &lt;option value="1"&gt;Active&lt;/option&gt;
                                            &lt;option value="0"&gt;Not Active&lt;/option&gt;
                                          &lt;/select&gt;
                                      &lt;/td&gt;
                                  &lt;/tr&gt;
                                  &lt;tr&gt;
                                      &lt;td&gt;&amp;nbsp;&lt;/td&gt;
                                      &lt;td&gt;&lt;input type="submit" name="submit" value="Search" /&gt;&lt;/td&gt;
                                  &lt;/tr&gt;
                              &lt;/form&gt;
  </code></pre>
  
  <p>And here is the code once the form is submitted:</p>
  
  <pre><code>    &lt;?php
                                      // SEARCH
  
                                      if(isset($_POST['submit'])) {
                                          // define the list of fields
                                          $fields = array('display_name', 'service_name', 'suburb', 'nfw_users.usersstate2', 'user_type', 'nfw_users.active1');
                                          $conditions = array();
  
                                          // loop through the defined fields
                                          foreach($fields as $field){
                                              // if the field is set and not empty
                                              if(isset($_POST[$field]) &amp;&amp; $_POST[$field] != '') {
  
                                                  $example = $_POST[$field];
                                                  // create a new condition while escaping the value inputed by the user (SQL Injection)
                                                  $conditions[] = "`$field` LIKE '%{$example}%'";
                                              }
  
  
                                          // builds the query
                                            $query = "SELECT
                                                      nfw_users.id_num,
                                                      nfw_users.display_name,
                                                      nfw_users.first,
                                                      nfw_users.last,
                                                      nfw_users.email,
                                                      nfw_users.mobile,
                                                      nfw_users.landline,
                                                      nfw_users.user_type,
                                                      nfw_users.suburb,
                                                      nfw_users.active AS active1,
                                                      nfw_users.state AS usersstate2,
                                                      nfw_services.state AS servicesstate3
                                                      FROM
                                                      nfw_users
                                                      left JOIN nfw_services ON nfw_services.user_id = nfw_users.id_num
                                                      LEFT JOIN nfw_service_areas ON nfw_service_areas.service_id = nfw_services.id_num ";
  
                                          if(count($conditions) &gt; 0) {
                                              // append the conditions
  
  
                                              $query .= "WHERE " . implode (' AND ', $conditions) ." GROUP BY nfw_users.id_num ORDER BY nfw_users.display_name"; // you can change to 'OR', but I suggest to apply the filters cumulative
                                          }
                                          }}
                                          $result = mysql_query($query);
  var_dump($query); 
                                          if (mysql_num_rows($result) &gt; 0) {
                                                  while ($score = mysql_fetch_assoc($result)) {
                                                      $active1 = $score['active1'];
                                                    if ($active1=='1') {
                                                      $activeother = "&lt;i class='fa fa-check' style='color:green;'&gt;&lt;/i&gt;";
                                                    }
                                                    else {
                                                      $activeother = "&lt;i class='fa fa-times' style='color:red;'&gt;&lt;/i&gt;";
                                                    }
  
                                              $content = "&lt;tr&gt;&lt;td&gt;" . $score['display_name'] . "&lt;/td&gt;&lt;td&gt;" . $score['first'] . "&lt;/td&gt;&lt;td&gt;" . $score['last'] . "  &lt;/td&gt;&lt;td&gt;" . $score['email'] . " &lt;/td&gt;&lt;td&gt; " . $score['mobile'] . " &lt;/td&gt;&lt;td&gt; " . $score['landline'] . "&lt;/td&gt;&lt;td&gt;$activeother&lt;/td&gt;&lt;td&gt; " . $score['user_type'] . "&lt;/td&gt;&lt;td&gt; " . date('d-m-Y', strtotime($score['date_join'])) . "&lt;/td&gt;&lt;td class='invoicing-columns'&gt;&lt;a class='btn btn-yellow' href='view-invoices.php?id=" . $score['id_num'] . "'&gt;&lt;i class='fa fa-eye'&gt;&lt;/i&gt;&lt;/a&gt;&lt;/td&gt;&lt;td class='invoicing-columns'&gt;&lt;a class='btn btn-red' href='del-customers.php?id=" . $score['id_num'] . "' onclick='return check();' class='delete'&gt;&lt;i class='fa fa-minus-circle'&gt;&lt;/i&gt;&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;";
                                              echo $content;
                                              }
                                              }
                                          ?&gt;
  </code></pre>
  
  <p>Yes i do know one part isnt using sqli and i will fix that.  :)</p>