471,337 Members | 992 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

PHP and MySQL multiple table search query help

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:

Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. //RETURN RESULT
  2. $searchquery = $_POST['search'];
  3. $srch="%".$_POST['search']."%";
  4. //echo "search term: " . $srch; -- Testing
  5. $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");
  6.  
  7. $total = mysql_num_rows($result);
  8.  
  9.  
  10. ?>
  11.  
  12. <h2><?php echo($total . " RECORD(S) FOUND CONTAINING: <font size=2 color='#CC0000'><b>" .$searchquery ); ?></b></font></div>
  13. <p>
  14.  
  15. <?php if($total>0) {
  16.  
  17. // print table header
  18. echo("<table cellspacing=\"0\" cellpadding=\"0\" width=\"500\" class=\"edittable\">
  19. <tr><th>Name / Company</th><th align='center'>Office No.</th><th align='center'>Mobile No.</th></tr>");
  20.  
  21. // fetch the current row into the array $row
  22. while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
  23. {
  24. $compid = $row["CompanyID"];
  25. //I need to translat the compid above into a company name!
  26.  
  27. $name = $row["FirstName"] . " " . $row["LastName"] . "<br><strong>" . $compid ."</strong>";
  28. echo("<tr>");
  29. echo("<td><a href=\"contact.php?id=" . $row["ID"] . "&mode=read\">" . $name . "</a></td>");
  30. echo("<td align='center'>" . formatPhone($row["OfficePhone"]) . "</td>");
  31. echo("<td align='center'>" . formatPhone($row["CellPhone"]) . "</td>");
  32. //echo("<td align='center'>" . formatPhone($row["HomePhone"]) . "</td>");
  33. echo "</tr>";
  34. }
  35. echo("</table>");
  36. }
  37.  
  38.  
  39. mysql_close();
  40. ?>
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
Sep 6 '10 #1
1 3958
Found the answer by testing my instincts:
Expand|Select|Wrap|Line Numbers
  1. $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??
Sep 6 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Ralph Freshour | last post: by
3 posts views Thread by RG | last post: by
1 post views Thread by Ray | last post: by
1 post views Thread by SC | last post: by
12 posts views Thread by ziycon | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.