How to check whether a value exists in a database using mysqli prepared statements

  1. Comments

To see whether some value exists in a database, we should never select the data itself, but only the count of rows that contains the requested value.

$sql  "SELECT count(1) FROM users WHERE name = ?";
$stmt $mysqli->prepare($sql);
$stmt->bind_param("s"$name);
$stmt->execute();
$stmt->bind_result($found);
$stmt->fetch();
if (
$found)
{
    echo 
"found :)";
} else {
    echo 
"not found :'(";
}

Let's see what's going on here

$sql  "SELECT count(1) FROM users WHERE name = ?";

The SQL query we are running here does have a peculiar fields part: in fact we aren't selecting any actual field from the table, but only the result of the aggregation function count(1). The 1 parameter doesn't have any actual meaning, and it could be any constant value. It says mysql to count all the rows returned.

$stmt $mysqli->prepare();
$stmt->bind_param("s"$name);
$stmt->execute();

that's usual prepare/execute stuff that executes our query and makes $name variable bound to a question mark in the query.

$stmt->bind_result($found);
$stmt->fetch();

here we are storing the single result returned by the query (the result of count(1) function) to the $found variable.

Now $found variable contains the number of rows found. If no rows found, it contains 0, which is equal to FALSE when used in a conditional operator, while any other value will be counted as TRUE:

if ($found)
{
    echo 
"found :)";
} else {
    echo 
"not found :'(";
}

Related articles: