View original (French)

Erreur Duplicate entry '2' pour la clé 'PRIMARY'

Etudiante -  
Pitet Posted messages 2845 Status Member -
Hello, <break> It has now been 2 days that I’m stuck on this problem; I want to be able to add different content to different subjects. I therefore executed the following code in two different pages. <break> When there is nothing in the database it works. The first content goes in, the second as well BUT when you want to add a third content this error is displayed: <break> "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'PRIMARY'' in ...\class.pdogsb.inc.php on line 203
(! ) PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'PRIMARY' in ...\class.pdogsb.inc.php on line 203" <break>
After looking on several forums I noticed that the primary key needed to be auto-incremented, I did that but the same problem persists.... <break>
If you could help me, thanks in advance :) <break>

case 'ajouterContenu':{
$autre='<form method=post ACTION="index.php?uc=admin&action=contenuAjoute">';
$listeDeroulante=$pdo->getSujetPage();
$autre.=$listeDeroulante;
include("vues/admin/v_ajouter.php");
break;
}

case 'contenuAjoute':{
$text=$_POST['text'];
$codeSujet=$_POST['sujet'];
$textPage=$pdo->ajoutContenu($codeSujet,$text);
include("vues/admin/v_accueilAdmin.php");
break;
}

public function ajoutContenu($codeSujet,$textPage){
$req = "SELECT 'code' as code
FROM 'contenu'";
$res = PdoGsb::$monPdo->query($req);
$laLigne = $res->fetch();
$codeContenu=$laLigne['code']+1;
$req="INSERT INTO 'borg'.'contenu' ('code', 'code_sujet', 'text_contenu') VALUES ('".$codeContenu."', '".$codeSujet."', '".$textPage."');";
$res = PdoGsb::$monPdo->query($req);
return "Nouveau contenu ajouté";
}</break></break></break></break></break></break>

1 answer

  1.
    Pitet Posted messages 2845 Status Member 530
     
    Hi,

    Your query SELECT 'code' as code FROM 'contenu' returns all codes from the table contenu, so if you have 2 contents, you get the following list:
    1
    2

    Since you only do a single fetch, you retrieve the first code from the previous list, namely 1, to which you add 1, i.e. the value 2. But the content with code 2 already exists.
    To fix this, you should modify your select query to retrieve the last added code, for example:
    SELECT 'code' as code FROM 'contenu' ORDER BY code DESC
    With this query we sort the results in descending order, thus we obtain the following list:
    2
    1

    The rest of the code will therefore work correctly. Moreover, since we want to retrieve only the last added code, we can limit the result of our select query to a single result:
    SELECT 'code' as code FROM 'contenu' ORDER BY code DESC LIMIT 1

    But...
    If in your database you have properly defined the field code as auto_increment, it will automatically increment with each insert query, so you don’t need to specify it in these queries:
    $req="INSERT INTO 'borg'.'contenu' ('code_sujet', 'text_contenu') VALUES ('".$codeSujet."', '".$textPage."');";

    Thus you don’t need to perform a select query to retrieve the last used code.

    Have a great day
    1