SQL Returning error even though query successful

Question

I have a basic web form that takes 3 inputs and inserts them into a database table. The DB set up:

    require_once 'MDB2.php';
    include "sql-connect.php";

    $host = 'host.yyy.uk';
    $dbName = 'user01';
    $dsn = "mysql://$username:$password@$host/$dbName"; 

    $db = &MDB2::connect($dsn); 
    if (PEAR::isError($db)) { 
        die($db->getMessage());
    }

    $db->setFetchMode(MDB2_FETCHMODE_ASSOC);

But even if the data is entered correctly into the DB and the data is inputted into the DB, php returns an error (Invalid query: Duplicate entry 'AAA123(ACode)' for key 'PRIMARY). This is my code for handling the form:

if (isset($_POST['submit'])) {
    $sql = "INSERT INTO MODULES (APart, ACode, ATitle) VALUES ('$_POST[APart]', '$_POST[ACode]', '$_POST[ATitle]')";

    mysql_query($sql);

    $endResult = mysql_query($sql);

    if (!$endResult) {
        $message  = 'Invalid query: ' . mysql_error() . "\n";
        $message .= 'Whole query: ' . $sql;
        die($message);
    }
}

Sorry, 'Invalid query: Duplicate entry 'AAA123' for key 'PRIMARY' this is where 'AAA123' is the entry for ACode — Will Paull, Dec 01 '15 at 20:17
Maybe the error is it's way of saying you should stop using `mysql_` and use `mysqli_` or `PDO` instead... — Rasclatt, Dec 01 '15 at 20:18
as the error say, you cant have Duplicate entry for the primary key... — Amir Bar, Dec 01 '15 at 20:19
@amirbar that's the thing, the entries don't exist in the DB — Will Paull, Dec 01 '15 at 20:22
@Rasclatt I'm very new to this, and have no control over how the Database is set up, bar being able to modify the tables, can I still use mysqli? — Will Paull, Dec 01 '15 at 20:23
Yeah you can use either mysqli or pdo. They are libraries though php — Rasclatt, Dec 01 '15 at 20:24
Personally I would go with pdo, but you'll probably find mysqli_ more familiar — Rasclatt, Dec 01 '15 at 20:27
This query is wide open to SQL injection. `$_POST[APart]` should not be concatenated directly into the SQL query, this is an unsafe practice. — Ian, Dec 01 '15 at 20:55
@Ian yea I didn't include the part where i assigned them as variables, but will make sure I do when this code goes live. — Will Paull, Dec 02 '15 at 00:04

score 0 · Answer 1 · answered Jul 17 '18 at 03:09

You are running the query twice.

The first time, it succeeds but you do nothing with the result.

The second time, it fails - however, first succeeds, so you get some partial success (data is created).

Just remove the duplicate.

if (isset($_POST['submit'])) {
    $sql = "INSERT INTO MODULES (APart, ACode, ATitle) VALUES ('$_POST[APart]', '$_POST[ACode]', '$_POST[ATitle]')";

    // mysql_query($sql); /* REMOVE THIS LINE - this query succeeds */

    $endResult = mysql_query($sql); /* LEAVE THIS LINE - this fails if the first succeeds, because of your key constraints */

    if (!$endResult) {
        $message  = 'Invalid query: ' . mysql_error() . "\n";
        $message .= 'Whole query: ' . $sql;
        die($message);
    }
}

And also, look into PDO and MySQL libraries to prevent yourself from injection.

Sumithran · Answer 2 · 2018-07-17T09:27:40.443

0

Error:Duplicate entry means you are trying to insert a row which is already present in your Database.

Solution
Remove the primary key from the column (Acode) or add unique index on multiple columns instead of the primary key.

Indexes Best practices

edited Jul 17 '18 at 09:27

answered Jul 17 '18 at 03:18

Sumithran

6,217
4
40
54

SQL Returning error even though query successful

2 Answers2