insert or update mysql records with phprets

Question

I have some php code that uses the php library phprets to sync the current properties to MySQL database. I was using REPLACE INTO when putting the records in which worked great, I have MLS number set to UNIQUE. The issue is that I had to change that because I am adding geocoding with two additional columns lat and long to the table after the fact and REPLACE would delete the geocoding. So I changed over to a INSERT ON DUPLICATE UPDATE Statement. Not is seems to be not be inserting new records. The updating appears to be working fine.

foreach ($property_classes as $class) {

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

    //[][=$file_name = strtolower("property_{$class}.csv");
    //$fh = fopen($file_name, "w+");

    $maxrows = true;
    $offset = 1;
    $limit = 1000;
    $fields_order = array();

    while ($maxrows) {

        $query = "(Status=S,A,P,B,H),({$rets_modtimestamp_field}={$previous_start_time}+)";
        // 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));

        if ($rets->NumRows() > 0) {

            if ($offset == 1) {
                // print filename headers as first line
                $fields_order = $rets->SearchGetFields($search);
                //fputcsv($fh, $fields_order);
            }

            // process results
            while ($record = $rets->FetchRow($search)) {
                $this_record = array();
                foreach ($fields_order as $fo) {
                    $this_record[] = $record[$fo];
                }
                //fputcsv($fh, $this_record);
                $clean_records = str_replace('"', '', $this_record);
                $astring = implode('", "', $clean_records); 
                $astringTwo = '"'.$astring.'"';
                $fieldsstring = implode(",", $fields_order);
                $upArray = array_combine($fields_order,$clean_records);
                foreach ($upArray as $key => $value) {
                    $upArray[$key] = $key . "='" . $value."'";
                }
                $upStr=implode(", ", $upArray);
                $query="INSERT INTO CRMLS_property_residential ($fieldsstring) VALUES ($astringTwo) ON DUPLICATE KEY UPDATE $upStr";
                mysql_query($query);
                //echo $query;
            }

            $offset = ($offset + $rets->NumRows());

        }

        $maxrows = $rets->IsMaxrowsReached();
        echo "    + Total found: {$rets->TotalRecordsFound()}<br>\n";

        $rets->FreeResult($search);
    }

    //fclose($fh);

    echo "  - done<br>\n";

}

Answer 1

This doesn't answer your direct question, but if you change back to the REPLACE INTO process, you could pull the existing lat/lon values from your database and put those into the array so the rest of your process acts as if they did come from the RETS server (rather than blowing away any values you might have already had there). This also gives you the opportunity to determine whether or not you already have the geocodes (and if not, those could be done at that time and put in the same way).