How Can i solved this error:SQLSTATE[23000]: Integrity constraint violation: 106

MySQL database
#1

Hi,

I am facing an issue when inserting values from a form into my mysql -[php]table item_image[/php]- with the -[php]lastInsertId()[/php]-

Here is my db

[php]item table
id(auto increment,Primary key)
item_name (varchar)
item_description (tinytext)
item_price(varchar)
item_date(date)[/php]

[php]item_image
itemId(PRIMARY KEY),
small_image(varchar),
big_image(varchar)[/php]

HERE IS MY FORM

[php]

Name of Article :

Short Description:

Item small Image :

Item small Image:

[/php]

AND DOWN HERE WE HAVE THE PHP CODE

[php]if (isset($_GET[‘addform’])) {
$item_name = trim($_POST[‘item_name’]);
$item_description = trim($_POST[‘item_description’]);
$item_price = trim($_POST[‘item_price’]);

try {

$sql = ‘INSERT INTO items SET
item_name = :item_name,
item_description = :item_description,
item_price = :item_price,
item_date= CURDATE()’;
$stmt = $pdo->prepare($sql);
$stmt->bindValue(’:item_name’, $item_name);
$stmt->bindValue(’:item_description’, $item_description);
$stmt->bindValue(’:item_price’, $item_price);
$stmt->execute();
}
catch (PDOException $e) {
echo “Something went wrong”.$e->getMessage();
}

$itemId = $pdo->lastInsertId();

if (isset($_POST[‘addimages’])) {

$add_images = $_POST['addimages'];

try {
  $sql ='INSERT INTO item_image SET
          itemId = :itemId,
         small_image = :small_image,
         big_image = :big_image';
  $stmt = $pdo->prepare($sql);
     foreach ($add_images as $add_image) {
       $stmt->bindValue(':itemId', $itemId);
       $stmt->bindValue(':small_image', $add_image[0]);
       $stmt->bindValue(':big_image', $add_image[1]);
       $stmt->execute();
     }
}   
  catch (PDOException $e) {
  echo "Sth got wrong with the query".$e->getMessage();
}

}[/php]

The issue i am having is : All the values got insert in items table, but for the item_image table…i am having this error :

[php]SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘30’ for key ‘PRIMARY’[/php]

I am having searching, but cannot find the issue…my foreach loop seems to be correct…

I am trying to is to do is to: [php]insert the lastInsertId()[/php] value and [php]addimages array values[/php] into my[php] item_image[/php] table

Can sombody help out…Thanks.

#2

Have you checked the item_image to see if you have an entry with the itemId 30?

Quite often, this would be a debugging problem. Where you are testing and clearing out the item table but forgetting to clear out the item_image.

#3

Yes, the itemId 30 does exist in the items table!..and i woud like to know how to sort it out…do have any tips for me?

#4

You are trying to insert a record with an ID that already exists. The field name is set as a primary key so cannot have duplicates.

My suggestion would be to have an ID as primary key in item_image and have that auto increment and then have itemId to be set as the ID of the image in items. Structure would look like:

[PHP]
item_image
id(auto increment, PRIMARY KEY),
itemId(integer),
small_image(varchar),
big_image(varchar)
[/PHP]

#5

ahhhh…ok…yeah…that means that if i want to select the items images in my item_image table…i must do base on the value in itemId column…right?

#6

Correct.

Sponsor our Newsletter | Privacy Policy | Terms of Service