472,993 Members | 2,638 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,993 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
  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");
  7. $total = mysql_num_rows($result);
  10. ?>
  12. <h2><?php echo($total . " RECORD(S) FOUND CONTAINING: <font size=2 color='#CC0000'><b>" .$searchquery ); ?></b></font></div>
  13. <p>
  15. <?php if($total>0) {
  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>");
  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!
  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. }
  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.

Sep 6 '10 #1
1 4034
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

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

Similar topics

by: Ralph Freshour | last post by:
I'm not sure the follow multiple table query is the right way to do what I need to do although it seems to be working: $php_SQL = "SELECT * ". "FROM basics, personal, photos ". "WHERE...
by: RG | last post by:
Hopefully someone here can help. I would like to perform multiple queries to a MySQL database and have all the results in 1 result set. Is this possible? TIA RG
by: Ray | last post by:
Hello all, I am attempting to delete multiple rows from multiple tables as follows: DELETE FROM attachments,responses,response_lines WHERE attachments.id IN(2,7,11) AND...
by: Matt Liverance | last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables working any faster. I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid...
by: SC | last post by:
I'm developing a site that may eventually have a very large database of users (hopefully, but who knows). It will be a community website where users can search each other (think Friendster,...
by: almu | last post by:
Hi all, I need help to build a set of queries in PHP/MySQL for a multiple-parameter search form, whereby the user will be able to search any number of ways (upto seven) Thanks
by: fusonts | last post by:
Can someon show me how to wirte a mySQL search query to search from only the open records. This is what I have but it continues to return records that are closed so I'm obviously doing something...
by: natalie99 | last post by:
Hi All :) I am trying to stipulate rules for the return of a pricing value in Access. I know this should be very easy, I simply cannot seem to make my expressions work! The two tables have...
by: ziycon | last post by:
I have a basic search function working with PHP and mysql, can anyone help me to make a function that takes a single search criteria and searches multiple tables and gives the different results back...
by: SLauren | last post by:
Hi, I have a query where i have to filter some conditions. I am trying out for a query where i have to include the combinations (1,1) and (0,-1) but i need to exclude the combination (0,1). So...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.