PHP: Insert into MySQL database and check if already existing - with binding parameters

Question

I am trying to insert test data into a MySQL database using the below lines which works fine so far.

1) How can I check whether the email already exists in the database and if, echo a message? I saw references here to the use of WHERE EXISTS or mysqli_num_rows but I am not sure which and how to apply here - in combination with binding parameters.
2) I came across unset($username, $password, $database); to make this query more secure. Is that something that is needed / useful here and if, where should I put it ?

My PHP:

$conn = new mysqli($host, $username, $password, $database);
if($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare("INSERT INTO cust (email, pw) VALUES (?, ?)");
$stmt->bind_param("ss", $email, $hashPw);

$email = "me@mail.com";
$pw = "testpw12345";                
$hashPw = password_hash($pw, PASSWORD_DEFAULT); 
$stmt->execute();

echo "Success";

$stmt->close();
$conn->close();

[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 04 '19 at 16:43
As for your second question, it would be better to use constants or hardcode the values instead of using variables, which you would then need to unset. Also encapsulation would come in handy here. — Dharman, Oct 04 '19 at 17:07

score 1 · Answer 1 · answered Oct 04 '19 at 17:05

An alternative to the solution proposed already.

$stmt = $conn->prepare("SELECT COUNT(1) FROM cust WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$emailExists = $stmt->get_result()->fetch_row()[0];
if ($emailExists) {
    echo "This email address is already in use";
} else {
    // continue with insert code
}

This approach does not require you to close the statement. Once you execute get_result the statement data is fetched in full.
This solution also has a potential performance benefit. If your table contains many columns with many data, then fetching that data just to check if a record exists is a waste of CPU. Simply fetch COUNT(1) and check the single column of the single returned record. If it is 0, the value is falsish, if it is more than your if statement will evaluate to true and a message will be displayed. I would also strongly recommend to structure your code in such a way that you rarely have to use exit.

Nick · Accepted Answer · 2019-10-04T14:24:17.787

0

To check if the email already exists in the database, just try to select a row with it in:

$stmt = $conn->prepare("SELECT * FROM cust WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
    echo "This email address is already in use";
    exit;
}
$stmt->close();
// continue with insert code

In terms of your other questions, I don't see any reason to unset variables, and using prepared queries and password_hash gives you about as good protection as you can get.

edited Oct 04 '19 at 14:24

answered Oct 04 '19 at 13:59

Nick

138,499
22
57
95

Thanks for this ! I tried to add this but it gives me the following error - do I have to do something like "unbind" before I can bind the insert parameters (it seems the error is with that part): "Fatal error: Uncaught Error: Call to a member function bind_param() on bool in..." – Oct 04 '19 at 14:14
1

@Justin299 sorry, forgot the call to close which you need before preparing another statement. See my edit. – Nick Oct 04 '19 at 14:18
@YourCommonSense: Thanks. Can you say what is wrong or missing here ? – Oct 04 '19 at 14:26
1

so now it is correct. however the use of num_rows is still superfluous – Your Common Sense Oct 04 '19 at 14:26
@Justin299 I forgot the call to store_result – Nick Oct 04 '19 at 14:27
@YourCommonSense why is num_rows superfluous? – Nick Oct 04 '19 at 14:28
1

It more an opinion, but this function is useless and doesn't exist in other drivers. I would rather fetch the data selected. it will make calling close() unnecessary. – Your Common Sense Oct 04 '19 at 14:30
@Nick: Can you say why it is ignoring the count ? I am confused now and not sure what else to change. – Oct 04 '19 at 14:31
@YourCommonSense fair point. I guess it's 6 of one half a dozen of the other. If you fetch the data you then have to compare the result of whichever `fetch` you use. – Nick Oct 04 '19 at 14:32
1

@Justin299 did you see my comment about `store_result` and my edit? – Nick Oct 04 '19 at 14:32
@Justin299 good to hear. I apologise for wasting your time earlier with the bits I left out. – Nick Oct 04 '19 at 14:34
@Nick: No problem at all - happy it works in the end. – Oct 04 '19 at 14:35

PHP: Insert into MySQL database and check if already existing - with binding parameters

2 Answers2