Solved

Help needed joining multiple tables for a email

Posted on 2014-04-21
9
261 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
  • 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 500 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 109

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 109

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The viewer will learn how to count occurrences of each item in an array.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

773 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