CODEWITHSUNDEEP
phpmysqlpdo
jonmrich
jonmrich

Reputation: 4333

INSERT IGNORE INTO not working

My code looks like this (relevant parts):

$json = file_get_contents('php://input');

$obj = json_decode($json, true);
$device_id =$obj['device_id'];
$num =$obj['sensor_value'];
$sensor_value =intval($num);
$date = new DateTime();
$date->setTimezone(new DateTimeZone('America/New_York'));
$fdate = $date->format('Y-m-d H:i:s'); 

    $sql = "INSERT IGNORE INTO rr_emergency SET `device_id` = $device_id, `sensor_value` = $sensor_value, `date_time` = $fdate;";
    $result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);

Nothing gets posted to my database using this syntax. I'm trying to only insert a new record if a record doesn't exist with the same device_id value.

And, yes, I know this code appears to be at risk for SQL injection...the data comes from another webservice that I control, so that isn't an issue here.

Upvotes: 1

Views: 557

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

When sending queries with values, you should use parameterized queries. I suspect that is your problem. You can fix the root cause or you add single quotes for the string and date values. I would recommend the more standard syntax:

INSERT IGNORE INTO rr_emergency(device_id, sensor_value, date_time)
    VALUES($device_id, $sensor_value, '$fdate')";

Note: the error that you are getting is a compile-time error, because MySQL does not understand what the query means. INSERT IGNORE ignores run-time errors, but the query has to compile before it can run.

And, let me repeat the advice to use parameterized queries. If you had done so, your code would have worked much sooner.

EDIT:

If device_id should be unique in the table, then define a unique constraint:

alter table rr_emergency add constraint unq_emergency_device unique (device_id);

Then insert ignore will ignore any duplicate key errors.

Upvotes: 2

Related Questions