php - 字段列表中的列不明确 MYSQL、PHP、SEARCH、SELECT

Question

在成功连接到数据库后，我正在尝试使用此语句从我的 mysql 数据库中选择信息:

$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%'";

我收到此错误:where 子句中的“作者”列不明确。据我所知，这是因为我有多个具有相同字段名称的表。

我想从这些表和这些字段中提取信息:

 -markers:
    author
    title
    bike
    id
    date

 -imagemarkers:
   -author
   -title
   -id
   -date

 -users:
   -loction
   -email
   -username
   -id

我已经搜索了解决方案，到目前为止得出的结论是每个表字段都应该像这样引用:

  markers.title
  imagemarkers.title

  markers.author
  imagemarkers.author

  markers.date
  imagemarkers.date

  markers.id
  imagemarkers.id
  users.id

声明可能类似于:

  SELECT markers.author
    FROM markers JOIN imagemarkers ON markers.author = imagemarkers.author

但我不确定如何使用我需要检索的信息量来完成这项工作。

我目前的整个代码如下所示:

        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 "<h2>you searched for: " . $s . "..</h2>";
    echo "<h5>there are " . $num . " results</h4>";

while ($row = mysql_fetch_assoc($result)) {
    echo "<p>username: " . $row['username'] . "<br />";
    echo "location: " . $row['location'] . "</p>";
    echo "author: " . $row['author'] . "</p>";//error: Column 'author' in where clause is ambiguous, same with date. 
    echo "date: " . $row['date'] . "</p>";
    echo "id: " . $row['id'] . "</p>";
    echo "title: " . $row['title'] . "</p>"; 
    echo "<hr />";

    }
} else {
        echo "<h3> you must type something in the box</h3>";
}  }

谁能给我任何帮助？

非常感谢。

Answer 1

那是因为字段的命名方式相同

尝试:

$query = "SELECT * FROM `users` u, `markers` m,`imagemarkers` im
    WHERE u.username LIKE '%$s%' 
    OR u.email LIKE '%$s%' 
    OR u.location LIKE '%$s%' 
    OR m.author LIKE '%$s%' 
    OR m.bike LIKE '%$s%' 
    OR m.title LIKE '%$s%'";

等等