CODEWITHSUNDEEP
phpmysqllaraveleloquent
UCAB
UCAB

Reputation: 39

Update laravel SQL table with Numeric value out of range:

I have an app that is receiving sensor data. As an illustration let's say the expected range is between 0.01 and 10. In this case in the migration I may have something like:

$table->float('example',5, 2)

This way I am able to handle an order of magnitude outside the expected range. However it is possible for the sensor to glitch and send values of say 10 000. As the sensor is sending an array of values it is likely that not all the data is incorrect so it is preferred to still write the data to the DB. For the initial insert I am using the code below which is working as expected:

DB::table($tableName)->insertOrIgnore($insert_array);

However, in some circumstances the sensor can resend the data in which case the record needs to be updated. It's possible that the value(s) that are out of range can remain in the array, in which case the update statement below will throw an out of range error:

DB::table($tableName)->where('$some_id','=', $another_id)->update($insert_array);

I have not been able to find an something akin to an "updateorignore" functionality. What is the best way to handle updating this record? Note I cannot simply put it in a try catch since this table will be a parent table to some children and ignoring it will result in some orphaned entries.

Upvotes: 0

Views: 615

Answers (2)

UCAB
UCAB

Reputation: 39

Thanks to James Clark Developer to help me get to the solution. Below is some sample code to resolve the problem:

                $values = array();
                //set up values for binding to prevent SQL injection
                foreach ($insert_array as $item) {
                        $values[] = '?'; 
                }

                //array values need to be in a "flat array"
                $flat_values = implode(", ", $values);
                
                //add in separators ` to column names
                $columns = implode("`, `",array_keys($insert_array));

                //write sql statement
                $sql = "INSERT IGNORE INTO `example_table` (`$columns`) VALUES 
                ($flat_values) ON DUPLICATE KEY UPDATE id = '$id'";
                DB::insert($sql, array_values($insert_array));
            }

Upvotes: 1

James Clark
James Clark

Reputation: 1325

Do you have some unique points of data to tie the records together, such as a timestamp or an id from the origin of the data? If so, you can use updateOrInsert

DB::table($tableName)
    ->updateOrInsert(
        ['remote_id' => $insert_array['remote_id'],
        [
           'example_field_1' => $insert_array['example_field_1'],
           'example_field_2' => $insert_array['example_field_2'],
        ]
    );

This is discussed at: https://laravel.com/docs/master/queries#updates

Upvotes: 1

Related Questions