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

1. The importance of prepared statements
2. The full code
3. The explanation
4. Using the helper function
5. Comments (2)

The importance of prepared statements

The below example is using mysqli prepared statements to select a value from a database. And that's the only way such queries must be run, because it prevents syntax errors and SQL injections.

The full code

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 :'(";
}

The explanation

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 :'(";
}

Using the helper function

As you may notice, the amount of code is rather big for such a simple operation. But a programmer can always create their own tools, to automate repetitive routine tasks. And luckily, we have such a tool already, a simple mysqli helper function. Using this function the code will become as simple as

$sql = "SELECT count(1) FROM users WHERE name = ?";
$row = mysqli($mysqli, $sql, [$name])->get_result()->fetch_row();
$found = $row[0];
if ($found)
{
    echo "found :)";
} else {
    echo "not found :'(";
}