i am trying to select information from my mysql database with this statement after making a successful connection to the database:
$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%'";
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.
I want to extract the information from these tables, and from these fields:
-markers:
author
title
bike
id
date
-imagemarkers:
-author
-title
-id
-date
-users:
-loction
-email
-username
-id
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:
markers.title
imagemarkers.title
markers.author
imagemarkers.author
markers.date
imagemarkers.date
markers.id
imagemarkers.id
users.id
And that the statement might look something like:
SELECT markers.author
FROM markers JOIN imagemarkers ON markers.author = imagemarkers.author
But i'm not sure how to make this work with the amount of information I need to retrieve.
The whole code i have at the moment looks like this:
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>";
} }
Can anyone offer me any help?
Thanks very much.
thats because fields are named the same way
try:
$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%'";
and so on