There are multiple ways to check if a value exists in the database. Let me demonstrate how this can be done properly with PDO and mysqli.
PDO
PDO is the simpler option. To find out whether a value exists in the database you can use prepared statement and fetchColumn(). There is no need to fetch any data so we will only fetch 1 if the value exists.
<?php
// Connection code.
$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new \PDO('mysql:host=localhost;port=3306;dbname=test;charset=utf8mb4', 'testuser', 'password', $options);
// Prepared statement
$stmt = $pdo->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->execute([$_POST['email']]);
$exists = $stmt->fetchColumn(); // either 1 or null
if ($exists) {
echo 'Email exists in the database.';
} else {
// email doesn't exist yet
}
For more examples see: How to check if email exists in the database?
MySQLi
As always mysqli is a little more cumbersome and more restricted, but we can follow a similar approach with prepared statement.
<?php
// Connection code
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli('localhost', 'testuser', 'password', 'test');
$mysqli->set_charset('utf8mb4');
// Prepared statement
$stmt = $mysqli->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$exists = (bool) $stmt->get_result()->fetch_row(); // Get the first row from result and cast to boolean
if ($exists) {
echo 'Email exists in the database.';
} else {
// email doesn't exist yet
}
Instead of casting the result row(which might not even exist) to boolean, you can also fetch COUNT(1) and read the first item from the first row using fetch_row()[0]
For more examples see: How to check whether a value exists in a database using mysqli prepared statements
Minor remarks
- If someone suggests you to use
mysqli_num_rows(), don't listen to them. This is a very bad approach and could lead to performance issues if misused.
- Don't use
real_escape_string(). This is not meant to be used as a protection against SQL injection. If you use prepared statements correctly you don't need to worry about any escaping.
- If you want to check if a row exists in the database before you try to insert a new one, then it is better not to use this approach. It is better to create a unique key in the database and let it throw an exception if a duplicate value exists.
