How to insert unique email into mysql table

Question

I want to take email addresses from users to add them to a mailing list. However, I want to prevent duplicate entries, so I am using the INSERT IGNORE approach. I am using the PHP script below, but constantly receive this error:

Fatal error: Uncaught Error: Call to a member function bind_param() on bool

I've reviewed a LOT of SO articles on this error but still can't get it to work. I have confirmed that the $email and $id variables do have values. I suspect the error must have something to do with the use of IGNORE, but I honestly don't know.

Here's my code:

    $email = filter_input(INPUT_POST, 'email',FILTER_SANITIZE_EMAIL);
    $id = filter_input(INPUT_POST, 'id',FILTER_VALIDATE_INT) ?: NULL;
    
    $sqlQuery = 'INSERT IGRNORE INTO email(email, id) VALUES(:email,:id);';
    $stmt = $dbc->prepare($sqlQuery);
    $stmt->bind_param(':email',$email);
    $stmt->bind_param(':id',$id);
    $stmt->execute();
    mysqli_close($dbc);

I've tried including only one variable for the insert but I get the error against both bind_param lines. I also got the error when I had this structured to have both variables in a single bind_param entry.

I'm open to other ways of avoiding duplicate emails in the database, so long as they can be done with a single PHP file.

what happens if you run the sql command on the mysql server directly(with values in stead of placeholders)? — Techno, Nov 11 '20 at 21:57
You are mixing PDO and mysqli. mysqli doesn't have named placeholders nor single binding. — Dharman, Nov 11 '20 at 22:02
@RobBiermann running the command on the server still produced the same errors, even after removing the surplus R from the word IGNORE (@Strawberry). In the end I found success with the accepted answer to this question: https://stackoverflow.com/questions/25996040/mysql-insert-where-not-exists — jeffbartelli, Nov 13 '20 at 01:15

Dharman · Accepted Answer · 2021-02-19T21:09:00.520

You are mixing PDO and mysqli syntax. You need to pick one.

PDO

Open the connection, execute the statement without IGNORE, and then catch the exception to see why it failed. The code 1062 means that MySQL tried to insert a duplicate value.

$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'pass', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

try {
    $stmt = $pdo->prepare('INSERT INTO email(email, id) VALUES(:email,:id)');
    $stmt->execute([
        'email' => $email,
        'id' => $id
    ]);
} catch (PDOException $e) {
    if ($e->errorInfo[1] === 1062) {
        // duplicate
    } else {
        // If not 1062 then rethrow
        throw $e;
    }
}

mysqli

Using mysqli it's a little bit more work, but the same logic. With mysqli you can't use named placeholders and you can't bind-in-execute. The bind_param() function is very peculiar so pay special attention to the syntax.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'test');
$mysqli->set_charset('utf8mb4'); // always set the charset

try {
    $stmt = $mysqli->prepare('INSERT INTO email(email, id) VALUES(?, ?)');
    $stmt->bind_param('ss', $email, $id);
    $stmt->execute();
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() === 1062) {
        // duplicate
    } else {
        // If not 1062 then rethrow
        throw $e;
    }
}

You were right, I was mixing SQLI and PDO, but somehow it had been working across several files until this issue. I have now had to go through all my code to settle on only one format (PDO), but that's for the best. Thanks for the help! — jeffbartelli, Nov 11 '20 at 23:32
While this solution worked in my dev environment, it didn't work in production. I ended up using the accepted solution in this question: [link](https://stackoverflow.com/questions/25996040/mysql-insert-where-not-exists) — jeffbartelli, Nov 13 '20 at 01:10
@jeffbartelli What does it mean it didn't work? Did you get an error? — Dharman, Nov 13 '20 at 10:06
I didn't track down the specific error. When I did a basic test of the script it failed to save the record to the database in production, but not in my virtual dev environment. I tried isolating the script and running it but it only returned that the script had run successfully. Naturally I'm new with PHP so there was quite possibly better ways to return the error messages... — jeffbartelli, Nov 16 '20 at 07:54

How to insert unique email into mysql table

1 Answers1

PDO

mysqli