Solved

Help needed joining multiple tables for a email

Posted on 2014-04-21
9
257 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
 
LVL 108

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 108

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now