How do I prevent duplicate email entries in my code for PHP?

Question

$q = "INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES ('$fn', '$ln', '$e', SHA1('$p'), NOW() )";       
        $r = @mysqli_query ($dbc, $q); // Run the query.
        if ($r) { // If it ran OK.

            // Print a message:
            echo '<h1>Thank you!</h1>
        <p>You are now registered. In Chapter 12 you will actually be able to log in!</p><p><br /></p>';    

        } else { // If it did not run OK.

            // Public message:
            echo '<h1>System Error</h1>
            <p class="error">You could not be registered due to a system error. We apologize for any inconvenience.</p>'; 

            // Debugging message:
            echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

        } // End of if ($r) IF.

        mysqli_close($dbc);

I'm trying to use mysqli_query, and mysqli_num_rows() but am not having any luck.

I am not able to use UNIQUE because it doesn't do quite what i need, I need to use mysqli_num_rows()

When you update and the entry exists, then the number of inserted rows is 0. — Markus Zeller, Apr 06 '20 at 18:25
**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Apr 07 '20 at 09:28

score -2 · Answer 1 · answered Apr 06 '20 at 19:11

//Function to Check If a Record Already Exist

<?php
        function recordExists($table, $where, $dbc) {
            $query = "SELECT * FROM `$table` WHERE $where";
            $result = $conn->query($query);

            if($result->num_rows > 0) {
                return true;    // The record(s) do exist
            }
            return false;       // No record found
        }
    ?>

//Sample Usage Below

<?php
        $exists = recordExists("users", "email= '$e'", $dbc);
        if ($exists == '1') {
            // This Email Already Exist
            // Print Error Message
        }
        else{
            // Email Does Not Exist
            // Insert Record into the Database
        }

    ?>

This is far from efficient, and relies on implementing data integrity exclusively in code when the same objective can be achieved with no overhead in the database. It also requires a lot more code complexity than `alter table users add unique index emailaddr(emailaddr)` — symcbean, Apr 06 '20 at 21:41

How do I prevent duplicate email entries in my code for PHP?

1 Answers1