How to manage MySQL insert and update actions using the phprets library

Other Questions MySQL
1

I’m developing a PHP application that uses the phprets library to synchronize property data from an MLS to a MySQL database. I initially used REPLACE INTO statements, which functioned well because my MLS number was marked as UNIQUE.

However, I encountered a challenge when I integrated geocoding features that necessitated two new columns for latitude and longitude. The REPLACE statement was overwriting the geocoding data I’d manually added, so I transitioned to using INSERT ON DUPLICATE KEY UPDATE instead.

Now, I’m dealing with another problem: the INSERT ON DUPLICATE KEY UPDATE command is not adding new records to the database. While the updates for existing records seem to process correctly, the new entries are not being inserted.

foreach ($property_types as $type) {
    echo "Processing: {$type}\n";
    
    $has_next = true;
    $offset_value = 1;
    $page_limit = 500;
    $fields_sequence = array();
    
    while ($has_next) {
        $search_parameters = "(PropertyStatus=Active,Pending,Sold),({$modification_timestamp}={$last_timestamp}+)";
        echo "Searching: {$search_parameters} Batch Size: {$page_limit} Offset: {$offset_value}\n";
        
        $returned_results = $rets_client->SearchQuery("Property", $type, $search_parameters, 
            array('Limit' => $page_limit, 'Offset' => $offset_value, 'Format' => 'COMPACT-DECODED', 'Count' => 1));
        
        if ($rets_client->NumRows() > 0) {
            if ($offset_value == 1) {
                $fields_sequence = $rets_client->SearchGetFields($returned_results);
            }
            
            while ($row = $rets_client->FetchRow($returned_results)) {
                $record_data = array();
                foreach ($fields_sequence as $col) {
                    $record_data[] = $row[$col];
                }
                
                $cleaned_data = str_replace('"', '', $record_data);
                $data_string = implode('", "', $cleaned_data);
                $finalized_data = '"' . $data_string . '"';
                $columns_list = implode(",", $fields_sequence);
                
                $update_fields = array_combine($fields_sequence, $cleaned_data);
                foreach ($update_fields as $field => $value) {
                    $update_fields[$field] = $field . "='" . $value . "'";
                }
                $update_query = implode(", ", $update_fields);
                
                $query_string = "INSERT INTO MLS_properties ({$columns_list}) VALUES ({$finalized_data}) ON DUPLICATE KEY UPDATE {$update_query}";
                mysql_query($query_string);
            }
            
            $offset_value += $rets_client->NumRows();
        }
        
        $has_next = $rets_client->IsMaxrowsReached();
        echo "Total records found: {$rets_client->TotalRecordsFound()}\n";
        
        $rets_client->FreeResult($returned_results);
    }
    
    echo "Processing complete\n";
}

What factors could be causing the failure to insert new records while updates proceed normally?

2

Your issue looks like it’s with the unique constraint handling in your INSERT ON DUPLICATE KEY UPDATE query. When it updates existing records instead of inserting new ones, it means the duplicate key detection is firing when it shouldn’t. I ran into this exact problem with property data sync. Turned out some fields had empty strings or NULLs that weren’t being handled consistently. Check if your MLS number field (or other unique fields) are getting empty strings instead of actual NULL values. Your str_replace function might also be creating duplicates where there shouldn’t be any. I’d add some debugging - log the actual SQL queries and examine a few cases where new records should insert but don’t. Also, ditch mysql_query since it’s deprecated. Switch to mysqli or PDO with prepared statements. You’ll get better security and error reporting, which will help figure out why the inserts are failing.