PHP - mysqli: Check if row exists

PHP
1

Hi,

I have a DB table where I want to check if a row that matches a given criteria exists.

I have the following:

$query = "SELECT EXISTS(SELECT * FROM users WHERE user_name = 'John' LIMIT 1)";
$result = mysqli_fetch_assoc(DB::$db->query($query));

I just couldn’t connect the dots to do the check with PHP, e.g.

if ($result == 1) {
  // User John exists...
}

Any ideas how I should do the check once I got the $result from the query?

Thanks.

2

why not just use count()?

$sql = "SELECT count(*) AS counter FROM users WHERE user_name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(['John']);
$counts = $stmt->fetch();
if($counts['counter'] == 1) ...
3

There will be multiple entries with the same criteria in the table. I want to check if there is at least one entry that matches the criteria. Also, I have read on multiple posts around the web that EXISTS is faster than count().

4

i see. you could run your SQL statement in an administration tool like PHPMyAdmin first and have a look at what data structure you get back. Then you may try to give your result an alias. And finally you can fetch this result like any other statement and check for the contents.

5

It returns the following with print_r($result):

Array ( [EXISTS(SELECT * FROM users WHERE user_name = 'John' LIMIT 1)] => 1 ) 1

Seems to be an associative array whose key is the SQL query, and its value is 1.

I used

if (current($result) == 1) $name_exists = true;

which seems to work. Not sure if using current() in this situation is ideal.

6

You could make it even more simple:

$sql = "SELECT TRUE FROM users WHERE user_name = ? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute(['John']);
$exists = $stmt->fetch();

if (!$exists) {
    // no match found
}
1 Like
7

Could you use AS to assign a name to the result field, and then use $result['name']?

1 Like
8

Just a literal answer for the question as in the title.

$name = 'John';
$stmt = DB::$db->prepare("SELECT 1 FROM users WHERE user_name = ? LIMIT 1");
$stmt->bind_param("s", $name);
$stmt->bind_result($exists);
$stmt->execute();
$stmt->fetch();
if ($exists) ...
1 Like
9

Hold on a minute. Why would you have duplicate usernames in the first place. Usernames should be unique.

10

True, but maybe the sample code is just a “for example”, and the requirement is for an entirely different usage that cannot be shared here for some reason.

11

Thanks, I think that can work.

It’s not username in the sense of login username. It is user’s name. Imagine a table of people some of which may share the exact same name.

Thank you all for your alternative suggestions. I am just trying to process the result that I get with the EXISTS syntax. I mean I want to learn how the returned data by an EXISTS statement is used after it is returned.

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.