Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to join two lists to make an update statement for mysqli

Posted on 2014-09-23
7
Medium Priority
?
575 Views
Last Modified: 2014-09-23
i have two lists that i have built to use a insert mysql command i now need to make it a update mysqli command

my values for the field names are in $fields_order_l and my data values are in $queue
this statement works wonderfully for insert into
$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES " . implode(",",$queue);

Open in new window

so my field names are in comma lists as well as my data as the insert mysql command works correctly

how do it get it to format for a update query?
i cant figure out how to use two lists to loop through them, or how i would combine them for a field name->data array

if you need anything else please ask
thank you in advance for any code or help you may provide
Johnny
0
Comment
Question by:Johnny
  • 4
  • 3
7 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40339461
INSERT queries typically list the fields and VALUES in separate strings.  UPDATE queries list them together, interspersed like this.  Note that I did not escape the data; in a real-world script you would want to use the correct escape function.
http://iconoun.com/demo/temp_pern.php

<?php // demo/temp_pern.php
error_reporting(E_ALL);
echo '<pre>';

// TEST DATA
$table  = 'my_table';
$fields = array('pet', 'food', 'car');
$values = array('Lucky', 'pie', 'BMW');


// SETTING UP INSERT QUERY
$fields_list = "'" . implode("','", $fields) . "'";
$values_list = "'" . implode("','", $values) . "'";

$query = "INSERT INTO $table ($fields_list) VALUES ($values_list)";
echo PHP_EOL . $query;


// SETTING UP UPDATE QUERY
$set = NULL;
$dat = array_combine($fields, $values);
foreach ($dat as $key => $val)
{
    $set .= "'" . $key . "' = '" . $val . "',";
}
$set = rtrim($set, ',');
$query = "UPDATE $table SET ($set) WHERE...";
echo PHP_EOL . $query;

Open in new window

If you want to show us your data, I'll be glad to show you an example.
0
 

Author Closing Comment

by:Johnny
ID: 40339899
exactly what i needed, fantastic as always thank you
0
 

Author Comment

by:Johnny
ID: 40339980
@Ray
apparently i jumped the gun and i screwed up a bit...
i have the data as multiple entries and did not realize it how do i loop them to form my query
here is what i have so far below is sample data
//$fields_order_l
//$queue
$fields_order_l2 = explode(',', $fields_order_l);
echo "<pre>";
var_dump($fields_order_l);
echo "</pre><br>";
echo "<pre>";
var_dump($queue);
echo "</pre><br>";
// SETTING UP UPDATE QUERY
$set = NULL;
$dat = array_combine($fields_order_l2, $queue);
foreach ($dat as $key => $val)
{
    $set .= "'" . $key . "' = '" . $val . "',";
}
$set = rtrim($set, ',');
$query = "UPDATE $table SET ($set) WHERE...";
echo PHP_EOL . $query;
die('stop');
$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES (" . implode(",",$queue) . ") ON DUPLICATE KEY UPDATE " . $set . "id=id";

Open in new window


output fields ($fields_order_l from before build in my code i have)
string(2352) "A10F,A10L,A10N,A10S,A10W,A1F,A1L,A1N,A1S,A1W,A2F,A2L,A2N,A2S,A2W,A3F,A3L,A3N,A3S,A3W,A4F,A4L,A4N,A4S,A4W,A5F,A5L,A5N,A5S,A5W,A6F,A6L,A6N,A6S,A6W,A7F,A7L,A7N,A7S,A7W,A8F,A8L,A8N,A8S,A8W,A9F,A9L,A9N,A9S,A9W,AAN,ACR,ADDITIONAL_ROOMS,ADDITIONAL_SALES_INFO,ADI,AGE,AGENTREMARKS,AIR,ALLOW_COMMENTS_REVIEWS,ALT_ELEMENTARY_NAME,ALT_HIGH_NAME,ALT_JUNIORHIGH_NAME,AMN,AON,APP,AR,ASF,ASFSOURCE,ASM,ATC,ATTDISCL,AUCT_PRICE_TYPE,AUCTION_DATE,AZN,B2F,B2L,B2S,B2W,B3F,B3L,B3S,B3W,B4F,B4L,B4S,B4W,B78,BAS,BAT,BB,BID_RESERVE_PRICE,BLT,BMD,BMT,BOARDNUM,BR,BRALL,BRBELOW,BTH,C35,C57,C810,CARS,CC,CIT,CLOSEDDATE,CNY,COLISTER,COMPPAIDON,CONTRACTDATE,CONTTOSHOW,CP,CRP,CTGF,CURRENTLYLEASED,DEED_GARAGE_COST,DEED_PARKING_COST,DIM,DIN,DIR,DISABILITY_ACCESS,DISABILITY_ACCESS_DETAILS,DPR,DRF,DRL,DRS,DRV,DRW,E,ELC,EQP,EXISTING_BF,EXP,EXT,EXT_PROP_FEATS,FEE_LEASE_AMOUNT,FIN,FIREPLACE_LOCATION,FND,FP,FPL,FRANCHISOR_FEED,FREQ,FRF,FRL,FRS,FRW,FULL_BATHS,GAR,GARAGE_ONSITE,GARAGE_OWNERSHIP,GARAGE_TYPE,GREENDISCL,GREENFEAT,GREENSRC,GRID,GS,GSD,HALF_BATHS,HEA,HEM,HERS,HS,HSD,HSN,IDX,IMPS,IMPW,INF,INT_PROP_FEATS,INTERNET_AVM,INTERNETLISTING,JH,JHD,KIT,KTF,KTL,KTS,KTW,LAADDRESS2,LACELLPHONE,LACITY,LAEMAIL,LAFAXNUMBER,LAFIRSTNAME,LAID,LALASTNAME,LAOFFICEPHONE,LAPAGERNUMBER,LASTATE,LASTREETNAME,LASTREETNUMBER,LAT,LAUNDRYF,LAUNDRYL,LAUNDRYS,LAUNDRYW,LAZIP,LD,LDR,LDS,LEASEXD,LIST,LLP,LMT,LN,LNG,LO_LOCATION,LOCITY,LOCKBOX,LOEMAIL,LOFAXNUMBER,LOID,LONAME,LOPHONE,LOSTATE,LOSTREETNAME,LOSTREETNUMBER,LOWWW,LOZIP,LP,LRF,LRL,LRS,LRW,LSZ,MAI,MANAGECOMPANY,MANAGECONTACT,MANAGEPHONE,MANAGINGBROKER,MASTER_ASSOC_FEE,MBB,MBF,MBL,MBS,MBW,MC,MODx,MPN,MT,N,NC,NCO,NO_GARAGE_SPACES,NO_PARKING_SPACES,OD,OLP,OMD,OPH,OSD,OT,OWN,OWR,PAR,PAR_FEE_LEASE_AMOUNT,PARKING_ONSITE,PARKING_OWNERSHIP,PHO,PHOTOCOUNT,PHOTODATE,PIN,PKN,PNT,POS,POST_DIRECTION,RECORDMODDATE,REMARKS,REMARKSINTERNET,TYP,RMS,ROF,RR,S,SAADDRESS2,SACELLPHONE,SACITY,SAEMAIL,SAFAXNUMBER,SAFIRSTNAME,SAID,SALASTNAME,SALE_OR_RENT,SALE_OR_RENT_LN,SAOFFICEPHONE,SAPAGERNUMBER,SAS,SASTATE,SASTREETNAME,SASTREETNUMBER,SAZIP,SCI,SECURESHOWASSIST,SELLERSCONC,SHO,SHORT_SALE,SO_LOCATION,SOCITY,SOEMAIL,SOFAXNUMBER,SOID,SONAME,SOPHONE,SOSTATE,SOSTREETNAME,SOSTREETNUMBER,SOWWW,SOZIP,SP,SP_INCL_PARKING,SPEC_SVC_AREA,SPEC_SVC_FEE,ST,STATE,STD,STR,STREETSUFFIX,STY,SUB,TAX,TOURURL,TPE,TRM,TWN,TXC,TXY,UD,VIRTUALPHOTOS,VT,VTDATE,W,WF,XD,ZP,ZP4"

Open in new window


my output now with
echo "<pre>";
var_dump($fields_order_l2);
echo "</pre><br>";
echo "<pre>";
var_dump($queue);
echo "</pre><br>";

Open in new window


output

Proccessing...
array(335) {
  [0]=>
  string(4) "A10F"
  [1]=>
  string(4) "A10L"
  [2]=>
  string(4) "A10N"
  [3]=>
  string(4) "A10S"
  [4]=>
  string(4) "A10W"
  [5]=>
  string(3) "A1F"
  [6]=>
  string(3) "A1L"
  [7]=>
  string(3) "A1N"
  [8]=>
  string(3) "A1S"
  [9]=>
  string(3) "A1W"
  [10]=>
  string(3) "A2F"
  [11]=>
  string(3) "A2L"
  [12]=>
  string(3) "A2N"
  [13]=>
  string(3) "A2S"
  [14]=>
  string(3) "A2W"
  [15]=>
  string(3) "A3F"
  [16]=>
  string(3) "A3L"
  [17]=>
  string(3) "A3N"
  [18]=>
  string(3) "A3S"
  [19]=>
  string(3) "A3W"
  [20]=>
  string(3) "A4F"
  [21]=>
  string(3) "A4L"
  [22]=>
  string(3) "A4N"
  [23]=>
  string(3) "A4S"
  [24]=>
  string(3) "A4W"
  [25]=>
  string(3) "A5F"
  [26]=>
  string(3) "A5L"
  [27]=>
  string(3) "A5N"
  [28]=>
  string(3) "A5S"
  [29]=>
  string(3) "A5W"
  [30]=>
  string(3) "A6F"
  [31]=>
  string(3) "A6L"
  [32]=>
  string(3) "A6N"
  [33]=>
  string(3) "A6S"
  [34]=>
  string(3) "A6W"
  [35]=>
  string(3) "A7F"
  [36]=>
  string(3) "A7L"
  [37]=>
  string(3) "A7N"
  [38]=>
  string(3) "A7S"
  [39]=>
  string(3) "A7W"
  [40]=>
  string(3) "A8F"
  [41]=>
  string(3) "A8L"
  [42]=>
  string(3) "A8N"
  [43]=>
  string(3) "A8S"
  [44]=>
  string(3) "A8W"
  [45]=>
  string(3) "A9F"
  [46]=>
  string(3) "A9L"
  [47]=>
  string(3) "A9N"
  [48]=>
  string(3) "A9S"
  [49]=>
  string(3) "A9W"
  [50]=>
  string(3) "AAN"
  [51]=>
  string(3) "ACR"
  [52]=>
  string(16) "ADDITIONAL_ROOMS"
  [53]=>
  string(21) "ADDITIONAL_SALES_INFO"
  [54]=>
  string(3) "ADI"
  [55]=>
  string(3) "AGE"
  [56]=>
  string(12) "AGENTREMARKS"
  [57]=>
  string(3) "AIR"
  [58]=>
  string(22) "ALLOW_COMMENTS_REVIEWS"
  [59]=>
  string(19) "ALT_ELEMENTARY_NAME"
  [60]=>
  string(13) "ALT_HIGH_NAME"
  [61]=>
  string(19) "ALT_JUNIORHIGH_NAME"
  [62]=>
  string(3) "AMN"
  [63]=>
  string(3) "AON"
  [64]=>
  string(3) "APP"
  [65]=>
  string(2) "AR"
  [66]=>
  string(3) "ASF"
  [67]=>
  string(9) "ASFSOURCE"
  [68]=>
  string(3) "ASM"
  [69]=>
  string(3) "ATC"
  [70]=>
  string(8) "ATTDISCL"
  [71]=>
  string(15) "AUCT_PRICE_TYPE"
  [72]=>
  string(12) "AUCTION_DATE"
  [73]=>
  string(3) "AZN"
  [74]=>
  string(3) "B2F"
  [75]=>
  string(3) "B2L"
  [76]=>
  string(3) "B2S"
  [77]=>
  string(3) "B2W"
  [78]=>
  string(3) "B3F"
  [79]=>
  string(3) "B3L"
  [80]=>
  string(3) "B3S"
  [81]=>
  string(3) "B3W"
  [82]=>
  string(3) "B4F"
  [83]=>
  string(3) "B4L"
  [84]=>
  string(3) "B4S"
  [85]=>
  string(3) "B4W"
  [86]=>
  string(3) "B78"
  [87]=>
  string(3) "BAS"
  [88]=>
  string(3) "BAT"
  [89]=>
  string(2) "BB"
  [90]=>
  string(17) "BID_RESERVE_PRICE"
  [91]=>
  string(3) "BLT"
  [92]=>
  string(3) "BMD"
  [93]=>
  string(3) "BMT"
  [94]=>
  string(8) "BOARDNUM"
  [95]=>
  string(2) "BR"
  [96]=>
  string(5) "BRALL"
  [97]=>
  string(7) "BRBELOW"
  [98]=>
  string(3) "BTH"
  [99]=>
  string(3) "C35"
  [100]=>
  string(3) "C57"
  [101]=>
  string(4) "C810"
  [102]=>
  string(4) "CARS"
  [103]=>
  string(2) "CC"
  [104]=>
  string(3) "CIT"
  [105]=>
  string(10) "CLOSEDDATE"
  [106]=>
  string(3) "CNY"
  [107]=>
  string(8) "COLISTER"
  [108]=>
  string(10) "COMPPAIDON"
  [109]=>
  string(12) "CONTRACTDATE"
  [110]=>
  string(10) "CONTTOSHOW"
  [111]=>
  string(2) "CP"
  [112]=>
  string(3) "CRP"
  [113]=>
  string(4) "CTGF"
  [114]=>
  string(15) "CURRENTLYLEASED"
  [115]=>
  string(16) "DEED_GARAGE_COST"
  [116]=>
  string(17) "DEED_PARKING_COST"
  [117]=>
  string(3) "DIM"
  [118]=>
  string(3) "DIN"
  [119]=>
  string(3) "DIR"
  [120]=>
  string(17) "DISABILITY_ACCESS"
  [121]=>
  string(25) "DISABILITY_ACCESS_DETAILS"
  [122]=>
  string(3) "DPR"
  [123]=>
  string(3) "DRF"
  [124]=>
  string(3) "DRL"
  [125]=>
  string(3) "DRS"
  [126]=>
  string(3) "DRV"
  [127]=>
  string(3) "DRW"
  [128]=>
  string(1) "E"
  [129]=>
  string(3) "ELC"
  [130]=>
  string(3) "EQP"
  [131]=>
  string(11) "EXISTING_BF"
  [132]=>
  string(3) "EXP"
  [133]=>
  string(3) "EXT"
  [134]=>
  string(14) "EXT_PROP_FEATS"
  [135]=>
  string(16) "FEE_LEASE_AMOUNT"
  [136]=>
  string(3) "FIN"
  [137]=>
  string(18) "FIREPLACE_LOCATION"
  [138]=>
  string(3) "FND"
  [139]=>
  string(2) "FP"
  [140]=>
  string(3) "FPL"
  [141]=>
  string(15) "FRANCHISOR_FEED"
  [142]=>
  string(4) "FREQ"
  [143]=>
  string(3) "FRF"
  [144]=>
  string(3) "FRL"
  [145]=>
  string(3) "FRS"
  [146]=>
  string(3) "FRW"
  [147]=>
  string(10) "FULL_BATHS"
  [148]=>
  string(3) "GAR"
  [149]=>
  string(13) "GARAGE_ONSITE"
  [150]=>
  string(16) "GARAGE_OWNERSHIP"
  [151]=>
  string(11) "GARAGE_TYPE"
  [152]=>
  string(10) "GREENDISCL"
  [153]=>
  string(9) "GREENFEAT"
  [154]=>
  string(8) "GREENSRC"
  [155]=>
  string(4) "GRID"
  [156]=>
  string(2) "GS"
  [157]=>
  string(3) "GSD"
  [158]=>
  string(10) "HALF_BATHS"
  [159]=>
  string(3) "HEA"
  [160]=>
  string(3) "HEM"
  [161]=>
  string(4) "HERS"
  [162]=>
  string(2) "HS"
  [163]=>
  string(3) "HSD"
  [164]=>
  string(3) "HSN"
  [165]=>
  string(3) "IDX"
  [166]=>
  string(4) "IMPS"
  [167]=>
  string(4) "IMPW"
  [168]=>
  string(3) "INF"
  [169]=>
  string(14) "INT_PROP_FEATS"
  [170]=>
  string(12) "INTERNET_AVM"
  [171]=>
  string(15) "INTERNETLISTING"
  [172]=>
  string(2) "JH"
  [173]=>
  string(3) "JHD"
  [174]=>
  string(3) "KIT"
  [175]=>
  string(3) "KTF"
  [176]=>
  string(3) "KTL"
  [177]=>
  string(3) "KTS"
  [178]=>
  string(3) "KTW"
  [179]=>
  string(10) "LAADDRESS2"
  [180]=>
  string(11) "LACELLPHONE"
  [181]=>
  string(6) "LACITY"
  [182]=>
  string(7) "LAEMAIL"
  [183]=>
  string(11) "LAFAXNUMBER"
  [184]=>
  string(11) "LAFIRSTNAME"
  [185]=>
  string(4) "LAID"
  [186]=>
  string(10) "LALASTNAME"
  [187]=>
  string(13) "LAOFFICEPHONE"
  [188]=>
  string(13) "LAPAGERNUMBER"
  [189]=>
  string(7) "LASTATE"
  [190]=>
  string(12) "LASTREETNAME"
  [191]=>
  string(14) "LASTREETNUMBER"
  [192]=>
  string(3) "LAT"
  [193]=>
  string(8) "LAUNDRYF"
  [194]=>
  string(8) "LAUNDRYL"
  [195]=>
  string(8) "LAUNDRYS"
  [196]=>
  string(8) "LAUNDRYW"
  [197]=>
  string(5) "LAZIP"
  [198]=>
  string(2) "LD"
  [199]=>
  string(3) "LDR"
  [200]=>
  string(3) "LDS"
  [201]=>
  string(7) "LEASEXD"
  [202]=>
  string(4) "LIST"
  [203]=>
  string(3) "LLP"
  [204]=>
  string(3) "LMT"
  [205]=>
  string(2) "LN"
  [206]=>
  string(3) "LNG"
  [207]=>
  string(11) "LO_LOCATION"
  [208]=>
  string(6) "LOCITY"
  [209]=>
  string(7) "LOCKBOX"
  [210]=>
  string(7) "LOEMAIL"
  [211]=>
  string(11) "LOFAXNUMBER"
  [212]=>
  string(4) "LOID"
  [213]=>
  string(6) "LONAME"
  [214]=>
  string(7) "LOPHONE"
  [215]=>
  string(7) "LOSTATE"
  [216]=>
  string(12) "LOSTREETNAME"
  [217]=>
  string(14) "LOSTREETNUMBER"
  [218]=>
  string(5) "LOWWW"
  [219]=>
  string(5) "LOZIP"
  [220]=>
  string(2) "LP"
  [221]=>
  string(3) "LRF"
  [222]=>
  string(3) "LRL"
  [223]=>
  string(3) "LRS"
  [224]=>
  string(3) "LRW"
  [225]=>
  string(3) "LSZ"
  [226]=>
  string(3) "MAI"
  [227]=>
  string(13) "MANAGECOMPANY"
  [228]=>
  string(13) "MANAGECONTACT"
  [229]=>
  string(11) "MANAGEPHONE"
  [230]=>
  string(14) "MANAGINGBROKER"
  [231]=>
  string(16) "MASTER_ASSOC_FEE"
  [232]=>
  string(3) "MBB"
  [233]=>
  string(3) "MBF"
  [234]=>
  string(3) "MBL"
  [235]=>
  string(3) "MBS"
  [236]=>
  string(3) "MBW"
  [237]=>
  string(2) "MC"
  [238]=>
  string(4) "MODx"
  [239]=>
  string(3) "MPN"
  [240]=>
  string(2) "MT"
  [241]=>
  string(1) "N"
  [242]=>
  string(2) "NC"
  [243]=>
  string(3) "NCO"
  [244]=>
  string(16) "NO_GARAGE_SPACES"
  [245]=>
  string(17) "NO_PARKING_SPACES"
  [246]=>
  string(2) "OD"
  [247]=>
  string(3) "OLP"
  [248]=>
  string(3) "OMD"
  [249]=>
  string(3) "OPH"
  [250]=>
  string(3) "OSD"
  [251]=>
  string(2) "OT"
  [252]=>
  string(3) "OWN"
  [253]=>
  string(3) "OWR"
  [254]=>
  string(3) "PAR"
  [255]=>
  string(20) "PAR_FEE_LEASE_AMOUNT"
  [256]=>
  string(14) "PARKING_ONSITE"
  [257]=>
  string(17) "PARKING_OWNERSHIP"
  [258]=>
  string(3) "PHO"
  [259]=>
  string(10) "PHOTOCOUNT"
  [260]=>
  string(9) "PHOTODATE"
  [261]=>
  string(3) "PIN"
  [262]=>
  string(3) "PKN"
  [263]=>
  string(3) "PNT"
  [264]=>
  string(3) "POS"
  [265]=>
  string(14) "POST_DIRECTION"
  [266]=>
  string(13) "RECORDMODDATE"
  [267]=>
  string(7) "REMARKS"
  [268]=>
  string(15) "REMARKSINTERNET"
  [269]=>
  string(3) "TYP"
  [270]=>
  string(3) "RMS"
  [271]=>
  string(3) "ROF"
  [272]=>
  string(2) "RR"
  [273]=>
  string(1) "S"
  [274]=>
  string(10) "SAADDRESS2"
  [275]=>
  string(11) "SACELLPHONE"
  [276]=>
  string(6) "SACITY"
  [277]=>
  string(7) "SAEMAIL"
  [278]=>
  string(11) "SAFAXNUMBER"
  [279]=>
  string(11) "SAFIRSTNAME"
  [280]=>
  string(4) "SAID"
  [281]=>
  string(10) "SALASTNAME"
  [282]=>
  string(12) "SALE_OR_RENT"
  [283]=>
  string(15) "SALE_OR_RENT_LN"
  [284]=>
  string(13) "SAOFFICEPHONE"
  [285]=>
  string(13) "SAPAGERNUMBER"
  [286]=>
  string(3) "SAS"
  [287]=>
  string(7) "SASTATE"
  [288]=>
  string(12) "SASTREETNAME"
  [289]=>
  string(14) "SASTREETNUMBER"
  [290]=>
  string(5) "SAZIP"
  [291]=>
  string(3) "SCI"
  [292]=>
  string(16) "SECURESHOWASSIST"
  [293]=>
  string(11) "SELLERSCONC"
  [294]=>
  string(3) "SHO"
  [295]=>
  string(10) "SHORT_SALE"
  [296]=>
  string(11) "SO_LOCATION"
  [297]=>
  string(6) "SOCITY"
  [298]=>
  string(7) "SOEMAIL"
  [299]=>
  string(11) "SOFAXNUMBER"
  [300]=>
  string(4) "SOID"
  [301]=>
  string(6) "SONAME"
  [302]=>
  string(7) "SOPHONE"
  [303]=>
  string(7) "SOSTATE"
  [304]=>
  string(12) "SOSTREETNAME"
  [305]=>
  string(14) "SOSTREETNUMBER"
  [306]=>
  string(5) "SOWWW"
  [307]=>
  string(5) "SOZIP"
  [308]=>
  string(2) "SP"
  [309]=>
  string(15) "SP_INCL_PARKING"
  [310]=>
  string(13) "SPEC_SVC_AREA"
  [311]=>
  string(12) "SPEC_SVC_FEE"
  [312]=>
  string(2) "ST"
  [313]=>
  string(5) "STATE"
  [314]=>
  string(3) "STD"
  [315]=>
  string(3) "STR"
  [316]=>
  string(12) "STREETSUFFIX"
  [317]=>
  string(3) "STY"
  [318]=>
  string(3) "SUB"
  [319]=>
  string(3) "TAX"
  [320]=>
  string(7) "TOURURL"
  [321]=>
  string(3) "TPE"
  [322]=>
  string(3) "TRM"
  [323]=>
  string(3) "TWN"
  [324]=>
  string(3) "TXC"
  [325]=>
  string(3) "TXY"
  [326]=>
  string(2) "UD"
  [327]=>
  string(13) "VIRTUALPHOTOS"
  [328]=>
  string(2) "VT"
  [329]=>
  string(6) "VTDATE"
  [330]=>
  string(1) "W"
  [331]=>
  string(2) "WF"
  [332]=>
  string(2) "XD"
  [333]=>
  string(2) "ZP"
  [334]=>
  string(3) "ZP4"
}

array(251) {
  [0]=>
  string(2959) ""","Not Applicable","","","","","Basement","Recreation Rm","14X10","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","","Recreation Room","REO/Lender Owned","Yes","51-60 Years","Submit offers to www.offerrunway.com. This Property is eligible under the Freddie Mac First Look Initiative  through 8-30-14. See attached document for financing options. Buyer to verify all information.","None","Y","","","","","No","","Rockford","1312","Assessor","0","","","","","","","Main Level","10X9","","","Main Level","9X8","","","Not Applicable","","","Yes","Partially Finished","","Yes","","1955","2014-09-05T05:00:00","Full","19","3","3","0","1.1","","","","1","2.5%","ROCKFORD","2014-09-19T05:00:00","Winnebago","","Gross Sale Price","2014-09-05T05:00:00","","","ROCKFORD","","","","","98X126.6","","Van Wie Ave to address","No","","Yes","","Main Level","9X6","","","","","","","","Vinyl Siding,Brick","","","Cash","","","","","No","Not Applicable","","Main Level","15X15","","1","","Yes","Owned","Attached","","","","","","205","1","Gas,Forced Air","No","","","205","2217","Y","Sewer-Public","Public","None","","Y","All","","205","","","Main Level","12X10","","","(815) 713-1100","Byron","JLMurray@TRIMETRICS.com","(866) 861-5637","John","930015","Murray","(815) 713-1100","","IL","","6561Burberry","42.296906","","Not Applicable","","","61010","2014-08-11T05:00:00","2014-08-11T20:02:29","","","Exclusive Right To Sell","","26","08698572","-89.093094","93134","Rockford","","JOHN@trimetrics.com","(866) 861-5637","93134","Key Realty Inc.","(815) 713-1100","IL","E State St. Suite 11","5411","http://www.KeyRealtyUS.com","61108","39900","","Main Level","16X13","",".25-.49 Acre","None","","","","Yes","","None","","Main Level","13X10","","Off-Market","","","26","","N","","1","","","39900","2014-09-05T05:00:00","","","","Fee Simple","C/O LISTING OFFICE","","","","","","10","2014-08-11T20:08:52","1111355010","Garage","","Immediate","","2014-09-23T17:31:24","1312 Sq Ft Ranch home with 3 bedrooms and 1.5 baths. Has an attached 1 car garage. Family room with fireplace. Partially finished basement with rec room and 2nd fireplace. Fenced back yard with enclosed porch. Located close to hospital. \"As Is\".","Yes","Detached Single","8","","No","","","(847) 809-0472","Elgin","ruth_elders@yahoo.com","","Ruth","77121","Elders","No","","(847) 809-0472","","N","IL","Bedford Court","1010","60120","None","","No","Use automated showing system.","Foreclosed/REO","7375","Elgin","ruth_elders@yahoo.com","(847) 695-8804","7375","Ruth\'s Key Realty","(847) 695-8803","IL","Larkin","1000","","60123","39900","","N","","Closed","Illinois","2014-09-23T17:31:24","Van Wie","Avenue","","","2965.42","","Other","","ROCKFORD","","2013","2014-09-23T17:31:24","No","None","","","No","","61103","""
  [1]=>
  string(2853) ""","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","",".2024","No additional rooms","None","Yes","51-60 Years","","Central Air","Y","","","","","No","Oven/Range,Refrigerator,Washer,Dryer","Lombard","1490","Assessor","0","","","","","","Carpet","2nd Level","10X9","","Carpet","2nd Level","10X9","","","Not Applicable","","","Yes","Finished,Crawl","","Yes","","1957","","Partial","10","3","3","0","2","","","","2","2.5% -$200","LOMBARD","","Du Page","","Gross Sale Price","","","South","LOMBARD","","","","","66X134","","Wilson to Fairfield, North to Home.","No","","Yes","","Not Applicable","","","","","","","","","Vinyl Siding,Brick","","","","Family Room","","1","Wood Burning","No","Not Applicable","Wood Laminate","Lower","16X12","","2","7 Foot or more high garage door","Yes","Owned","Detached","","","","","WM HAMMERSCHMIDT ELEMENTARY SCHO","44","0","Gas","Yes","","GLENBARD EAST HIGH SCHOOL","87","818","Y","Sewer-Public","Lake Michigan","None","","Y","All","GLENN WESTLAKE MIDDLE SCHOOL","44","","Wood Laminate","Main Level","13X12","","","","Lombard","cc@jwreedymail.com","","Cecilia","235516","Whitt","(630) 915-0486","","IL","S Main St","1136","41.870651","","Lower","10X10","","60148","2014-08-13T05:00:00","2014-08-13T20:06:33","","","Exclusive Right To Sell","259900","42","08700782","-88.005875","24213","Lombard","","reedy@cin.net","(630) 629-0024","24213","J.W. Reedy Realty","(630) 629-0016","IL","S Main St","1136","","60148","254900","Hardwood","Main Level","17X14","","Less Than .25 Acre","None","","","","No","","None","Carpet","2nd Level","12X11","","Active","","","42","","N","","2","","","269900","","","","","Fee Simple","Gregory and Anthony Shannon","","","","","","21","2014-08-14T18:21:37","0617207013","Garage","","Closing","","2014-09-23T18:24:01","Great Lombard Location! Hardwood floors, updated baths, large bedrooms are just a few of the perks. Family room is large and has brick wood burning fireplace and lots of natural light. Garage is HUGE - 2 wide/2 deep (great man cave). Two tier deck in back yard with great landscaping. New Roof 2014, newer furnace, a/c and HWH. Walk to Madison Meadows. Great Family Home.","Yes","Detached Single","6","","No","","","","","","","","","","No","","","","N","","","","","None","","","CALL LISTING OFFICE OR USE SHOWINGASSIST","","","","","","","","","","","","","","","","N","","Price Change","Illinois","2014-09-23T18:24:01","Fairfield","Avenue","Bi-Level","","5175.78","http://www.VisualTour.com/showvt.asp?prt=45&t=3417318","Split Level","","YORK","","2013","2014-09-23T18:24:01","No","None","2014-08-14T19:06:53","","No","","60148","""
  [2]=>
  string(3045) ""","Not Applicable","","","","Other","Lower","Office","9X8","None","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","","Office","REO/Lender Owned","Yes","26-30 Years","ALL DISCLOSURES ARE ON THE MLS LISTING","Central Air","N","","","","Park/Playground,Tennis Courts,Pond/Lake,Curbs/Gutters,Sidewalks,Street Paved","No","Oven/Range,Refrigerator,Washer,Dryer","Orland Park","988","Assessor","0","Unfinished","","","","","Wood Laminate","Main Level","11X9","None","Wood Laminate","Main Level","9X8","None","","Not Applicable","","","No","Finished","","Yes","","1985","2014-08-28T05:00:00","Full","10","3","3","0","2","","","","2","2.5%","ORLAND PARK","","Cook","","Gross Sale Price","","","","ORLAND PARK","","No","","","60X120","","143RD TO 88TH SOUTH TO FAIRWAY DRIVE","No","","Yes","","Not Applicable","","Concrete","","0","Circuit Breakers","Ceiling Fan,Sump Pump","","S (South)","Vinyl Siding","Deck,Porch","","","","Block","0","","No","Not Applicable","","Not Applicable","","","2","","Yes","Owned","Detached","","","","","","135","0","Gas,Forced Air","No","","","230","8818","Y","Sewer-Public,Sewer-Storm","Lake Michigan","Interstate Access","Bar-Dry,Wood Laminate Floors,1st Floor Bedroom,1st Floor Full Bath","N","All","","135","Eating Area-Table Space","Wood Laminate","Main Level","13X10","None","","","Oak Lawn","jcastello101@comcast.net","","James","216933","Castello","(312) 320-0626","","IL","W. 95th St.","4925","41.624691","","Not Applicable","","","60453","2014-08-11T05:00:00","2014-08-12T22:10:25","Fenced Yard,Landscaped Professionally","","Exclusive Right To Sell","194900","44","08699921","-87.835114","22824","Oak Lawn","","jcastello101@comcast.net","(708) 346-0007","22824","Midwest Preferred","(708) 346-0002","IL","W. 95th St.","4925","http://www.castellohomes.com","60453","179900","Wood Laminate","Main Level","15X12","All",".25-.49 Acre","None","","","","Yes","","None","Wood Laminate","Main Level","12X11","None","Active","","No","44","0","N","","2","","","194900","","","","","Fee Simple","FREDDIE MAC","","","","","","6","2014-08-12T22:10:25","27102110270000","Garage","","Closing","","2014-09-23T17:44:43","REO WITH REPAIRS. THIS PROPERTY IS ELIGIBILE UNDER THE FREDDIE MAC FIRST LOOK INITIATIVE THROUGH 8/12/2014 THROUGH 8/31/2014 AND PARTIALLY FINISHED BASEMENT WITH OFFICE SPACE, REC ROOM/FAMILY ROOM AREA WITH BAR. INTERIOR PROFESSIONALLY PAINTED UPSTAIRS,NEW RANGE EXTERIOR PAINTED,NEW FRONT DOOR, GARAGE SERVICE DOOR NEW","Yes","Detached Single","6","Asphalt/Glass (Shingles)","No","17","","","","","","","","","No","","","","N","","","","","None","","","REGISTER","","","","","","","","","","","","","","","Yes","N","","Price Change","Illinois","2014-09-23T17:44:43","FAIRWAY","Drive","","FAIRWAY","4177","","1 Story","Conventional","ORLAND","Homeowner","2012","2014-09-23T17:44:43","No","None","","11","No","","60462","""
  [3]=>
  string(2698) ""","Not Applicable","","","","Carpet","Lower","Den","8X15","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","","Den","None","Yes","31-40 Years","","Central Air","N","","","","","No","Dishwasher,Disposal","Shorewood","1790","Assessor","0","","","","","","Carpet","2nd Level","12X10","","Carpet","2nd Level","10X15","","","Not Applicable","","","No","Unfinished","","No","","1978","","Partial","17","3","3","0","2","","","","2","2.5%-100","SHOREWOOD","","Will","","Net Sale Price","","","","SHOREWOOD","","","","","64X148X99X171","","Rt. 52 to River Rd North to Harvest to Greenfield","No","","Yes","","Not Applicable","","Concrete","","","","","","","Vinyl Siding,Brick","","","","Family Room","","1","","No","Not Applicable","Carpet","Lower","15X14","","2","","Yes","Owned","Attached","","","","","","30C","0","Gas","Yes","","JOLIET WEST HIGH SCHOOL","204","310","Y","Sewer-Public","Public","School Bus Service","","N","All","","30C","Eating Area-Table Space","Vinyl","Main Level","13X16","","","","Shorewood","melissa.bell@coldwellbanker.com","","Melissa","700280","Bell","(815) 342-3400","","IL","Westwood Dr","403","41.525295","","Not Applicable","","","60404","2014-08-13T05:00:00","2014-08-14T03:34:58","","","Exclusive Right To Sell","189900","42","08701202","-88.213531","74601","Joliet","","","(815) 744-1536","74601","Coldwell Banker Honig-Bell","(815) 744-1000","IL","Essington Rd","950","http://www.cbhonig-bell.com","60435","179900","Carpet","Main Level","16X14","",".25-.49 Acre","None","","","","No","","None","Carpet","2nd Level","14X12","","Active","","No","42","","N","","2","","","189900","","","","","Fee Simple","OOR","","","","","","18","2014-08-14T03:34:58","06093010400000","Garage","","Closing","","2014-09-23T17:55:25","Spacious three bedroom home in Country West Subdivision. Great location huge lot with mature trees. vaulted ceilings, lower level has a kitchenette. New carpeting in bedrooms. Family room fireplace, beautifully maintained home. Close to park.","Yes","Detached Single","7","Asphalt/Glass (Shingles)","No","","","","","","","","","","No","","","","N","","","","","None","","","Call CSS 1-877-977-1010 or text agent after hours","","","","","","","","","","","","","","","","N","","Price Change","Illinois","2014-09-23T17:55:25","Greenfield","Road","","Country West","4700","http://tours.databasedads.com/2987083/310-Greenfield-Rd-Shorewood-IL?u=1","Split Level w/ Sub","","TROY","","2013","2014-09-23T17:55:25","No","None","2014-08-15T02:10:39","","No","","60404","""
  [4]=>
  string(3244) ""","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","Not Applicable","","","","","","No additional rooms","REO/Lender Owned","Yes","81-90 Years","Visit Homesearch.com for more information. Financed offers require pre-qual from Greenlight Loans 888-668-5832. To present offers prior to the start of the online marketing event, please send to sales@homesearch.com. Info is not guaranteed.","Central Air","N","","","","","No","","CHI - Dunning","2511","Estimated","0","","","Last List Price","2014-09-06T05:00:00","","Hardwood","2nd Level","12X12","","Hardwood","2nd Level","12X10","","","","","","Yes","Finished","","Yes","247300","1930","2014-09-10T05:00:00","Full","8","3","3","0","3","","","","2","1% OR $400 WHICHEVER IS GREATER","CHICAGO","2014-09-23T05:00:00","Cook","","Net Sale Price","2014-09-10T05:00:00","","North","CHICAGO","","No","","","31 X 124","Separate","E/W on Addison to north on Plainfield, at Waveland","No","","Yes","Hardwood","Main Level","18X10","","","0","","","","","Frame","","","Cash","Living Room","","1","","No","Not Applicable","","Lower","22X17","","3","","Yes","Owned","Attached","","","","","","299","0","Gas,Forced Air","Yes","","","299","3706","Y","Sewer-Public","Lake Michigan,Public","None","Hardwood Floors,1st Floor Laundry,2nd Floor Laundry","N","All","","299","","Hardwood","Main Level","21X12","","","(773) 416-8520x","Glencoe","reo@risingrealty.com;offers@risingrealty.com","","Jason","171454","Shapiro","(773) 395-9999","","IL","Longmeadow Ln.","1193","41.94693","","Not Applicable","","","60022","2014-08-13T05:00:00","2014-08-13T15:27:54","","","Exclusive Right To Sell","247300","29","08700339","-87.834404","14858","Chicago","","reo@risingrealty.com","(773) 395-9553","14858","Rising Realty, LLC","(773) 395-9999","IL","W. Webster, #307","1820","http://www.risingrealty.com","60614","247300","Hardwood","Main Level","22X18","","Less Than .25 Acre","None","","","","No","","Full","Hardwood","2nd Level","20X19","","Off-Market","","","29","3706","N","","2","","","247300","2014-09-10T05:00:00","","","","Fee Simple","Owner of Record","","","","","","11","2014-08-13T15:27:54","12232170360000","Garage","750","Closing,Immediate","","2014-09-23T18:18:16","Legal 2unit reno\'d as SFR. Great open space, hardwood floors, huge master suite w/balcony. Full basement w/bar. SOLD \"AS IS\" CONDITION. NO SURVEY/DISCLOSURES. PROOF OF FUND/ PRE-APPROVAL MUST ACCOMPANY ALL OFFERS. ADDENDUM REQUIRED AFTER ACCEPTANCE. EM MUST BE CERT FUNDS. ROOMS, LOT SZE/DETAILS NOT GRNTD. Property is at Auction, all Auction terms apply.","Yes","Detached Single","7","","Yes","0","","","","","","Non","99999","Member","No","","","","U","IL","","","","None","","Yes","call CSS 877-977-1010","Foreclosed/REO","","Lisle","","","NONMEMBER","NON MEMBER","(630) 955-0011","IL","Warrenville RD","2443","","60532","256200","Yes","N","","Closed","Illinois","2014-09-23T18:18:16","PLAINFIELD","Avenue","","","5290","","2 Stories","","JEFFERSON","","2012","2014-09-23T18:18:16","No","None","","7200","No","","60634","1919""

Open in new window


sorry i did not realize it had multiple in $query how do loop that so i can build my update query

here is my full code block as is right now (its using a rets server pull)
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)";
                $query = '(RECORDMODDATE='.$hour_ago.'+)'; 
                // run RETS search 
                echo "  * Query: {$query}  Limit: {$limit}  Offset: {$offset}<br>\n"; 
                $search = $rets->SearchQuery(
                "Property",
                $class,
                $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
                        $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
                                    $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>";
// SETTING UP UPDATE QUERY
$set = NULL;
$dat = array_combine($fields_order_l2, $queue);
foreach ($dat as $key => $val)
{
    $set .= "'" . $key . "' = '" . $val . "',";
}
$set = rtrim($set, ',');
$query = "UPDATE $table SET ($set) WHERE...";
echo PHP_EOL . $query;
die('stop');
$query = "INSERT INTO `".$table."` (".$fields_order_l.") VALUES (" . implode(",",$queue) . ") ON DUPLICATE KEY UPDATE " . $set . "id=id";

                                              
				    
				    //$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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40340265
Where does the $queue variable come from?
0
 

Author Comment

by:Johnny
ID: 40340286
this is a rets server pull - what we talked about for RE. rets is sorta as i understand it a database it just has its own commands.

see bottom code block line 43 for $queue build line.
$queue[] .= $this_record;

Open in new window

this holds the data line 675 in the output block, each line being an array and comma delimited string.
(note the field names and query lines are not matching and im am unsure as to why right now) as the query injected just fine to BUILD the database in the first run this is now to update changes every hour.


Thanks as always for your interest in this matter
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40340406
Let me explore a work-around.  Instead of trying to pull an update, what would happen if you just retrieved all of the data for a complete rebuild of the database?  My thinking goes sort of like this, assuming the database name is RETS.

1. CREATE new database with name like "newRETS"
2. Pull RETS data and load new database
3. Query to rename existing RETS database with name like "oldRETS"
4. Query to rename newly loaded database with name like "RETS"

The last two rename steps will take place in a flash.  Poof - you've got a newly loaded database that appeared instantly.  Even if the data pull took an hour, the switchover would be fast.
0
 

Author Comment

by:Johnny
ID: 40340440
my problem is this in production will pull updates every hour, mind you on one table there are 5 tablas at one point that will be pulled it took 118k records from first pull to work all night for its full database now. and that was limiting back 6 months.

i also have a two fold thing here although if i had the database updated every hour i could pull it form there and not touch the rets server for the extra pulls. hrrrm

fyi this is whats rets is http://www.reso.org/ it got took over by reso. all RE databases are built on its standard so ive been told.

recap not sure that would work
i may resubmit this as it just got complicated. or maybe look for another ave to fix this problem.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

772 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