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 [email protected]
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 (in my case, check if an email exists in MySQL)?
You have to execute your query and add single quote to $email in the query beacuse it's a string, and remove the is_resource($query)
$query is a string, the $result will be the resource
$query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
$result = mysqli_query($link,$query); //$link is the connection
if(mysqli_num_rows($result) > 0 ){....}
UPDATE
Base in your edit just change:
if(is_resource($query) && mysqli_num_rows($query) > 0 ){
$query = mysqli_fetch_assoc($query);
echo $email . " email exists " . $query["email"] . "\n";
By
if(is_resource($result) && mysqli_num_rows($result) == 1 ){
$row = mysqli_fetch_assoc($result);
echo $email . " email exists " . $row["email"] . "\n";
and you will be fine
UPDATE 2
A better way should be have a Store Procedure that execute the following SQL passing the Email as Parameter
SELECT IF( EXISTS (
SELECT *
FROM `Table`
WHERE `email` = @Email)
, 1, 0) as `Exist`
and retrieve the value in php
Pseudocodigo:
$query = Call MYSQL_SP($EMAIL);
$result = mysqli_query($conn,$query);
$row = mysqli_fetch_array($result)
$exist = ($row['Exist']==1)? 'the email exist' : 'the email doesnt exist';
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 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?
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
mysqli_num_rows()
, don't listen to them. This is a very bad approach and could lead to performance issues if misused.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.After validation and before INSERT check if username already exists, using mysqli(procedural). This works:
//check if username already exists
include 'phpscript/connect.php'; //connect to your database
$sql = "SELECT username FROM users WHERE username = '$username'";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$usernameErr = "username already taken"; //takes'em back to form
} else { // go on to INSERT new record
Source: Stackoverflow.com