So here is my scenario, I have two databases.
Database 1 is called store_sales and it has a table called transactions.
Here is the structure of the table
**transactions table**
CustomerFName, CustomerLName, Address, City, State, Zip, transNumber, dealDate, customerNumber, itemBought, matched, newCustomer
Database 2 is called contacts and has a table called people.
Here is the structure for the people table.
**people table**
CustomerFName, CustomerLName, Address, City, State, Zip, campaignName, campaignStartDate, campaignEndDate
The peoples table is a list of contacts I have that I do marketing to, I have a list of say 400k records in my people table. Every time I do marketing to a record, I assign a campaignName to a record, so I know who I sent marketing material to in a campaign. So if I start a campaign called summer-sale2016 and I include 100k emails in that campaign then 100k records would have “summer-sale2016” as their campaignName record.
The transactions table is a list of all transactions ever for a store since a store opened, so if a store has been open for 8 years maybe it has 17k transactions, which means I have 17k records in that database.
Before I get into how far I’ve gotten into this program let me tell you what I plan for this program to do.
I would like to select all records from my people table that have a specific campaignName and check if the address and city from a people table match any record with the same address and city in the transactions table where the transaction “dealDate” cell value is between the people campaignStartDate and campaignEndDate if there is a match then it means this transaction was most likely a result of my marketing campaign.
Once a match is found from the above conditions then I would like to update the transaction matched cell for the record to the campaign name that was used for the matching people record, What I would like to do then is check if the matched transaction record has any prior history in the store, if it does then it means its no a new customer but if it does not have prior history with the store then it means the customer is new and I am reaching new people.
So that is what I would like to do.
Here is what I am doing (which either does not work or is too slow and eventually errors out).
Step 1
Select all records from people table that match a specific campaign name.
Step 2
Create a foreach loop that iterates through the array of people records found, every time I loop I have a PDO select that looks like this.
$db->$query->prepare("SELECT * from transactions WHERE Address=? AND City=? AND dealDate Between ? AND ?");
foreach($dataIgotFromStep1 as $key){
$db->execute(array($key['Address'], $key['City'], $key['campaignStartDate'], $key['CampaignEndDate']));
}
Step 3
In this step I check to see if anything was found, if it was then it means a record matched, so I then do another SQL query this time updating the matched record in the transactions table.
As you can imagine, this takes for ever and does a ton of queries in one script.
It works but takes a very long time. I know this is not a good way to do things, I need advice on a better way to do this.
Thank you in advance I appreciate the help