//Determine who receives bracelets and update bracelet recipients table //This is only applicable to recipients of CHARMS $sql = "SELECT DISTINCT ACCOUNT_NUM FROM sc_master WHERE REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' AND PROCESSED <> 'X' LIMIT 1000"; $qry = mysql_query($sql) or die(mysql_error()); while($r = mysql_fetch_array($qry)) { $acct = $r['ACCOUNT_NUM']; $fname = $r['FNAME']; //no data due to DISTINCT clause $lname = $r['LNAME']; //no data due to DISTINCT clause $bsql = "SELECT ID FROM sc_bracelet_recipients WHERE CONSULTANT_ID = '$acct'"; $bqry = mysql_query($bsql) or die(mysql_query()); $count = mysql_num_rows($bqry); if($count < 1) { //get count of how many bracelet records were marked $bracelets = $bracelets + 1; //update sc_master with an "X" for each bracelet needed mysql_query("UPDATE sc_master SET BRACELET = 'X' WHERE ACCOUNT_NUM = '$acct' AND REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' LIMIT 1") or die(mysql_error()); //update sc_bracelet_recipients to track these records mysql_query("INSERT INTO sc_bracelet_recipients (CONSULTANT_ID, FIRST_NAME, LAST_NAME, BATCH_MONTH) VALUES('$acct', '$fname', '$lname', '$reference')"); } //mark the record as processed to avoid reprocessing it on the next pass mysql_query("UPDATE sc_master SET PROCESSED = 'X'") or die(mysql_error()); }
I have a script that we run once a month on a large 'awards' list provided by a customer. A recipient can be on the list more than once for different achievements, and for certain awards they may receive a charm for a bracelet. If they are receiving a charm for the first time we must send them a charm bracelet, however if they have already received a bracelet all we send is the charm.
Here's a summary of steps:
Is the recipient award associated with a charm?Yes - check the 'bracelets' table to see if they have received a bracelet already
If they haven't received a bracelet:
update the bracelets table
indicate that the record receives a bracelet
if this recipient receives multiple awards, only mark 1 record for a bracelet
[*]Mark all records that have been processed as processed
The 'batch' of data that we process is anywhere from 5,000 to 15,000 records per month. The logic noted above runs (loops) for each record in the 'batch' and the bracelet table contains a couple hundred thousand records. This just kills the server... so, you'll see that I limit the primary query to 1,000 records, this is due to how slow this script is (the tables contain hundreds of thousands of records).
I'm guessing there's someone far smarter than I that can probably handle all of this in one beautiful and much faster query, preferably fast enough to process the entire list in one run vs. breaking it into groups of 1,000 per run.
Thanks in advance for your help!
Rumble