CODEWITHSUNDEEP
mysqlsql
Arvis
Arvis

Reputation: 319

Error: Duplicate entry '0' for key 'PRIMARY'

I can't resolve my problem, this is the error from mysql that I'm getting:

Error: Duplicate entry '0' for key 'PRIMARY'

I can edit and update my data when I've got one record in the database but when I add two rows, I get the error.

Some pictures from database

And when I change the row, row ID goes down to 0 and that's is a problem as I can't edit other rows.

When i try to change row, first row ID goes down to 0 Database

enter image description here

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 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);
        ?>

It couldn't read the value of ID, as 0 was returned.

Upvotes: 29

Views: 181778

Answers (9)

Stefano Munarini
Stefano Munarini

Reputation: 2717

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

This will set the ID attribute to auto increment its value for every new row inserted in the table, thus preventing duplicate keys.

Upvotes: 4

Shiqing
Shiqing

Reputation: 47

for me it's because when exporting the db, somehow the order of the column is messed up (eg. INSERT INTO 'table' VALUES(value1,value2) becomes INSERT INTO 'table' VALUES(value2,value1))

I have to specify --complete-insert option which ensures that the exported SQL statements include column names explicitly

Upvotes: 1

Rupesh Jha
Rupesh Jha

Reputation: 11

i am using phpmyadmin, so go to db , search for wp_postmeta tabel add AI(auto-increment) to meta_id save the changes

enter image description here

Upvotes: 1

CrazyWooki
CrazyWooki

Reputation: 43

I'd been struggling to fix this. My tables had auto increment (AI) switched on Before I started tinkering with records I tried a simple repair in phpMyAdmin. Go to the SQL tab and run each command in turn.

REPAIR TABLE wp_options REPAIR TABLE wp_users REPAIR TABLE wp_usermeta

This did the trick for me and allowed me to login.

Upvotes: 2

Jose Carlos Ramos Carmenates
Jose Carlos Ramos Carmenates

Reputation: 2943

The error log like (In my case), I'm using Aurora DB:

PHP message: WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO `date173_postmeta

How to fix it using MySQL Workbench:

1- Connect at your DB, and go to the table with the issue, in my case date173_postmeta

2- Select the tools icon:

enter image description here

3- In the windows/tab at right, select the AI checkbox and click on Apply button:

enter image description here

Following the last steps my issues gone.

Upvotes: 7

Eyal Sooliman
Eyal Sooliman

Reputation: 2248

Just make sure that your primery keys are also A-I.

Upvotes: 3

cssyphus
cssyphus

Reputation: 40038

For those arriving at this question because of the question title (as I did), this solved my problem:

This error can indicate that the table's PRIMARY KEY is not set to AUTO-INCREMENT, (and your insert query did not specify an ID value).

To resolve:

Check that there is a PRIMARY KEY set on your table, and that the PRIMARY KEY is set to AUTO-INCREMENT.

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

Upvotes: 54

symcbean
symcbean

Reputation: 48357

The problem is that your code attempts to change every row in the data changing the primary key to the value in $ID. This is not set anywhere in your code, and presumably is being cast as 0

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

The primary key value should be sent to the form and returned so it can be processed by your code, but the value should be retained, hence....

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

You should also read up on MySQL injection - even after you've fixed the errors here, anyone can do just about anything they want with your database.

Upvotes: 5

a14m
a14m

Reputation: 8055

The problem in set ID = $ID

Try removing it so the code should be 

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

Be sure to change this where cause it'll update ever row with these values

Upvotes: 3

Related Questions