very strange duplicate entry error

Question

I am saving data repeatedly with code. The first time I run it it doesn't complain. The second time it says Duplicate entry '$id' for key 'PRIMARY'. I am echoing $id value and they are different everytime. The table is 5min old and I guess it can't be corrupted. Is my approach wrong?

function insertData($conn,$data){
    $id=$data['id'];
    $name=$data['name'];
    $fp=$data['first_price'];
    $sp=$data['second_price'];
    echo "$id<br>";
    echo "$name<br>";
    echo "$fp<br>";
    echo "$sp<br>";
    $query = 'INSERT INTO names VALUES("$id", "$name", "$fp", "$fp")';
    $result = $conn->query($query);
    if (!$result){
        echo "nothing saved, sorry $conn->error";
    }
}

table structure:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(15)  | NO   | PRI | NULL    |       |
| name         | varchar(150) | YES  |     | NULL    |       |
| first_price  | varchar(10)  | YES  | MUL | NULL    |       |
| second_price | varchar(10)  | YES  | MUL | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

scrowler · Accepted Answer · 2016-02-02T23:15:17.863

You're trying to insert the string literals $id etc into the table because you're using single quotes.

Here's an example that would work:

$query = "INSERT INTO names VALUES('$id', '$name', '$fp', '$fp')";

Now generally speaking you shouldn't need to insert a primary key value, just use null and it will auto increment if your table is set up that way. In your case it's not (auto_increment is not listed under "extra" for the primary key). Consider adding it.

I'll assume that the ->query() is from the PDO library, so to avoid SQL injection you should use parameter binding and your adjusted code would look like this:

$query = 'INSERT INTO names VALUES(?, ?, ?, ?)';
$stmt = $conn->prepare($query);
$stmt->execute(array($id, $name, $fp, $fp));

... or if you're using mysqli rather than PDO:

$query = 'INSERT INTO names VALUES(?, ?, ?, ?)';
$stmt = $conn->prepare($query);
$stmt->bind_param('isdd', $id, $name, $fp, $fp);
$stmt->execute();

Might want to give some details in case it's MySQLi, and not PDO (could go either way with the code provided). Otherwise good answer! — Qirel, Feb 02 '16 at 22:57

score 1 · Answer 2 · answered Feb 02 '16 at 22:48

Your query is literally inserting the string $id as you're using single quotes for your query string. Do this instead:

$query = "INSERT INTO names VALUES('$id', '$name', '$fp', '$fp')";

Just so you know the reasoning behind it, using single quotes for a string makes it so that variables are ignored.

very strange duplicate entry error

2 Answers2