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";

}

**WARNING**: If you're just learning PHP, please, do not learn the obsolete `mysql_query` interface. It's awful and is being removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. Always be absolutely **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will have severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Apr 28 '15 at 18:43
@tadman Jay Blanchard usually comes in and posts this but I guess he's a little late to this party haha. — Ye., Apr 28 '15 at 18:45
Yes I realise its old code I'm im using the sync code that came from the phprets example. I will update it soon. Any ideas on where I went wrong with the INSERT? — dcoder50, Apr 28 '15 at 18:54
Try echoing `$query` to see what it looks like. Maybe there's a problem with quoting. — Barmar, Apr 28 '15 at 18:58
I did it seems to be correct. I couldnt post it here its too long — dcoder50, Apr 28 '15 at 19:21
@dcoder50 You should try and replicate this outside of PHP to see if you can isolate the problem. If it works externally, you can probably narrow down the problem to a specific fault in the PHP here. For example: Dump the query string before you run it and see if it looks correct or not. — tadman, Apr 28 '15 at 19:40

score 0 · Accepted Answer · answered May 07 '15 at 01:58

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).

insert or update mysql records with phprets

1 Answers1