When i inserting data into mysql it insert two datas, and i dont know why

Question

When i inserting using this code it insert two datas and i downt know how to fix it

$sql = "SELECT Version_id FROM versions ORDER BY Version_id DESC LIMIT 1;";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $lastVersion =$row["Version_id"];
    }
}
echo($lastVersion);
$lastVersion++;
 $sql = "INSERT INTO versions (version)
VALUES ('v$lastVersion')";
if (mysqli_query($conn, $sql)) {
     echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

You mean 2 rows? Also, I'd think that you would need to add the value of `Version_id` to your newly added row as well. — jeroen, Sep 19 '16 at 10:03
specify a little more what you are working with. Your `SELECT` and `INSERT` commands don't match up. — RST, Sep 19 '16 at 10:15

score 1 · Accepted Answer · edited May 23 '17 at 10:34

While I don't exactly understand what you mean with "two datas", I do see multiple issues with your code.

First of all it is horribly inefficient and prone to race conditions. It's also quite wrong, in that it doesn't do what you want it. Not to mention should be replaced with native database functionality.

Most of these can be fixed by simply changing the version_id field to a AUTO_INCREMENT. This will automatically give the new record the next available ID in the set, exactly as what you're trying to do. Then you can retrieve this ID by using "lastInsertId()"

That'll make all of the code in your post superflous, and only require you do do something like this when actually inserting data:

$sql = "INSERT INTO `version`(`setting`, `date`) VALUES (:setting, :date)";
$stmt = $db->prepare ($sql);
$res = $stmt->execute ($data);
$newID = $db->lastInsertId ();

After this the new version ID is stored in the $newID variable.

Of course, if you want to UPDATE the version ID for some reason, then INSERT is the wrong command to use. Also, why use an entire table for what's basically a simple version number? In short, your whole table doesn't make a whole lot of sense for me.
I recommend explaining the rationale behind it, so that we can possibly come up with some better solutions you can use.

When i inserting data into mysql it insert two datas, and i dont know why

1 Answers1