asked on

Help needed joining multiple tables for a email

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

Ess Kay

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

jej07

ASKER

That makes sense. Can you think of a way to work around the improper structure until it can be fixed?

ASKER CERTIFIED SOLUTION

Ess Kay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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.

Ess Kay

thanks ray, i was thinking that but failed to mention it. gotta give the people what they want

Ray Paseur

:-)

jej07

ASKER

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?

Ess Kay

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

jej07

ASKER

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.