Appropriate SQL syntax for inserting only unique entries while using 'bind value' in 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.

Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, May 11 '17 at 17:55
If you don't want duplicate entries, why not just add a unique index to the column? — Don't Panic, May 11 '17 at 18:00

score 1 · Accepted Answer · edited May 23 '17 at 11:54

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.)

edited May 23 '17 at 11:54

Community

1
1

answered May 11 '17 at 18:18

Don't Panic

37,589
9
55
71

This worked nicely, thank you kindly. Now on to figuring out how to prevent auto increment from continuing its count despite not updating the database with a duplicate copy of a word...hmm – wordsinthemind May 11 '17 at 18:46
I don't think you can avoid that, but why do you need to? Does the autoincrement id have some meaning in your application? I would assume that it would just be a surrogate key. – Don't Panic May 11 '17 at 18:55

Appropriate SQL syntax for inserting only unique entries while using 'bind value' in PHP

1 Answers1