check if SQL row exists with PHP

Question

I'm using MySQL with PHP and I need to do something like this (pseudocode):

if (sql row exists where username='bob')
{
    // do this stuff
}

See PHP Manual Example #1 of [mysql_fetch_assoc](http://php.net/manual/en/function.mysql-fetch-assoc.php) — brady.vitrano, Jul 08 '11 at 04:39
Does this answer your question? [How to check if a row exists in MySQL?](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql) — Dharman, Nov 26 '19 at 18:36

score 29 · Answer 1 · edited Feb 16 '22 at 11:07

29

If you would like to use PDO (PHP Data Object), then use the following code:

$dbh = new PDO("mysql:host=your_host_name;dbname=your_db_name", $user, $pass);
$stmt = $dbh->prepare("SELECT username from my_table where username = ':name'");
$stmt->bindParam(":name", "bob");
$stmt->execute();

if($stmt->rowCount() > 0)
{
    // row exists. do whatever you want to do.
}

edited Feb 16 '22 at 11:07

Dharman

30,962
25
85
135

answered Jul 08 '11 at 04:37

MD Sayem Ahmed

28,628
27
111
178

Have in mind what the PHP documentation says about `rowCount()`: "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.". So check first if your database returns the correct number or not. – PhoneixS Mar 18 '22 at 10:48

rvr_jon · Answer 2 · 2012-03-10T11:41:50.907

Sayem's answer has the most upvotes, but I believe it is incorrect regarding PDO.

From the PHP docs:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.

$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
if ($res = $conn->query($sql)) {

  /* Check the number of rows that match the SELECT statement */
  if ($res->fetchColumn() > 0) {

       /* Issue the real SELECT statement and work with the results */
       $sql = "SELECT name FROM fruit WHERE calories > 100";
       foreach ($conn->query($sql) as $row) {
           print "Name: " .  $row['NAME'] . "\n";
       }
  }
  /* No rows matched -- do something else */
  else {
      print "No rows matched the query.";
  }
}

$res = null;
$conn = null;

score 3 · Answer 3 · answered Jul 08 '11 at 05:19

another approach

$user = "bob";
$user = mysql_real_escape_string($user);
$result = mysql_query("SELECT COUNT(*) AS num_rows FROM my_table WHERE username='{$user}' LIMIT 1;");
$row = mysql_fetch_array($result);
if($row["num_rows"] > 0){
   //user exists
}

check if SQL row exists with PHP

3 Answers3

Linked