2 Table Search Query

[brussell]

Trying to SELECT * from 2 tables (customerdetails & rackallocation (as below)) but am getting ambiguous errors for the 'companyname' column due to it being a primary key in 'customerdetails' and foreign key in 'rackallocation'. Please can you help spot my error or advise on additional code required.

CREATE TABLE `customerdetails` (
`companyname` varchar(50) NOT NULL, 
`contactname` varchar(30) default NULL,
`phonenumber` varchar(15) default NULL,
`emailaddress` varchar(50) default NULL,
PRIMARY KEY  (`companyname`))
ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `rackallocation` (
`racklocation` varchar(15) NOT NULL,
`racktype` char(4) default NULL,
`companyname` varchar(50) NOT NULL,
`gwname` varchar(30)default NULL, 
PRIMARY KEY  (`racklocation`),
KEY `companyname` (`companyname`),
FOREIGN KEY (`companyname`) REFERENCES `customerdetails` (`companyname`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE=InnoDB DEFAULT CHARSET=latin1

QUERY:
$query = "select * from customerdetails, rackallocation where ".$searchtype." like '%".$searchterm."%' and customerdetails.companyname = rackallocation.companyname";
$result = mysql_query($query) or die('Problem with query: ' . $query . '<br />' . mysql_error());
$num_results = mysql_num_rows($result);

[jvrothjr]

look into joins.....

[fenway]

[code]"select c.*, r.*
FROM customerdetails AS c
JOIN rackallocation AS r ON (c.companyname = r.companyname)
WHERE ".$searchtype." like '%".$searchterm."%' "[/code]

Obviously, you'll have to decide which table the search term is in.