Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help needed joining multiple tables for a email

Posted on 2014-04-21
9
Medium Priority
?
277 Views
Last Modified: 2014-04-24
I have an app that allows a user to search for a contractor near them. After the search, their name is logged into the "locator_leads" table, along with the ids for the contractors they found.
locator_leads TABLE

| id  | firstname | applicationID
---------------------------------------
| 1   | John            | 201,202          |
| 2   | Jane            | 202                   |
| 3   | Jim              | 201,204,308   |

Open in new window

The contractors can be found in the application_entries table, along with the distributors they purchase from.
application_entries TABLE

| id    | company_name     | purchase_from_company1 | purchase_from_company2 | purchase_from_company3 |
| 201 | Contractor One      | 5      | 106    |     |
| 202 | Contractor Two      | 10     | 5        |     |

Open in new window

Finally, the distributors are listed in the locator_leads_dist_contacts table.
locator_leads_dist_contacts TABLE

| id    | company_name      | lead_contact_email                                                                                                                          |
| 5     | Distributor One       | distributor1@gmail.com                                                                                                                 | 
| 10   | Distributor Two       | distributor2@gmail.com, distributor2.2@gmail.com, distributor3.2@gmail.com | 
| 106 | Distributor Three   | distributor3@gmail.com, distributor3.2@gmail.com                                                         |

Open in new window

What I would like to do is once a week, email a report to distributors who have had a contractor appear in the search. The report would include what contractor appeared, along with the first name of their lead.

So for example because Distributor One had a contractor appear, at the end of the week they would get a report showing: Contractor One, John, Contractor Two, John

Distributor Three's report would only show Contractor One, John.

I have been able to join and retrieve all the information needed for the emails. However, I can't figure out how to group or sort so I only send one email to each distributor's lead_contact_email address.

Any help you could provide would be greatly appreciated.
$query_lead = mysql_query("SELECT created, firstname, applicationID FROM locator_leads WHERE DATE(locator_leads.created) >=  (CURDATE() -  INTERVAL 1 WEEK)");

while($leadArr = mysql_fetch_object($query_lead))
{
	
$contractorIDArray = explode(",",$leadArr->applicationID);

	  foreach ( $contractorIDArray as $contractorValue ) { //01

		$query_dist = mysql_query("SELECT
				`application_entries`.`company_name`,
				`dist1`.`company_name` AS `first_dist`,
				`dist2`.`company_name` AS `second_dist`,
				`dist3`.`company_name` AS `third_dist`,
				`dist1`.`lead_contact_email`AS `first_email`,
				`dist2`.`lead_contact_email`AS `second_email`,
				`dist3`.`lead_contact_email`AS `third_email`
				FROM application_entries
				  LEFT JOIN  locator_leads_dist_contacts AS dist1
				  ON         dist1.id = application_entries.purchase_from_company1
				  LEFT JOIN  locator_leads_dist_contacts AS dist2
				  ON         dist2.id = application_entries.purchase_from_company2  
				  LEFT JOIN  locator_leads_dist_contacts AS dist3
				  ON         dist3.id = application_entries.purchase_from_company3
				  WHERE `application_entries`.`id` ='$contractorValue'
				  ");

		  while($distArr = mysql_fetch_object($query_dist))
		  { //02

					
					echo 'Lead: '. $leadArr->firstname .'<br />' . PHP_EOL;
					echo 'Contractor: '. $distArr->company_name .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;&nbsp;&nbsp;AAPD 1: '. $distArr->first_dist .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;AAPD 1: '. strtolower($distArr->first_email) .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;&nbsp;&nbsp;AAPD 1: '. $distArr->second_dist .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;AAPD 2: '. strtolower($distArr->second_email) .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;&nbsp;&nbsp;AAPD 1: '. $distArr->third_dist .'<br />' . PHP_EOL;
					echo '&nbsp;&nbsp;AAPD 2: '. strtolower($distArr->third_email) .'<br />' . PHP_EOL;
					echo '<hr>';

		  } //02

	  }

}

Open in new window

0
Comment
Question by:jej07
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40013468
You need a better structure for table

Adding all IDs into one field will slow your system


for example

locator_leads TABLE   should be like this:

Lead ID | id  | firstname | applicationID 
---------------------------------------
 1          | 1   | John            | 201
 2          | 1   | John            | 202
3           | 2   | Jane            | 202                   
4           | 3   | Jim              | 201
5           | 3   | Jim              | 204
6           | 3   | Jim              | 308

Open in new window




Same with application_entries

application_entries TABLE

| id    | company_name     | purchase_from_company |
| 201 | Contractor One      | 5      |  
| 201 | Contractor One      | 106  |

Open in new window


Once that is done

SELECT *
FROM locator_leads A  LEFT JOIN
     application_entries B ON A.applicationID = B.ID  CROSS JOIN
     locator_leads_dist_contacts C ON B.purchase_from_company  =  C.ID
0
 
LVL 1

Author Comment

by:jej07
ID: 40014671
That makes sense. Can you think of a way to work around the improper structure until it can be fixed?
0
 
LVL 15

Accepted Solution

by:
Ess Kay earned 2000 total points
ID: 40014850
sure, but it will strain your database. If your db is big enough, it might give you timeouts


I guess you can replace the = with LIKE

and try something like this

SELECT *
FROM locator_leads A  LEFT JOIN
     application_entries B ON B.ID LIKE '%' & A.applicationID & '%'   LEFT JOIN
     locator_leads_dist_contacts C ON B.purchase_from_company1  =  C.ID LEFT JOIN
 locator_leads_dist_contacts D ON B.purchase_from_company2   =  D.ID LEFT JOIN
 locator_leads_dist_contacts E ON B.purchase_from_company3   =  E.ID
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40014921
A possible risk in using LIKE may arise because 1 is LIKE 11, 13, 111, 74391, etc.  You want to have unique keys that match exactly, and you want indexes on these key columns.  You can use ALTER TABLE to add the appropriate columns to your tables.  @esskayb2d is giving you correct advice here.  For an interesting perspective on the topic, make a Google search for the exact phrase, "Should I Normalize my Database" and read the interesting writings on the various sides of the question.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40014951
thanks ray, i was thinking that but failed to mention it. gotta give the people what they want
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40015075
:-)
0
 
LVL 1

Author Comment

by:jej07
ID: 40015635
The application_entries and locator_leads_dist_contacts tables will not have more than 500 rows. The locator_leads table however will continue to grow.

There were some pretty strong arguments for normalizing the database. I do believe that's the direction I will need to take. For the now I was just hoping there would be a "down and dirty" solution.

Is it possible to sort out and group my query results above using php?
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40020759
Are you asking if it's possible, or for someone to write it for you?

Is it possible? Yes

Will we write it for you? Perhaps, but you will need to create a new question with PHP tag
0
 
LVL 1

Author Closing Comment

by:jej07
ID: 40021812
I thought it could be done with one query, but ended up using three. Thank you though, as this was a huge help.

Next week I plan on going back and trying to fix the table structure. Thank you for that tip as well.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This video teaches users how to migrate an existing Wordpress website to a new domain.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question