Error: Duplicate entry "0" for the "PRIMARY" key

Question

Mh, I cannot solve my problem, this is a mysql error: Error: Duplicate entry '0' for key 'PRIMARY'

when I had only one record in the database, I can edit and update my data, when I add two lines, I got an error.

Some photos from the database

And when I change the line, the line identifier is reduced to 0, and this is the problem, why I can not edit other lines.

 CREATE TABLE `dati` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `value1` varchar(255) NOT NULL, `value2` varchar(255) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 PACK_KEYS=1 

Update code:

 <?php // Izlabot datus datubāzē! $titletxt = $_POST['title_edit']; $value1 = $_POST['value1_edit']; $value2 = $_POST['value2_edit']; if(isset($_POST['edit'])){ $con=mysqli_connect("localhost","root","","dbname"); if (mysqli_connect_errno()) { echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error(); } $sql="UPDATE dati SET ID='$ID',title= '$titletxt',value1='$value1',value2='$value2' WHERE 1"; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo '<script> alert(" Ieraksts ir veiksmīgi labots! "); window.location.href = "index.php"; </script>'; mysqli_close($con); } ?> 

From the form:

 <?php $con=mysqli_connect("localhost","root","","dbname"); if (mysqli_connect_errno()) { echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error(); } $result = mysqli_query($con,"SELECT * FROM dati"); while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td><input id='titled' type='text' name='title_edit' value='" . $row['title'] . "'></td>"; echo "<td><input id='value1d' type='text' name='value1_edit' value='" . $row['value1'] . "'></td>"; echo "<td><input id='value2d' type='text' name='value2_edit' value='" . $row['value2'] . "'></td>"; echo "<input type='hidden' name='id' value='" . $row['ID'] . "'>"; echo "<td><button name='edit' id='edit_btn' class='frm_btns' value='" . $row['ID'] . "'>Edit</button></td>"; echo "</tr>"; } mysqli_close($con); ?> 

They did not read the ID, because it always gives 0 number.

Answer 1

For those who came to this question because of the name of the question (like me), this solved my problem:

This error may indicate that the PRIMARY KEY table is not set to AUTO-INCREMENT (and your insert request did not indicate an ID value).

To solve:

Make sure that the PRIMARY KEY is set in your table, and in which the PRIMARY KEY is set to AUTO-INCREMENT.

How to add auto increment to mysql database column using phpmyadmin?

Answer 2

The problem is that your code is trying to change every line in the data that changes the primary key by a value in $ ID. This is not set anywhere in your code and appears to display as 0

 $sql="UPDATE `dati` SET `ID`='$ID',`title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1"; 

The primary key value must be sent to the form and returned so that it can be processed by your code, but the value must be saved, therefore ....

 $sql="UPDATE `dati` SET `title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE `ID`=$ID"; 

You should also read about MySQL injection - even after fixing the errors here, anyone can do whatever they want with your database.

Answer 3

The problem is set ID = $ID

Try removing it so that the code is

 $sql="UPDATE `dati` `title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1"; 

Be sure to change this value because it will update the row with these values

Answer 4

Try the following:

 ID int(11) PRIMARY KEY AUTOINCREMENT(1,3) 

Answer 5

Just make sure your primer keys are also AI.