Jump to content

Rumblefish

Members
  • Posts

    3
  • Joined

  • Last visited

About Rumblefish

  • Birthday 02/21/1969

Profile Information

  • Location
    Idaho, USA

Rumblefish's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. JSG, Array question. The number of records captured in the array doesn't match the number of records in the table. Any ideas as to what may cause this? It's no bueno. Array Count = 97,317 Table Rows = 97,492 Still timing out after about 5-1/2 minutes, even using the array. I'm thinking I would see significant gains if I could get access to the record ID from the DISTINCT query. I've always been irritated that you can't get any other data except for the DISTINCT field. SELECT DISTINCT ACCOUNT_NUM FROM sc_master WHERE REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' I think the real culprit is the update query, especially because of the LIKE clause: UPDATE sc_master SET BRACELET = 'X' WHERE ACCOUNT_NUM = '$acct' AND REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' If I could simply collect the ID from the SELECT query and use the ID to update the record instead of having to find a corresponding record to post to, I'm guessing this will run very quickly. I ran a test script that identifies the records but doesn't run update or insert queries ant the script ran in a few seconds. Is there any way possible to collect the ID from the record returned via the DISTINCT query? Or, a different way to identify that record without such a cumbersome update query? Thanks again, Rumble
  2. Thanks jsg. I had considered storing that data in an array but wasn't sure how well an array would handle over 200k records, even if it was just an account number. Never tested that though. Regarding the upcoming changes with php, that really worries me as I have many thousands of lines of code written pretty much the way you see here, copied across a dozen or so platforms - that update will be brutal. Has the date been set for this next PHP release? Thanks again for your note. I'll try the array and follow up with the results. Best, R
  3. //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
×
×
  • Create New...