title: Column in field list is ambiguous MYSQL, PHP, SEARCH, SELECT
body: <p>i am trying to select information from my mysql database with this statement after making a successful connection to the database: </p>
  
  <pre><code>$query = "SELECT * FROM `users`, `markers`,`imagemarkers` 
      WHERE username LIKE '%$s%' 
      OR email LIKE '%$s%' 
      OR location LIKE '%$s%' 
      OR author LIKE '%$s%' 
      OR bike LIKE '%$s%' 
      OR id LIKE '%$s%' 
      OR title LIKE '%$s%'";
  </code></pre>
  
  <p>and i am getting this error: Column 'author' in where clause is ambiguous. Which i understand to be because i have multiple tables with the same field name.</p>
  
  <p>I want to extract the information from these tables, and from these fields:</p>
  
  <pre><code> -markers:
      author
      title
      bike
      id
      date
  
   -imagemarkers:
     -author
     -title
     -id
     -date
  
   -users:
     -loction
     -email
     -username
     -id
  </code></pre>
  
  <p>I have searched for the solution and have so far come to the conclusion that each table field should be refered to as something like: </p>
  
  <pre><code>  markers.title
    imagemarkers.title
  
    markers.author
    imagemarkers.author
  
    markers.date
    imagemarkers.date
  
    markers.id
    imagemarkers.id
    users.id
  </code></pre>
  
  <p>And that the statement might look something like:</p>
  
  <pre><code>  SELECT markers.author
      FROM markers JOIN imagemarkers ON markers.author = imagemarkers.author
  </code></pre>
  
  <p>But i'm not sure how to make this work with the amount of information I need to retrieve.</p>
  
  <p>The whole code i have at the moment looks like this: </p>
  
  <pre><code>        if (isset($_POST['submit'])) {
      if ($_POST['search'] !="") {
      require("connection.php");
  
      $s = mysql_real_escape_string($_POST['search']);
      $query = "SELECT * FROM `users`, `markers`,`imagemarkers` WHERE username LIKE '%$s%' OR email LIKE '%$s%' OR location LIKE '%$s%' OR author LIKE '%$s%' OR bike LIKE '%$s%' OR id LIKE '%$s%' OR title LIKE '%$s%'";
      $result = mysql_query($query, $connect)
          or die(mysql_error());
  
      $num = mysql_num_rows($result);
  
      echo "&lt;h2&gt;you searched for: " . $s . "..&lt;/h2&gt;";
      echo "&lt;h5&gt;there are " . $num . " results&lt;/h4&gt;";
  
  while ($row = mysql_fetch_assoc($result)) {
      echo "&lt;p&gt;username: " . $row['username'] . "&lt;br /&gt;";
      echo "location: " . $row['location'] . "&lt;/p&gt;";
      echo "author: " . $row['author'] . "&lt;/p&gt;";//error: Column 'author' in where clause is ambiguous, same with date. 
      echo "date: " . $row['date'] . "&lt;/p&gt;";
      echo "id: " . $row['id'] . "&lt;/p&gt;";
      echo "title: " . $row['title'] . "&lt;/p&gt;"; 
      echo "&lt;hr /&gt;";
  
      }
  } else {
          echo "&lt;h3&gt; you must type something in the box&lt;/h3&gt;";
  }  }
  </code></pre>
  
  <p>Can anyone offer me any help?</p>
  
  <p>Thanks very much. </p>