php check email already in database table

Question

hi there im a little new to all of this so please bear with me.

I am trying to have a form that registers first, last, email into a table. if the email already exists id like to divert to a page and update the timestamp in the record for that email address. im not sure where to start. thanks in advance

edit: to clarify the goal here is

1.person A enters their details, if there is no record of their email address then a new record is created, timestamped and they proceed to the next page

person B enters their details, they have registered before, the database updates their records timestamp and they proceed to the next page

// Attempt insert query execution
try{
    // Create prepared statement
    $sql = "INSERT INTO sessions (first, last, email) VALUES (:first, :last, :email)";
    $stmt = $pdo->prepare($sql);

    // Bind parameters to statement
    $stmt->bindParam(':first', $_REQUEST['first']);
    $stmt->bindParam(':last', $_REQUEST['last']);
    $stmt->bindParam(':email', $_REQUEST['email']);

    // Execute the prepared statement
    $stmt->execute();
    header('Location: stream.php');
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);

If your column is set with constraint it will restrict you, beside this you can just try retrieve the record using the incoming email if found restrict from php side and update the timestamp. — Prafulla Kumar Sahu, Sep 29 '20 at 07:47
Check if the user exists using a `SELECT * FROM sessions WHERE email=:email`. — Ron van der Heijden, Sep 29 '20 at 07:56
hi and thanks for your answers. to put this another way im really new to all of this lol so if you could maybe help me with the recommended code and where to place it in what I have above that would be really appreciated — Patrick Dinsmore, Sep 29 '20 at 08:02
But if you are new, you should learn. So start with the pseudo. `if select email then update timestamp else insert session`. You have the code to insert, so the others shouldn't be a problem. — Ron van der Heijden, Sep 29 '20 at 08:05
Surprisingly nobody suggests `REPLACE INTO` or `INSERT .... ON DUPLICATE UPDATE` — Raptor, Sep 29 '20 at 08:23
Does this answer your question? [PHP how to check for email already in MySQL database?](https://stackoverflow.com/questions/9606304/php-how-to-check-for-email-already-in-mysql-database) — Madan Sapkota, Sep 29 '20 at 08:34

score 1 · Accepted Answer · answered Sep 29 '20 at 08:28

1

You can first set the email field in the database table as UNIQUE key.

Then, replace your SQL statement with:

INSERT INTO sessions (first, last, email) VALUES (:first, :last, :email)
ON DUPLICATE KEY UPDATE session_timestamp=NOW()

which I assume the Timestamp field you mentioned is named as session_timestamp.

The SQL statement inserts a new session row into the table when email does not exist in the table; otherwise, if the email already exists, it updates the timestamp field with current time. You do not require to forward to another page for another database action, which will:

make your code difficult to manage
waste loading time

Reference documentation

answered Sep 29 '20 at 08:28

Raptor

53,206
45
230
366

1

Yes, it is better to use one query, only issue will be if he wants to reuse the email duplication validation he can not use it, but rarely that will come. – Prafulla Kumar Sahu Sep 29 '20 at 08:31
@PrafullaKumarSahu That's just an opinion. I would never trust the database structure for my domain's logic. – Ron van der Heijden Sep 29 '20 at 09:36
@RonvanderHeijden yes, may be you would like to choose already existing structure because will reduce effort, there can be several option to go with, based on requirement and opinion. – Prafulla Kumar Sahu Sep 29 '20 at 09:40
1

@PrafullaKumarSahu I'm not talking about existing structures or reduced effort. I would not advice to hard-code domain logic in the database. – Ron van der Heijden Sep 29 '20 at 09:45
@RonvanderHeijden oh! yes that is a good suggestion. – Prafulla Kumar Sahu Sep 29 '20 at 09:46

Prafulla Kumar Sahu · Answer 2 · 2020-09-29T22:48:40.447

0

create a function using this code and call that before insert to check if email already exists. I believe for registration and updating email address you need to check if email already exists, so you can also reuse it. I assume

You have pdo object

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

then use

function email_exists($pdo, $email){
    $stmt = $pdo->query("SELECT * FROM sessions WHERE email=:email");
    $stmt->execute(['email' => $email]); 
    if ($stmt->fetch()){
       return true;
    } else {
        return false;
    }
}

Please try to go through the documentation, it will help you getting everything done.

edited Sep 29 '20 at 22:48

answered Sep 29 '20 at 08:13

Prafulla Kumar Sahu

9,321
11
68
105

still incorrect. `$pdo` is inaccessible in this function. – Raptor Sep 29 '20 at 08:21
@Raptor I have added $pdo as parameter to function. I would also like to suggest to create a class so that pdo object will be avaialble through out the class and using $this->pdo, he can do the job. – Prafulla Kumar Sahu Sep 29 '20 at 08:25
Maybe, but it really depends on the overall code structure. If the code is just a tiny script, creating a class to wrap the whole thing up is too much. – Raptor Sep 29 '20 at 08:32
@Raptor yes, but my assumption is at least he will b having create, insert, select, delete, update, so wrapping these makes sense. – Prafulla Kumar Sahu Sep 29 '20 at 08:33

php check email already in database table

2 Answers2