适当的SQL语法，用于在PHP中使用“绑定值”时仅插入唯一条目

Question

I have an HTML form that allows users to type in a word and hit submit. This action sends the submitted word to a database table entitled 'word' which gives it an auto-incremented id and a timestamp for each submission.

I'm trying to prevent the database table from updating with duplicate word entries, and i'm also trying to incorporate prepared statements while doing this.

I have done a fair amount of research on this, but nothing I can find is helping me sort out the required syntax to pull this off. This submission got me close (MySQL: Insert record if not exists in table) but I cannot for the life of me figure out how to incorporate prepared statements in this solution that utilize bindvalue. I think my issue deals with preparing the statement before binding my values, but if that's the case I feel like I don't understand how to pull of the prepared statement.

I'm admittedly very new to all of this, so any and all solutions for improvement are welcome. Thanks. Here is the PHP and SQL code I have pieced together to get this working. Followed by that is the error message I receive.

if (!empty($_POST['word'])) {
    try {
        $sql = 'INSERT INTO word SET
                wordname = :wordname,
                worddate = now()
                SELECT * FROM (SELECT :wordname) AS tmp
                WHERE NOT EXISTS (SELECT wordname from word where wordname = :wordname) LIMIT 1';


        $s1 = $pdo->prepare($sql);
        $s1->bindValue(':wordname', $_POST['word']);
        $s1->execute();
    }
    catch (PDOException $e) {
        $error = 'Error submitting batch of forms.'.$e->getMessage();
        include 'error.html.php';
        exit();
    }
}

Error submitting batch of forms.SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM (SELECT wordname) AS tmp WHERE NOT EXISTS (SELECT wordname from word ' at line 4

Thanks once again.

Answer 1

Add a unique index to your wordname column, then you can just use a basic INSERT ... VALUES statement.

$sql = 'INSERT INTO word (wordname, worddate) VALUES (:wordname, now())';

With this query, your current bindValue code should work.

Based on the error you're currently getting, your PDO connection is already configured to throw exceptions, so your code will generate an PDOException if you try to insert a duplicate value for wordname. You can catch that specific error and respond accordingly. (This would be one of the "certain scenarios" referred to in that answer I linked.)