How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
mysql, mysqli, pdo, php
Solution
Using mysqli prepared statements legacy approach:
$query = "SELECT 1 FROM `tblUser` WHERE email=?";
$stmt = $dbl->prepare($query);
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$emailExists = (bool)$row;
Using mysqli modern approach staring PHP 8.2:
$query = "SELECT 1 FROM `tblUser` WHERE email=?";
$result = $dbl->execute_query($query, [$email]);
$row = $result->fetch_assoc();
$emailExists = (bool)$row;
Using PDO prepared statement:
$email = $_POST['email'];
$stmt = $conn->prepare('SELECT 1 FROM `tblUser` WHERE email = :email');
$stmt->execute(["email" => $_POST['email']]);
$row = $result->fetch();
$emailExists = (bool)$row;
- Prepared statements are best to be used to help protect against an SQL injection.
N.B.:
When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.
- FYI: Forms default to a GET method if not explicity instructed.
Note: `<input type = "text" name = "var">` - `$_POST['var']` match. `$_POST['Var']` no match.
- POST arrays are case-sensitive.
Consult:
- http://php.net/manual/en/tutorial.forms.php
Error checking references:
- http://php.net/manual/en/function.error-reporting.php
- http://php.net/manual/en/mysqli.error.php
- http://php.net/manual/en/pdo.error-handling.php
Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using `mysql_` to connect with (and querying with).
- You must use the same one from connecting to querying.
Consult the following about this:
- Can I mix MySQL APIs in PHP?
If you are using the `mysql_` API and have no choice to work with it, then consult the following Q&A on Stack:
- Check if a row exists using old mysql_* API
The `mysql_*` functions are deprecated and will be removed from future PHP releases.
- It's time to step into the 21st century.
You can also add a UNIQUE constraint to (a) row(s).
References:
- http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
- http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
- How to check if a value already exists to avoid duplicates?
How add unique key to existing table (with non uniques rows)
Problem
I need help checking if a row exists in the database. In my case, that row contains an email address. I am getting the result: ``` email no longer exists publisher@example.com ``` This is the code I'm currently using: ``` if (count($_POST)) { $email = $dbl->real_escape_string(trim(strip_tags($_POST['email']))); $query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'"; $result = mysqli_query($dbl, $query); if (is_resource($result) && mysqli_num_rows($result) == 1) { $row = mysqli_fetch_assoc($result); echo $email . " email exists " . $row["email"] . "\n"; } else { echo "email no longer exists" . $email . "\n"; } } ``` Is there a better way to check if a row exists in MySQL database (in my case, check if an email exists in MySQL)?