Solved

how to properly insert but when duplicate found update in mysql database

Posted on 2014-10-02
5
476 Views
Last Modified: 2014-10-03
i have been struggling with ON DUPLICATE KEY UPDATE. Im now wondering if i am doing this right.
i have a array that has 0 to 999 rows with multiple rows of data (field name with values)
array sample:
Array
(
    [0] => Array
        (
            [A10F] => "
            [A10L] => Not Applicable
            [A10N] => 
            [A10S] => 
            [A10W] => 
            [A1F] => 
            [A1L] => Main Level
            [A1N] => Deck
            [A1S] => 12X10
            [A1W] => 
            [A2F] => Vinyl
            [A2L] => Main Level
            [A2N] => Foyer
            [A2S] => 9X11
            [A2W] => 
            [A3F] => 
            [A3L] => Not Applicable
            [A3N] => 
            [A3S] => 
            [A3W] => 
            [A4F] => 
            [A4L] => Not Applicable
            [A4N] => 
            [A4S] => 
            [A4W] => 
            [A5F] => 
            [A5L] => Not Applicable
            [A5N] => 
            [A5S] => 
            [A5W] => 
            [A6F] => 
            [A6L] => Not Applicable
            [A6N] => 
            [A6S] => 
            [A6W] => 
            [A7F] => 
            [A7L] => Not Applicable
            [A7N] => 
            [A7S] => 
            [A7W] => 
            [A8F] => 
            [A8L] => Not Applicable
            [A8N] => 
            [A8S] => 
            [A8W] => 
            [A9F] => 
            [A9L] => Not Applicable
            [A9N] => 
            [A9S] => 
            [A9W] => 
            [AAN] => 
            [ACR] => .2157
            [ADDITIONAL_ROOMS] => Deck,Foyer
            [ADDITIONAL_SALES_INFO] => Short Sale
            [ADI] => Yes
            [AGE] => 91-100 Years
            [AGENTREMARKS] => This is a short sale.  Basement stairs may be unsafe.  Attic access with caution, ask before you try to enter attic. Any reduction in broker commission will be split equally between brokers.
            [AIR] => Central Air
            [ALLOW_COMMENTS_REVIEWS] => Y
            [ALT_ELEMENTARY_NAME] => 
            [ALT_HIGH_NAME] => 
            [ALT_JUNIORHIGH_NAME] => 
            [AMN] => 
            [AON] => No
            [APP] => 
            [AR] => Downers Grove
            [ASF] => 1452
            [ASFSOURCE] => Assessor
            [ASM] => 0
            [ATC] => 
            [ATTDISCL] => 
            [AUCT_PRICE_TYPE] => 
            [AUCTION_DATE] => 
            [AZN] => 
            [B2F] => Carpet
            [B2L] => Main Level
            [B2S] => 10X9
            [B2W] => 
            [B3F] => Carpet
            [B3L] => Main Level
            [B3S] => 10X9
            [B3W] => 
            [B4F] => 
            [B4L] => Not Applicable
            [B4S] => 
            [B4W] => 
            [B78] => Yes
            [BAS] => Cellar
            [BAT] => 
            [BB] => No
            [BID_RESERVE_PRICE] => 
            [BLT] => 1922
            [BMD] => 
            [BMT] => Partial
            [BOARDNUM] => 10
            [BR] => 3
            [BRALL] => 3
            [BRBELOW] => 0
            [BTH] => 2
            [C35] => 
            [C57] => 
            [C810] => 
            [CARS] => 2
            [CC] => 2.5-$200
            [CIT] => DOWNERS GROVE
            [CLOSEDDATE] => 
            [CNY] => Du Page
            [COLISTER] => 
            [COMPPAIDON] => Net Sale Price
            [CONTRACTDATE] => 2014-08-28T05:00:00
            [CONTTOSHOW] => No - has seller written direction
            [CP] => 
            [CRP] => DOWNERS GROVE
            [CTGF] => Short Sale
            [CURRENTLYLEASED] => No
            [DEED_GARAGE_COST] => 
            [DEED_PARKING_COST] => 
            [DIM] => 49 X 182
            [DIN] => Separate
            [DIR] => Main St. to 55th, East to Washington, South to home
            [DISABILITY_ACCESS] => No
            [DISABILITY_ACCESS_DETAILS] => 
            [DPR] => Yes
            [DRF] => Hardwood
            [DRL] => Main Level
            [DRS] => 16X12
            [DRV] => Asphalt
            [DRW] => Blinds
            [E] => 
            [ELC] => 
            [EQP] => 
            [EXISTING_BF] => 
            [EXP] => 
            [EXT] => Vinyl Siding
            [EXT_PROP_FEATS] => Deck,Porch,Storms/Screens
            [FEE_LEASE_AMOUNT] => 
            [FIN] => 
            [FIREPLACE_LOCATION] => 
            [FND] => Block
            [FP] => 0
            [FPL] => 
            [FRANCHISOR_FEED] => No
            [FREQ] => Not Applicable
            [FRF] => Hardwood
            [FRL] => Main Level
            [FRS] => 12X13
            [FRW] => 
            [FULL_BATHS] => 2
            [GAR] => Garage Door Opener(s),Transmitter(s)
            [GARAGE_ONSITE] => Yes
            [GARAGE_OWNERSHIP] => Owned
            [GARAGE_TYPE] => Detached
            [GREENDISCL] => 
            [GREENFEAT] => 
            [GREENSRC] => 
            [GRID] => 
            [GS] => FAIRMOUNT ELEMENTARY SCHOOL
            [GSD] => 58
            [HALF_BATHS] => 0
            [HEA] => Gas,Forced Air
            [HEM] => Yes
            [HERS] => 
            [HS] => SOUTH HIGH SCHOOL
            [HSD] => 99
            [HSN] => 5721
            [IDX] => Y
            [IMPS] => Sewer-Public
            [IMPW] => Lake Michigan
            [INF] => Commuter Train
            [INT_PROP_FEATS] => Hardwood Floors,1st Floor Bedroom,1st Floor Laundry,1st Floor Full Bath
            [INTERNET_AVM] => Y
            [INTERNETLISTING] => All
            [JH] => O NEILL MIDDLE SCHOOL
            [JHD] => 58
            [KIT] => Eating Area-Table Space
            [KTF] => Vinyl
            [KTL] => Main Level
            [KTS] => 14X13
            [KTW] => 
            [LAADDRESS2] => 
            [LACELLPHONE] => (630) 464-2262
            [LACITY] => 
            [LAEMAIL] => john.majewski@cbexchange.com
            [LAFAXNUMBER] => (781) 609-1882
            [LAFIRSTNAME] => John
            [LAID] => 242559
            [LALASTNAME] => Majewski
            [LAOFFICEPHONE] => 
            [LAPAGERNUMBER] => 
            [LASTATE] => IL
            [LASTREETNAME] => 
            [LASTREETNUMBER] => 
            [LAT] => 41.783329
            [LAUNDRYF] => 
            [LAUNDRYL] => Not Applicable
            [LAUNDRYS] => 
            [LAUNDRYW] => 
            [LAZIP] => 
            [LD] => 2014-08-09T05:00:00
            [LDR] => 2014-08-12T17:04:32
            [LDS] => Corner
            [LEASEXD] => 
            [LIST] => Exclusive Right To Sell
            [LLP] => 215000
            [LMT] => 55
            [LN] => 08699407
            [LNG] => -88.005173
            [LO_LOCATION] => 20179
            [LOCITY] => Downers Grove
            [LOCKBOX] => 
            [LOEMAIL] => 
            [LOFAXNUMBER] => (630) 964-4105
            [LOID] => 20179
            [LONAME] => Coldwell Banker Residential
            [LOPHONE] => (630) 964-9696
            [LOSTATE] => IL
            [LOSTREETNAME] => Main Street
            [LOSTREETNUMBER] => 5114
            [LOWWW] => 
            [LOZIP] => 60515
            [LP] => 150000
            [LRF] => Hardwood
            [LRL] => Main Level
            [LRS] => 12X13
            [LRW] => Blinds
            [LSZ] => Less Than .25 Acre
            [MAI] => None
            [MANAGECOMPANY] => 
            [MANAGECONTACT] => 
            [MANAGEPHONE] => 
            [MANAGINGBROKER] => No
            [MASTER_ASSOC_FEE] => 
            [MBB] => None
            [MBF] => Hardwood
            [MBL] => Main Level
            [MBS] => 11X11
            [MBW] => 
            [MC] => Active
            [MODx] => RANCH
            [MPN] => No
            [MT] => 55
            [N] => 
            [NC] => N
            [NCO] => 
            [NO_GARAGE_SPACES] => 2
            [NO_PARKING_SPACES] => 
            [OD] => 
            [OLP] => 215000
            [OMD] => 
            [OPH] => 
            [OSD] => 
            [OT] => 
            [OWN] => Fee Simple
            [OWR] => OOR
            [PAR] => 
            [PAR_FEE_LEASE_AMOUNT] => 
            [PARKING_ONSITE] => 
            [PARKING_OWNERSHIP] => 
            [PHO] => 
            [PHOTOCOUNT] => 11
            [PHOTODATE] => 2014-08-12T17:04:32
            [PIN] => 0917107015
            [PKN] => Garage
            [PNT] => 
            [POS] => Closing
            [POST_DIRECTION] => 
            [RECORDMODDATE] => 2014-09-02T14:26:13
            [REMARKS] => Terrific YMCA location!  Ranch home on a nice corner lot.      Area of new construction. Call village zoning to get building requirements for new construction.  Rehab would need significant foundation work.  Use caution when entering the basement.  This home will not qualify for an FHA loan.  This is a short sale.
            [REMARKSINTERNET] => Yes
            [TYP] => Detached Single
            [RMS] => 7
            [ROF] => Asphalt/Glass (Shingles)
            [RR] => No
            [S] => 
            [SAADDRESS2] => 
            [SACELLPHONE] => 
            [SACITY] => 
            [SAEMAIL] => 
            [SAFAXNUMBER] => 
            [SAFIRSTNAME] => 
            [SAID] => 
            [SALASTNAME] => 
            [SALE_OR_RENT] => No
            [SALE_OR_RENT_LN] => 
            [SAOFFICEPHONE] => 
            [SAPAGERNUMBER] => 
            [SAS] => N
            [SASTATE] => 
            [SASTREETNAME] => 
            [SASTREETNUMBER] => 
            [SAZIP] => 
            [SCI] => Short Sale
            [SECURESHOWASSIST] => 
            [SELLERSCONC] => 
            [SHO] => Use Showing Assist
            [SHORT_SALE] => 
            [SO_LOCATION] => 
            [SOCITY] => 
            [SOEMAIL] => 
            [SOFAXNUMBER] => 
            [SOID] => 
            [SONAME] => 
            [SOPHONE] => 
            [SOSTATE] => 
            [SOSTREETNAME] => 
            [SOSTREETNUMBER] => 
            [SOWWW] => 
            [SOZIP] => 
            [SP] => 
            [SP_INCL_PARKING] => Yes
            [SPEC_SVC_AREA] => N
            [SPEC_SVC_FEE] => 
            [ST] => Contingent
            [STATE] => Illinois
            [STD] => 2014-08-29T15:33:29
            [STR] => Washington
            [STREETSUFFIX] => Street
            [STY] => Ranch
            [SUB] => 
            [TAX] => 3970
            [TOURURL] => 
            [TPE] => 1 Story
            [TRM] => 
            [TWN] => DOWNERS GROVE
            [TXC] => Homeowner
            [TXY] => 2013
            [UD] => 2014-09-02T14:26:13
            [VIRTUALPHOTOS] => No
            [VT] => None
            [VTDATE] => 
            [W] => 
            [WF] => No
            [XD] => 
            [ZP] => 60516
            [ZP4] => "
        )

    [1] => Array
        (
            [A10F] => "
            [A10L] => Not Applicable
            [A10N] => 
            [A10S] => 
            [A10W] => 
            [A1F] => 
            [A1L] => Not Applicable
            [A1N] => 
            [A1S] => 
            [A1W] => 
            [A2F] => 
            [A2L] => Not Applicable
            [A2N] => 
            [A2S] => 
            [A2W] => 
            [A3F] => 
            [A3L] => Not Applicable
            [A3N] => 
            [A3S] => 
            [A3W] => 
            [A4F] => 
            [A4L] => Not Applicable
            [A4N] => 
            [A4S] => 
            [A4W] => 
            [A5F] => 
            [A5L] => Not Applicable
            [A5N] => 
            [A5S] => 
            [A5W] => 
            [A6F] => 
            [A6L] => Not Applicable
            [A6N] => 
            [A6S] => 
            [A6W] => 
            [A7F] => 
            [A7L] => Not Applicable
            [A7N] => 
            [A7S] => 
            [A7W] => 
            [A8F] => 
            [A8L] => Not Applicable
            [A8N] => 
            [A8S] => 
            [A8W] => 
            [A9F] => 
            [A9L] => Not Applicable
            [A9N] => 
            [A9S] => 
            [A9W] => 
            [AAN] => CELL 847-732-3776
            [ACR] => .2204
            [ADDITIONAL_ROOMS] => No additional rooms
            [ADDITIONAL_SALES_INFO] => None
            [ADI] => Yes
            [AGE] => 41-50 Years
            [AGENTREMARKS] => PLEASE REMOVE SHOES WHEN SHOWING-LOCK DOORS AND LEAVE A CARD PLEASE
            [AIR] => Central Air
            [ALLOW_COMMENTS_REVIEWS] => Y
            [ALT_ELEMENTARY_NAME] => 
            [ALT_HIGH_NAME] => 
            [ALT_JUNIORHIGH_NAME] => 
            [AMN] => 
            [AON] => No
            [APP] => 
            [AR] => Wheeling
            [ASF] => 1800
            [ASFSOURCE] => Landlord/Tenant/Seller
            [ASM] => 0
            [ATC] => 
            [ATTDISCL] => 
            [AUCT_PRICE_TYPE] => 
            [AUCTION_DATE] => 
            [AZN] => 
            [B2F] => Hardwood
            [B2L] => 2nd Level
            [B2S] => 14X10
            [B2W] => 
            [B3F] => Hardwood
            [B3L] => 2nd Level
            [B3S] => 12X10
            [B3W] => 
            [B4F] => Hardwood
            [B4L] => 3rd Level
            [B4S] => 24X14
            [B4W] => 
            [B78] => Yes
            [BAS] => Crawl
            [BAT] => 
            [BB] => 
            [BID_RESERVE_PRICE] => 
            [BLT] => 1967
            [BMD] => 
            [BMT] => None
            [BOARDNUM] => 10
            [BR] => 4
            [BRALL] => 4
            [BRBELOW] => 0
            [BTH] => 3
            [C35] => 
            [C57] => 
            [C810] => 
            [CARS] => 2
            [CC] => 2.5%-$295
            [CIT] => WHEELING
            [CLOSEDDATE] => 
            [CNY] => Cook
            [COLISTER] => 
            [COMPPAIDON] => Net Sale Price
            [CONTRACTDATE] => 
            [CONTTOSHOW] => 
            [CP] => 
            [CRP] => WHEELING
            [CTGF] => 
            [CURRENTLYLEASED] => No
            [DEED_GARAGE_COST] => 
            [DEED_PARKING_COST] => 
            [DIM] => 91X110X145X37
            [DIN] => Separate
            [DIR] => DUNDEE RD W OF 83(ELMHURST) TO ST. ARMAND NORTH TO HOME AT CORNER
            [DISABILITY_ACCESS] => No
            [DISABILITY_ACCESS_DETAILS] => 
            [DPR] => Yes
            [DRF] => Carpet
            [DRL] => Main Level
            [DRS] => 12X10
            [DRV] => 
            [DRW] => 
            [E] => 0
            [ELC] => 
            [EQP] => 
            [EXISTING_BF] => 
            [EXP] => 
            [EXT] => Vinyl Siding,Brick
            [EXT_PROP_FEATS] => Deck,Porch,Storms/Screens
            [FEE_LEASE_AMOUNT] => 
            [FIN] => 
            [FIREPLACE_LOCATION] => Family Room
            [FND] => Concrete
            [FP] => 1
            [FPL] => Attached Fireplace Doors/Screen,Gas Logs,Includes Accessories
            [FRANCHISOR_FEED] => No
            [FREQ] => Not Applicable
            [FRF] => Carpet
            [FRL] => Lower
            [FRS] => 24X16
            [FRW] => 
            [FULL_BATHS] => 3
            [GAR] => Garage Door Opener(s),Transmitter(s)
            [GARAGE_ONSITE] => Yes
            [GARAGE_OWNERSHIP] => Owned
            [GARAGE_TYPE] => Attached
            [GREENDISCL] => 
            [GREENFEAT] => 
            [GREENSRC] => 
            [GRID] => 
            [GS] => 
            [GSD] => 21
            [HALF_BATHS] => 0
            [HEA] => Gas,Forced Air
            [HEM] => Yes
            [HERS] => 
            [HS] => BUFFALO GROVE HIGH SCHOOL
            [HSD] => 214
            [HSN] => 14
            [IDX] => Y
            [IMPS] => Sewer-Public
            [IMPW] => Lake Michigan
            [INF] => None
            [INT_PROP_FEATS] => Hardwood Floors
            [INTERNET_AVM] => Y
            [INTERNETLISTING] => All
            [JH] => 
            [JHD] => 21
            [KIT] => Eating Area-Table Space
            [KTF] => Hardwood
            [KTL] => Main Level
            [KTS] => 15X13
            [KTW] => 
            [LAADDRESS2] => 
            [LACELLPHONE] => (847) 732-3776
            [LACITY] => WHEELING
            [LAEMAIL] => conniehoos@yahoo.com
            [LAFAXNUMBER] => (781) 609-9016
            [LAFIRSTNAME] => Connie
            [LAID] => 88483
            [LALASTNAME] => Hoos
            [LAOFFICEPHONE] => (847) 732-3776
            [LAPAGERNUMBER] => 
            [LASTATE] => IL
            [LASTREETNAME] => 
            [LASTREETNUMBER] => 
            [LAT] => 42.139629
            [LAUNDRYF] => 
            [LAUNDRYL] => Lower
            [LAUNDRYS] => 12X10
            [LAUNDRYW] => 
            [LAZIP] => 60090
            [LD] => 2014-08-28T05:00:00
            [LDR] => 2014-08-28T19:36:36
            [LDS] => Corner
            [LEASEXD] => 
            [LIST] => Exclusive Right To Sell
            [LLP] => 
            [LMT] => 36
            [LN] => 08713688
            [LNG] => -87.946587
            [LO_LOCATION] => 8055
            [LOCITY] => Arlington Heights
            [LOCKBOX] => 
            [LOEMAIL] => 
            [LOFAXNUMBER] => (847) 394-5890
            [LOID] => 8055
            [LONAME] => Coldwell Banker Residential Brokerage
            [LOPHONE] => (847) 222-5000
            [LOSTATE] => IL
            [LOSTREETNAME] => E. Rand Rd.
            [LOSTREETNUMBER] => 792
            [LOWWW] => 
            [LOZIP] => 60004
            [LP] => 319500
            [LRF] => Carpet
            [LRL] => Main Level
            [LRS] => 19X12
            [LRW] => 
            [LSZ] => Less Than .25 Acre
            [MAI] => None
            [MANAGECOMPANY] => 
            [MANAGECONTACT] => 
            [MANAGEPHONE] => 
            [MANAGINGBROKER] => No
            [MASTER_ASSOC_FEE] => 
            [MBB] => Full
            [MBF] => Hardwood
            [MBL] => 2nd Level
            [MBS] => 15X11
            [MBW] => 
            [MC] => Active
            [MODx] => 
            [MPN] => No
            [MT] => 36
            [N] => 18
            [NC] => N
            [NCO] => 
            [NO_GARAGE_SPACES] => 2
            [NO_PARKING_SPACES] => 
            [OD] => 
            [OLP] => 319500
            [OMD] => 
            [OPH] => 847-222-5000
            [OSD] => 
            [OT] => 
            [OWN] => Fee Simple
            [OWR] => OOR
            [PAR] => 
            [PAR_FEE_LEASE_AMOUNT] => 
            [PARKING_ONSITE] => 
            [PARKING_OWNERSHIP] => 
            [PHO] => 
            [PHOTOCOUNT] => 15
            [PHOTODATE] => 2014-08-28T19:36:36
            [PIN] => 03044060010000
            [PKN] => Garage
            [PNT] => 
            [POS] => Negotiable
            [POST_DIRECTION] => 
            [RECORDMODDATE] => 2014-09-03T05:07:24
            [REMARKS] => SIMPLY WONDERFUL INSIDE AND OUT** GRACIOUS FRONT PORCH ENTRY **BRIGHT SUN-FILLED ROOMS** 4 BEDROOMS *3 FULL BATHS* LARGE UPDATED KITCHEN OVERLOOKS LOVELY FAMILY ROOM W/BRICK FIREPLACE*  BRAND NEW CARPET IN LIV RM/DIN RM AND FAMILY RM* HARDWOOD FLOORS IN ALL BEDROOMS** AMAZING YARD IS A PRIVATE & PEACEFUL PLACE. LOTS OF STORAGE *ATTACHED GARAGE*LARGE BRIGHT LAUNDRY RM*  COME FALL IN LOVE WITH THIS HOME SWEET HOME
            [REMARKSINTERNET] => Yes
            [TYP] => Detached Single
            [RMS] => 8
            [ROF] => Asphalt/Glass (Shingles)
            [RR] => No
            [S] => 0
            [SAADDRESS2] => 
            [SACELLPHONE] => 
            [SACITY] => 
            [SAEMAIL] => 
            [SAFAXNUMBER] => 
            [SAFIRSTNAME] => 
            [SAID] => 
            [SALASTNAME] => 
            [SALE_OR_RENT] => No
            [SALE_OR_RENT_LN] => 
            [SAOFFICEPHONE] => 
            [SAPAGERNUMBER] => 
            [SAS] => N
            [SASTATE] => 
            [SASTREETNAME] => 
            [SASTREETNUMBER] => 
            [SAZIP] => 
            [SCI] => None
            [SECURESHOWASSIST] => 
            [SELLERSCONC] => 
            [SHO] => CALL 855-746-9200  APPT
            [SHORT_SALE] => 
            [SO_LOCATION] => 
            [SOCITY] => 
            [SOEMAIL] => 
            [SOFAXNUMBER] => 
            [SOID] => 
            [SONAME] => 
            [SOPHONE] => 
            [SOSTATE] => 
            [SOSTREETNAME] => 
            [SOSTREETNUMBER] => 
            [SOWWW] => 
            [SOZIP] => 
            [SP] => 
            [SP_INCL_PARKING] => Yes
            [SPEC_SVC_AREA] => N
            [SPEC_SVC_FEE] => 
            [ST] => Active
            [STATE] => Illinois
            [STD] => 2014-09-03T05:07:24
            [STR] => St Armand
            [STREETSUFFIX] => Lane
            [STY] => Tri-Level
            [SUB] => HIGHLAND GLEN
            [TAX] => 6312.84
            [TOURURL] => 
            [TPE] => 2 Stories
            [TRM] => 
            [TWN] => WHEELING
            [TXC] => Homeowner
            [TXY] => 2013
            [UD] => 2014-09-03T05:07:24
            [VIRTUALPHOTOS] => No
            [VT] => None
            [VTDATE] => 
            [W] => 15
            [WF] => No
            [XD] => 
            [ZP] => 60090
            [ZP4] => "
        )

    [2] => Array
        (
            [A10F] => "
            [A10L] => Not Applicable
            [A10N] => 
            [A10S] => 
            [A10W] => 
            [A1F] => 
            [A1L] => Not Applicable
            [A1N] => 
            [A1S] => 
            [A1W] => 
            [A2F] => 
            [A2L] => Not Applicable
            [A2N] => 
            [A2S] => 
            [A2W] => 
            [A3F] => 
            [A3L] => Not Applicable
            [A3N] => 
            [A3S] => 
            [A3W] => 
            [A4F] => 
            [A4L] => Not Applicable
            [A4N] => 
            [A4S] => 
            [A4W] => 
            [A5F] => 
            [A5L] => Not Applicable
            [A5N] => 
            [A5S] => 
            [A5W] => 
            [A6F] => 
            [A6L] => Not Applicable
            [A6N] => 
            [A6S] => 
            [A6W] => 
            [A7F] => 
            [A7L] => Not Applicable
            [A7N] => 
            [A7S] => 
            [A7W] => 
            [A8F] => 
            [A8L] => Not Applicable
            [A8N] => 
            [A8S] => 
            [A8W] => 
            [A9F] => 
            [A9L] => Not Applicable
            [A9N] => 
            [A9S] => 
            [A9W] => 
            [AAN] => 
            [ACR] => .2
            [ADDITIONAL_ROOMS] => No additional rooms
            [ADDITIONAL_SALES_INFO] => None
            [ADI] => Yes
            [AGE] => 61-70 Years
            [AGENTREMARKS] => 
            [AIR] => Central Air
            [ALLOW_COMMENTS_REVIEWS] => Y
            [ALT_ELEMENTARY_NAME] => 
            [ALT_HIGH_NAME] => 
            [ALT_JUNIORHIGH_NAME] => 
            [AMN] => 
            [AON] => No
            [APP] => Oven/Range,Dishwasher,Refrigerator,Washer,Dryer
            [AR] => Oswego
            [ASF] => 860
            [ASFSOURCE] => Assessor
            [ASM] => 0
            [ATC] => 
            [ATTDISCL] => 
            [AUCT_PRICE_TYPE] => 
            [AUCTION_DATE] => 
            [AZN] => 
            [B2F] => Hardwood
            [B2L] => Main Level
            [B2S] => 13X13
            [B2W] => 
            [B3F] => 
            [B3L] => Not Applicable
            [B3S] => 
            [B3W] => 
            [B4F] => 
            [B4L] => Not Applicable
            [B4S] => 
            [B4W] => 
            [B78] => Yes
            [BAS] => Unfinished
            [BAT] => 
            [BB] => No
            [BID_RESERVE_PRICE] => 
            [BLT] => 1949
            [BMD] => 
            [BMT] => Full
            [BOARDNUM] => 10
            [BR] => 2
            [BRALL] => 2
            [BRBELOW] => 0
            [BTH] => 1
            [C35] => 
            [C57] => 
            [C810] => 
            [CARS] => 1
            [CC] => 2.5% -$200
            [CIT] => OSWEGO
            [CLOSEDDATE] => 
            [CNY] => Kendall
            [COLISTER] => 
            [COMPPAIDON] => Gross Sale Price
            [CONTRACTDATE] => 
            [CONTTOSHOW] => 
            [CP] => 
            [CRP] => OSWEGO
            [CTGF] => 
            [CURRENTLYLEASED] => 
            [DEED_GARAGE_COST] => 
            [DEED_PARKING_COST] => 
            [DIM] => 66 X 132
            [DIN] => 
            [DIR] => Route 71 to Franklin to Garfield to Home.
            [DISABILITY_ACCESS] => No
            [DISABILITY_ACCESS_DETAILS] => 
            [DPR] => Yes
            [DRF] => 
            [DRL] => Not Applicable
            [DRS] => 
            [DRV] => 
            [DRW] => 
            [E] => 
            [ELC] => 
            [EQP] => 
            [EXISTING_BF] => 
            [EXP] => 
            [EXT] => Aluminum Siding,Vinyl Siding
            [EXT_PROP_FEATS] => 
            [FEE_LEASE_AMOUNT] => 
            [FIN] => 
            [FIREPLACE_LOCATION] => 
            [FND] => 
            [FP] => 
            [FPL] => 
            [FRANCHISOR_FEED] => No
            [FREQ] => Not Applicable
            [FRF] => 
            [FRL] => Not Applicable
            [FRS] => 
            [FRW] => 
            [FULL_BATHS] => 1
            [GAR] => 
            [GARAGE_ONSITE] => Yes
            [GARAGE_OWNERSHIP] => Owned
            [GARAGE_TYPE] => Detached
            [GREENDISCL] => 
            [GREENFEAT] => 
            [GREENSRC] => 
            [GRID] => 
            [GS] => EAST VIEW ELEMENTARY SCHOOL
            [GSD] => 308
            [HALF_BATHS] => 0
            [HEA] => Gas
            [HEM] => Yes
            [HERS] => 
            [HS] => 
            [HSD] => 308
            [HSN] => 67
            [IDX] => Y
            [IMPS] => Sewer-Public
            [IMPW] => Public
            [INF] => None
            [INT_PROP_FEATS] => 
            [INTERNET_AVM] => Y
            [INTERNETLISTING] => All
            [JH] => TRAUGHBER JUNIOR HIGH SCHOOL
            [JHD] => 308
            [KIT] => 
            [KTF] => Ceramic Tile
            [KTL] => Main Level
            [KTS] => 17X8
            [KTW] => 
            [LAADDRESS2] => 
            [LACELLPHONE] => 
            [LACITY] => Lombard
            [LAEMAIL] => cc@jwreedymail.com
            [LAFAXNUMBER] => 
            [LAFIRSTNAME] => Cecilia
            [LAID] => 235516
            [LALASTNAME] => Whitt
            [LAOFFICEPHONE] => (630) 915-0486
            [LAPAGERNUMBER] => 
            [LASTATE] => IL
            [LASTREETNAME] => S Main St
            [LASTREETNUMBER] => 1136
            [LAT] => 41.683304
            [LAUNDRYF] => 
            [LAUNDRYL] => Not Applicable
            [LAUNDRYS] => 
            [LAUNDRYW] => 
            [LAZIP] => 60148
            [LD] => 2014-08-07T05:00:00
            [LDR] => 2014-08-07T15:54:38
            [LDS] => 
            [LEASEXD] => 
            [LIST] => Exclusive Right To Sell
            [LLP] => 139900
            [LMT] => 27
            [LN] => 08695075
            [LNG] => -88.346153
            [LO_LOCATION] => 24213
            [LOCITY] => Lombard
            [LOCKBOX] => 
            [LOEMAIL] => reedy@cin.net
            [LOFAXNUMBER] => (630) 629-0024
            [LOID] => 24213
            [LONAME] => J.W. Reedy Realty
            [LOPHONE] => (630) 629-0016
            [LOSTATE] => IL
            [LOSTREETNAME] => S Main St
            [LOSTREETNUMBER] => 1136
            [LOWWW] => 
            [LOZIP] => 60148
            [LP] => 136600
            [LRF] => Hardwood
            [LRL] => Main Level
            [LRS] => 19X15
            [LRW] => 
            [LSZ] => Less Than .25 Acre
            [MAI] => None
            [MANAGECOMPANY] => 
            [MANAGECONTACT] => 
            [MANAGEPHONE] => 
            [MANAGINGBROKER] => No
            [MASTER_ASSOC_FEE] => 
            [MBB] => None
            [MBF] => Hardwood
            [MBL] => Main Level
            [MBS] => 13X13
            [MBW] => 
            [MC] => Off-Market
            [MODx] => 
            [MPN] => 
            [MT] => 86
            [N] => 
            [NC] => N
            [NCO] => 
            [NO_GARAGE_SPACES] => 1
            [NO_PARKING_SPACES] => 
            [OD] => 
            [OLP] => 139900
            [OMD] => 2014-09-02T05:00:00
            [OPH] => 
            [OSD] => 
            [OT] => 
            [OWN] => Fee Simple
            [OWR] => Lou Gramm
            [PAR] => 
            [PAR_FEE_LEASE_AMOUNT] => 
            [PARKING_ONSITE] => 
            [PARKING_OWNERSHIP] => 
            [PHO] => 
            [PHOTOCOUNT] => 20
            [PHOTODATE] => 2014-08-07T15:54:38
            [PIN] => 0317407009
            [PKN] => Garage
            [PNT] => 
            [POS] => Closing
            [POST_DIRECTION] => 
            [RECORDMODDATE] => 2014-09-02T16:27:55
            [REMARKS] => A real charmer! This home is absolutely adorable. The updated kitchen features ceramic floors, SS apppliances and oak cabinets. Living room and bedrooms feature hardwood flooring. Updates include the bathroom as well. Full basement ready for your finishing ideas. Oversized yard with deck is a great place to spend your summers. Great location. Close to schools, shopping and entertainment. You will not be disappointed!
            [REMARKSINTERNET] => Yes
            [TYP] => Detached Single
            [RMS] => 4
            [ROF] => 
            [RR] => No
            [S] => 
            [SAADDRESS2] => 
            [SACELLPHONE] => 
            [SACITY] => 
            [SAEMAIL] => 
            [SAFAXNUMBER] => 
            [SAFIRSTNAME] => 
            [SAID] => 
            [SALASTNAME] => 
            [SALE_OR_RENT] => No
            [SALE_OR_RENT_LN] => 
            [SAOFFICEPHONE] => 
            [SAPAGERNUMBER] => 
            [SAS] => N
            [SASTATE] => 
            [SASTREETNAME] => 
            [SASTREETNUMBER] => 
            [SAZIP] => 
            [SCI] => None
            [SECURESHOWASSIST] => 
            [SELLERSCONC] => 
            [SHO] => CALL LISTING OFFICE OR USE SHOWINGASSIST
            [SHORT_SALE] => 
            [SO_LOCATION] => 
            [SOCITY] => 
            [SOEMAIL] => 
            [SOFAXNUMBER] => 
            [SOID] => 
            [SONAME] => 
            [SOPHONE] => 
            [SOSTATE] => 
            [SOSTREETNAME] => 
            [SOSTREETNUMBER] => 
            [SOWWW] => 
            [SOZIP] => 
            [SP] => 
            [SP_INCL_PARKING] => Yes
            [SPEC_SVC_AREA] => N
            [SPEC_SVC_FEE] => 
            [ST] => Cancelled
            [STATE] => Illinois
            [STD] => 2014-09-02T16:27:55
            [STR] => Garfield
            [STREETSUFFIX] => Street
            [STY] => Ranch
            [SUB] => 
            [TAX] => 5044.22
            [TOURURL] => 
            [TPE] => 1 Story
            [TRM] => 
            [TWN] => OSWEGO
            [TXC] => 
            [TXY] => 2013
            [UD] => 2014-09-02T16:27:55
            [VIRTUALPHOTOS] => No
            [VT] => None
            [VTDATE] => 2014-08-09T07:50:12
            [W] => 
            [WF] => No
            [XD] => 
            [ZP] => 60543
            [ZP4] => "
        )

Open in new window


in my code i have an array with the field names by itself and the vlaues by them selves - and ihave constructed a set of fieldname=value for the on update string set.

$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES (" . implode(",",$queue) . ") ON DUPLICATE KEY UPDATE " . $set . "";

Open in new window


im now thinking this is the wrong way to do this as i ran into a problem doing it this way although it builds my query. it builds ALL the set in one massive string as ON DUPLICATE KEY UPDATE needs to check a unique value, and also need to know the insert query a full block of insert(all the rows one after the other and the sets all globed together does not work. i need each to be like a sql file one row at a time with insert and update corresponding much like a sql file makes from myphpadmin.

i just plain dont know enouph of mysql or php to make this loop work right, or even how to do it at all.

so im unsure how to get my array to work as a query for all this. i do not know how to code this i did find.
https://ellislab.com/forums/viewthread/203637/#1042042
it seams to do what i want but i do not understand how to make it work with my array.


my current array is built and saved to array $out

Thank you in advance for any code or help you may provide.
0
Comment
Question by:Johnny
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
Murfur earned 500 total points
ID: 40359491
Well this has been a fun lunch time exercise!

So what did I do?

There is so much data in each array that batching loads at the same time could possibly push the performance of the server or simply run slowly given that so many keys in the arrays have empty values.

After the initial array creation (for this test) I only copied key/value pairs that had a value into a new array to be passed to the SQL query (181 out of 335)

Then looping through the new array, I build the SQL query and at the end output the result to the screen before moving onto the next array (I only have 2 in this test).

In the real world you could do one of two things in place of the output echo:
-- either run the insert/update there or
-- add the query string to a new array and once all the inbound data processing had completed loop though the final array of INSERT commands to perform all the inserts/updates.

I've commented the code throughout to make it clear (I hope) and indented and marked rows with ##### to indicate the non-essential code for beautifying the display of this test.

Working demo (with syntax highlighting) here: http://www.murfur.com/experts-exchange/Q_28529942_associated_arrays.php

Does that help? Am I barking up the right tree?

Q-28529942-associated-arrays.php
0
 

Author Comment

by:Johnny
ID: 40359565
@Murfur
thank you for reply, so far this is exactly what i need.
i assume all the ################### blocks will need to be commented out in mysql query send.

my code has it looping every 1k records so there would be no more then 1k at a time. on avg there will be about 200 every hour.
you had mentioned that in "REAL WORLD" is there anything you suggest that i can do to make this work or need to change for real world?

would i just fire off this built query in my mysql block i have?

below is my code with your additions, and how i have it so far.

PS: LOVE!!! your comments best i have seen on EE thank you

<?######################?>
<?#	to make the display pretty!?>
<?######################?>

<style>
html, body, div, pre, code { font: 15px/20px monaco, courier, serif; color: #000;}
pre, code {
	white-space: normal;
	max-width: 100%;
}
</style>
<html>
<body>
<?######################?>

<?php 
ini_set("memory_limit","375M");
ini_set('max_execution_time', 12500); //7 mins is 420 seconds - 300 seconds = 5 minutes
require_once('functions.php'); 
include ("../../mysqli_ctx.php");

$rets_login_url = "taken_out_for_ee_post"; 
$rets_username = "taken_out_for_ee_post"; 
$rets_password = "taken_out_for_ee_post"; 

// use http://retsmd.com to help determine the SystemName of the DateTime field which 
// designates when a record was last modified 
//$rets_modtimestamp_field = "LastModifiedDateTime"; RECORDMODDATE
$rets_modtimestamp_field = "RECORDMODDATE";
// use http://retsmd.com to help determine the names of the classes you want to pull. 
// these might be something like RE_1, RES, RESI, 1, etc. 
$property_classes = array("DE"); 

// DateTime which is used to determine how far back to retrieve records. 
// using a really old date so we can get everything 
$previous_start_time = "1980-01-01T00:00:00"; 


$sixmonths = date('Y-m-d\TH:i:s', time()-15778800); // get listings updated within last 6 months
	$sixmonths = date('Y-m-d\TH:i:s', time()-15778800); // get listings updated within last 6 months
	//$hour_ago = date("Y-m-d\TH:i:s", time()-3600);//Time minus 3600 seconds, is the time 1 hour ago
	$hour_ago = gmdate("Y-m-d\TH:i:s", time()-3600);//Time minus 3600 seconds, is the time 1 hour ago GMT
	//86,400 seconds in a day
	$one_month_ago = gmdate("Y-m-d\TH:i:s", time()-(86400*30));//Time minus one day * 30 days = seconds, is the time 30days ago GMT

////////////////////////////// 

require_once("../lib/phrets.php"); 

// start rets connection 
$rets = new phRETS; 

//$rets->AddHeader("RETS-Version", "RETS/1.7.2"); 
//$rets->AddHeader('User-Agent', "YOUR_USER_AGENT_HEADER_IF_NECESSARY"); 

$rets->SetParam("compression_enabled", true); 

echo " * Connecting to {$rets_login_url} as {$rets_username}<br>\n"; 
$connect = $rets->Connect($rets_login_url, $rets_username, $rets_password); 

if ($connect) { 
    echo "  * Connected<br>\n"; 
} 
else { 
        echo "  * Not connected:<br>\n"; 
        print_r($rets->Error()); 
        exit; 
} 

foreach ($property_classes as $class) { 

        echo " * Property class: {$class}<br>\n"; 

        $maxrows = true; 
        $offset = 1; 
        $limit = 1000; 
        $fields_order = array(); 
        while ($maxrows) { 
                //$query = "(LN=0+),(Status=|A,C),(County=|Barnstable,Dukes,Nantucket,Plymouth)";
                //$rets_query = '(RECORDMODDATE='.$one_month_ago.'+)'; 
                $rets_query = '(RECORDMODDATE='.$hour_ago.'+)'; 
                // run RETS search 
                echo "  * Query: {$query}  Limit: {$limit}  Offset: {$offset}<br>\n"; 
                $search = $rets->SearchQuery(
                "Property",
                $class,
                $rets_query,
                array('Limit' => $limit, 'Offset' => $offset, 'Format' => 'COMPACT-DECODED', 'Count' => 1)); 
                echo "  * Total found: {$rets->TotalRecordsFound()}<br>\n";
                echo "Proccessing...<br>";
                if ($rets->NumRows() > 0) { 
                        if ($offset == 1) { 
                                // print filename headers as first line 
                                $fields_order = $rets->SearchGetFields($search); 
                                //fputcsv($fh, $fields_order); 
                        } 
                        // Define a new, empty queue
                        $rets_queue = array();

                        // process results 
                        while ($record = $rets->FetchRow($search)) { 
                                $this_record = array(); 
                                foreach ($fields_order as $fo) { 
                                        $this_record[] = '"'.mysqli_real_escape_string($mysqli,$record[$fo]).'"';
                                }//for each
                                //mysql inject
                                 $fields_order_l = implode(',', $fields_order);
                                 $fields_order_l = str_replace(",MOD,",",MODx,",$fields_order_l);
                                 $this_record = implode(',', $this_record);
				    $table="rets_property_de";

                                    // Add to queue instead of querying
                                    $rets_queue[] .= $this_record;

                                     // REMOVED THE QUERY CODE FROM THE WHILE LOOP
			} // WHILE

                        // Now that the loop is finished...

//$fields_order_l
//$queue
$fields_order_l2 = explode(',', $fields_order_l);
/*
echo "<pre>";
var_dump($fields_order_l2);
echo "</pre><br>";
echo "<pre>";
var_dump($queue);
echo "</pre><br>";
*/


// ITERATE OVER THE DATA
foreach ($rets_queue as $data_row)
{
    // TURN EACH ROW INTO AN ARRAY
    $data_arr = explode('","', $data_row);
/*
echo "<pre>";
var_dump($data_arr);
echo "</pre><br>";
die('stop');
*/

    // COMBINE TO SET KEYS
    $new = array_combine($fields_order_l2, $data_arr);

    // SAVE THIS AS A SUB-ARRAY
    $out[] = $new;
}
//echo '<pre>';
//print_r($out);


#	create associative array
$source_array = array( $original_array0, $original_array1); 
// add array to new example
$source_array = $out;


#	show array contents to check
#print_r($source_array, $return=FALSE);


#	loop through the batch
foreach ($source_array as $child_array) :

	#	create new array to contain elements that are not empty
	$query_columns = array();

	foreach ($child_array as $key=>$value) :
		# copy keys and values if values are not empty
		if($value != "") {
			$query_columns[$key] = $value;
		}
	endforeach;	
	
	#	total elements with real values
#	echo("Count this array: " . count($query_columns) . "<br />");
	
	
	#	set loop counters for commas in SQL syntax
	$colCount	= count($query_columns);
	$loopCount	= 0;
	
	#	build SQL query
	$query = "INSERT INTO `".$table."` (";

			###################
			$query .= "<div style='margin-left: 25px;'>";
			###################

	#	get column names for INSERT syntax
	foreach ($query_columns as $key=>$value) :
		$loopCount++;
		$query	.= "`" . $key . "`";
		$query	.= ($loopCount < $colCount?", ":"" );
	endforeach;
	
			###################
			$query .= "</div>";
			###################

	$query .= ") VALUES (";
	
			###################
			$query .= "<div style='margin-left: 25px;'>";
			###################

	#	reset loop counter
	$loopCount	= 0;
	#	get corresponding values for INSERT syntax
	foreach ($query_columns as $key => $value) :
		$loopCount++;
		$query	.= "'" . $value . "'";
		$query	.= ($loopCount < $colCount?", ":"" );
	endforeach;

			###################
			$query .= "</div>";
			###################
		
	$query .= ") ON DUPLICATE KEY UPDATE ";
	
			###################
			$query .= "<div style='margin-left: 25px;'>";
			###################

	#	reset loop counter
	$loopCount	= 0;
	#	get column names & values for update syntax
	foreach ($query_columns as $key => $value) :
		$loopCount++;
		$query	.= "`" . $key . "`='" . $value . "'";
		$query	.= ($loopCount < $colCount?", ":"" );
	endforeach;

	#	terminate SQL syntax
	$query .= ";";

			###################
			$query .= "</div>";
			###################
	
	#	display the finished SQL query syntax
?>
<style>
pre, code { display: inline-block; max-width: 100%; white-space: normal;}
</style>
<?
			###################
			echo('<div style="width: 100%">');
			echo("<pre><code class=\"sql\">");
			###################

	echo($query);

			###################
			echo("</code></pre>");
			echo("</div>");
			###################
	
	#	clear array contents ready for next iteration in the loop
	unset($query_columns);

#	end outer array loop
endforeach;





die('stop');
// SHOW HOW TO PROCESS EACH COMBINED ARRAY
/*
foreach ($out as $new)
{
    // WITH EACH SUB-ARRAY
    foreach ($new as $key => $val)
    {
        //echo PHP_EOL . "$key POINTS TO $val";
         $set .= "'" . $key . "' = '" . $val . "',";
    }
    echo PHP_EOL;
}
die('stop');
*/




/// SETTING UP UPDATE QUERY
$set = NULL;
foreach ($out as $new)
{
    // WITH EACH SUB-ARRAY
    foreach ($new as $key => $val)
    {
        //echo PHP_EOL . "$key POINTS TO $val";
         $set .= "`" . $key . "` = '" . $val . "',";
    }
    echo PHP_EOL;
}

$set = rtrim($set, ',');
//$query = "UPDATE $table SET ($set) WHERE...";
//echo PHP_EOL . $query;
//die('stop');

//have to use out as new loop to make insert into querys as a sql file type output for multible rows



$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES (" . implode(",",$queue) . ") ON DUPLICATE KEY UPDATE " . $set . "";

                                              
				    
				    //$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES " . implode(",",$queue);
				        echo $query;
				        die('stop');
				        $r = mysqli_query($mysqli,$query);
				        if(!$r )
					{
					  die('<br>Could not enter data: ' . mysqli_error($mysqli));
					}
					else 
					{
					//echo "<br>Entered data successfully<br>\n";
					// printf("%d Row inserted.<br>\n", $mysqli->affected_rows);
					//echo ".";
					}//if r
					//echo "<br>"; 
                        $offset = ($offset + $rets->NumRows()); 
                } // if num
                $maxrows = $rets->IsMaxrowsReached(); 
                $rets->FreeResult($search); 
        } // while max
 }// for each
mysqli_close($mysqli);
echo "- done<br>\n"; 
echo " * Disconnecting<br>\n"; 
$rets->Disconnect(); 

?> 

Open in new window

0
 
LVL 11

Expert Comment

by:Murfur
ID: 40359687
Yes, all the code between the #### lines are there purely for the display of the test and can be removed.

You've added my code but then as is, this duplicates the operation.

So mine properly starts at line 165 foreach ($source_array as $child_array) : , the 10 lines before that is where I created the source_array with the sample data for something to work on. My $source_array is the equivalent of your $out.

This is effectively the same point as line 276 (your foreach ($out as $new)) so you could  replace from there down to the $r = mysqli_query($mysqli,$query); and associated code (where I have my echo($query)) and then close off the maxrows test.
0
 

Author Closing Comment

by:Johnny
ID: 40359703
Thank you so much
0
 
LVL 11

Expert Comment

by:Murfur
ID: 40360940
Always happy to help! Exercises like this are how I learn! 😄
0

Featured Post

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!

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

705 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

22 Experts available now in Live!

Get 1:1 Help Now