PHP and MySQL multiple table search query help

[craigcairley]

Hi guys and gals,

I am trying to do a search of a table in my DB but one of the fields is CompanyID which links to another table that has all the company information so I don't have to write the company details every time into our contacts DB.

However when I need to search all contacts (for example) if i need all customers in one company I do this SQL Query:

SELECT company.CompanyName,FirstName,LastName,CompanyID,CellPhone,OfficePhone,Email FROM persons,company WHERE persons.FirstName LIKE  '%tele%' || persons.LastName LIKE  '%tele%' ||  persons.Email LIKE '%tele%' OR company.CompanyName LIKE '%tele%' ORDER BY persons.FirstName

However as we all can see this is flawed due to the fact the system will duplicate info.

See code:

//RETURN RESULT
$searchquery = $_POST['search'];
$srch="%".$_POST['search']."%";
//echo "search term: " . $srch; -- Testing
$result = mysql_query ("SELECT company.CompanyName,FirstName,LastName,CompanyID,CellPhone,OfficePhone,Email FROM persons,company WHERE persons.FirstName LIKE  '$srch' || persons.LastName LIKE  '$srch' ||  persons.Email LIKE '$srch' OR company.CompanyName LIKE '$srch' ORDER BY persons.FirstName");
 
$total = mysql_num_rows($result);
 
 
?>
 
<h2><?php echo($total . " RECORD(S) FOUND CONTAINING: <font size=2 color='#CC0000'><b>" .$searchquery ); ?></b></font></div>
<p>
 
<?php if($total>0) {
 
// print table header
echo("<table cellspacing=\"0\" cellpadding=\"0\" width=\"500\" class=\"edittable\">
<tr><th>Name / Company</th><th align='center'>Office No.</th><th align='center'>Mobile No.</th></tr>");
 
// fetch the current row into the array $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$compid = $row["CompanyID"];
//I need to translat the compid above into a company name!
 
$name = $row["FirstName"] . " " . $row["LastName"] . "<br><strong>" . $compid ."</strong>";
echo("<tr>");
echo("<td><a href=\"contact.php?id=" . $row["ID"] . "&mode=read\">" . $name . "</a></td>");
echo("<td align='center'>" . formatPhone($row["OfficePhone"]) . "</td>");
echo("<td align='center'>" . formatPhone($row["CellPhone"]) . "</td>");
//echo("<td align='center'>" . formatPhone($row["HomePhone"]) . "</td>");
echo "</tr>";
}
echo("</table>");
}
 
 
mysql_close();
?>

All I need to be able to do is search the whole persons table for a specific search string! Including the company names! I just not sure how to make company.CompanyName replace persons.CompanyID when searching!

I know it's an easy one, I just haven't coded for ages and have lost the plot a little.

Cheers

[craigcairley]

Found the answer by testing my instincts:

$result = mysql_query ("SELECT persons.ID,CompanyName,FirstName,LastName,CompanyID,CellPhone,OfficePhone,Email FROM persons JOIN company ON persons.CompanyID = company.ID WHERE persons.FirstName LIKE  '$srch' || persons.LastName LIKE  '$srch' ||  persons.Email LIKE '$srch' || company.CompanyName LIKE '$srch' ORDER BY persons.FirstName");

I used the MySQL JOIN commant and joined the two table where the persons.CompanyID = company.ID Giving me the info I needed.

Anyone have another suggestion on how to do it??