Check if email already exists in database and add/change data

Question

I've got a form which has 14 numeric inputs and 2 text inputs - name and email. Someone is adding data and it's saved to the database - I've done it. But when someone is adding data for the second time using the same email address, database should override the data in specific row with that email.

I read about UPDATE in sql but I don't know how to make a query which will check if that email exists and after that add or update data.

<?php
$servername = "localhost";
$username = "username";
$password = "pass";
$dbname = "test";
$quantity = $_POST['quantity'];
$quantity2 = $_POST['quantity2'];
$quantity3 = $_POST['quantity3'];
$quantity4 = $_POST['quantity4'];
$quantity5 = $_POST['quantity5'];
$quantity6 = $_POST['quantity6'];
$quantity7 = $_POST['quantity7'];
$quantity8 = $_POST['quantity8'];
$quantity9 = $_POST['quantity9'];
$quantity10 = $_POST['quantity10'];
$quantity11 = $_POST['quantity11'];
$quantity12 = $_POST['quantity12'];
$quantity13 = $_POST['quantity13'];
$quantity14 = $_POST['quantity14'];
$name = $_POST['name'];
$email = $_POST['email'];

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO Results (1paracwierc, 1paracwierc2, 2paracwierc, 2paracwierc2, 3paracwierc, 3paracwierc2, 4paracwierc, 4paracwierc2, 1parapol, 1parapol2, 2parapol, 2parapol2, final, final2, name, email)
VALUES ($quantity, $quantity2, $quantity3, $quantity4, $quantity5, $quantity6, $quantity7, $quantity8, $quantity9, $quantity10, $quantity11, $quantity12, $quantity13, $quantity14, '$name', '$email')";

if ($conn->query($sql) === TRUE) {
    echo "Saved.";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

A word of warning: don't insert user entered data directly into your SQL queries, otherwise you are prone to SQL injection attacks. Consider what happens if `$email` contains `blah'); drop table Results; --`. See http://php.net/manual/en/security.database.sql-injection.php for discussion and also http://stackoverflow.com/q/60174/21945 for a way to avoid this problem. — mhawke, Apr 21 '15 at 12:16
@mhawke and what if that input which stores the $email has the type="email"? When this input has type="email" the browser prevents from adding different data than email. — Marcin, Apr 21 '15 at 12:48
There is nothing to stop a user posting the form data to your server without a browser, e.g. using curl. Plus, older browsers might not support the ` — mhawke, Apr 21 '15 at 13:05

score 4 · Accepted Answer · answered Apr 21 '15 at 11:52

Use insert . . . on duplicate key update. You can do this if you have a unique key on what you want to be unique:

create unique index idx_results_name_email (name, email);

Then, the database will enforce uniqueness. The statement you want is:

INSERT INTO Results (1paracwierc, 1paracwierc2, 2paracwierc, 2paracwierc2, 3paracwierc, 3paracwierc2, 4paracwierc, 4paracwierc2, 1parapol, 1parapol2, 2parapol, 2parapol2, final, final2, name, email)
    VALUES ($quantity, $quantity2, $quantity3, $quantity4, $quantity5, $quantity6, $quantity7, $quantity8, $quantity9, $quantity10, $quantity11, $quantity12, $quantity13, $quantity14, '$name', '$email')
    ON DUPLICATE KEY UPDATE 1paracwierc = VALUES(1paracwierc),
                            1paracwierc2 = VALUES(1paracwierc2),
                             . . .
                            final2 = VALUES(final2);

Thank You very much. Could You please also tell me how to add different messages after adding new data and after updating? That it'll say "Saved" when the data is added and "Updated" when it's updated. — Marcin, Apr 21 '15 at 12:43
@Marcin . . . The `insert` value is controlled in the `VALUES()` statement that lists the variables. The `update` value is controlled by the `=` in the `on duplicate key update` part. You can put the appropriate values in each place. — Gordon Linoff, Apr 21 '15 at 23:51

Check if email already exists in database and add/change data

1 Answers1