Search form returning error.

cpharry

This is my code and the error is underneath.

<? 
$search = $_POST["search"];
$db = "`char`";

$connection = mysql_connect("localhost","wowbasec","PASSWORD REMOVED");

mysql_select_db("wowbasec_class", $connection);

$query = "select * from characters, location WHERE name='$search'";

$result = mysql_query($query, $connection); 

if (!$result) {
    echo 'Invalid query: ' . mysql_error(). "<br>";
    echo  'SQL Query: ' . $query. "<br>";
    die();
}

while ($r = mysql_fetch_assoc($result)) { // Begin while 
$ts = $r["name"]; 
$ts1 = $r["id"]; 
?>
<table width="1000" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><a href="view.php?id=<?php echo $ts1['id'];
										$ts1 = $_POST['id']; ?>"> <? echo $ts ?></td>
  </tr>
</table>
<? 
}
?>

Invalid query: Column 'name' in where clause is ambiguous
SQL Query: select * from characters, location WHERE name='Harry'

dagon

name is a mysql reserved word you have to surround it in back ticks, i find it preferable just to never use the reserved words.

cpharry

Still gets the same error.

Invalid query: Column 'name' in where clause is ambiguous
SQL Query: select * from characters, location WHERE name='searing gorge'

dagon

my mistake its not a reserved word, but mysql does know which table you are refering to could be (characters or location) so use either

$query = "select from characters, location WHERE characters.name='$search'";
or perhaps you mean
$query = "select from characters, location WHERE characters.name='$search' or location.name='$search' ";

cpharry

Ok it works using the bottom query you posted but whenever i search for something, the entire two databases come up.

Weedpacket

The error message is telling you that "name" is ambiguous.

You've got a "name" column in both "characters" and "location" tables and you don't say which one you mean. Do you mean "characters.name" or "location.name"?

cpharry

Well dagon helped with that and now i have....

$query = "select * from characters, location WHERE characters.name='$search' or location.name='$search' ";

As the query but whenever i search for something it just echoes the entire two databases.

cpharry

Also how do i split up the search results from the different tables because if i have two results both with the id 1 when the user clicks on the link which uses the id to get the data from the table it will get confused, so how do i split the results up into say an echoed table for the different tabled results if that makes sense.

Character Result Table

Character result id 1

Location Result Table

Location result id 1

dagon

select , c as where_from from characters WHERE characters.name='$search'
UNION
select , l as where_from from location WHERE location.name='$search'

have a look in the manual for the union syntax.