How to Check If a Row Exist in the Database Using Pdo

How to check if a row exist in the database using PDO?

You can just check the return value directly.

$stmt = $conn->prepare('SELECT * FROM table WHERE ID=?');
$stmt->bindParam(1, $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if( ! $row)
{
    echo 'nothing found';
}

/*
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // Same here
if( ! $rows)
{
    echo 'nothing found';
}
*/

If you are asking about checking without fetching then simply have MySQL return a 1 (or use the COUNT() command).

$sql = 'SELECT 1 from table WHERE id = ? LIMIT 1';
//$sql = 'SELECT COUNT(*) from table WHERE param = ?'; // for checking >1 records
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $_GET['id'], PDO::PARAM_INT);
$stmt->execute();

if($stmt->fetchColumn()) echo 'found';

check if record exists (PDO)

A few things. MySQL does not use the == equality operator, instead you should just use =. In addition, since you're using PDO, it might be better to set up Prepared Statements.

Finally, since you use COUNT(*), your query will always return 1 record. You need to update your code as follows:

$sql = $dbh->prepare("SELECT COUNT(*) AS `total` FROM directory WHERE telephone = :phone");
$sql->execute(array(':phone' => $telephone));
$result = $sql->fetchObject();

if ($result->total > 0) 
{
    echo 'The telephone number: ' . $telephone. ' is already in the database<br />';
}
else 
{
      echo 'No rows matched the query.';
}

It's probably worth noting too that since you're receiving $telephone direct from the $_GET super-global, you shouldn't really output it unsanitized to the browser (for reasons of XSS vulnerabilities). I'd recommend updating your first echo statement as follows:

echo 'The telephone number: ' . strip_tags($telephone). ' is already in the database<br />';

MYSQL, PHP, PDO Check if row exists

You are binding the exact string '$steamId' to the id so that it will search for an user with the id of '$steamId'.

$stmt->bindParam(":id", $steamId, PDO::PARAM_STR);

Also you can remove the quotes from the select statement.

$stmt = $conn->prepare("SELECT Steam_id from Account_info WHERE Steam_id = :id");

How to check if rows exist PDO PHP?

Below example for First Question, you can try to solve your problem certain ways below is example of it. you can read more about pdo from here

$query=$dbh->prepare("SELECT secret FROM users WHERE username=:param");
$query->bindParam(':param', $param);
$query->execute();
$result = $query -> fetch();
print_r($result);

For second issue

echo $system_default['lockerURL'];

and remove echo $system_default[ 0 ]->lockerURL; line

PHP PDO check if data exists not working properly

Your code could be much simpler

Just populate your $row variable and see whether it contains anything or not.

<?php
$stmt = $conn->prepare("SELECT * FROM `posts` WHERE `id` = ?");
$stmt->execute([$_GET["id"]]); // essential to protect from SQL injection!
$row = $stmt->fetch(PDO::FETCH_ASSOC)
if ($row) {
        echo '<div class="post-preview">
                <a>
                    <h2 class="post-title">
                        '.$row["title"].'
                    </h2>
                    <h3 class="post-subtitle">
                        '.$row["content"].'
                    </h3>
                </a>
                <p class="post-meta"><a href="#">'.$row["creator"].'</a> | '.$row["date"].'</p>
            </div>
            <hr>';
    }
    else {
        echo "<center><h1>Post does not exist.</h1></center>";
        header("Refresh: 2; url=index.php");
    }

Check if a record exists in an array using PDO and Mysql

Problem is in Your code logic and sql query.

Why to take array of unnecessary rows and do lookup in it?

Also You're repeating DATE condition with same value multiple times

You can just define condition for GUESTCHECK inside Your query, then check result count and do fetchAll (if there's a need on data).

Example with rows data:

  $query = "
    SELECT * 
    FROM `items` 
    WHERE 
     GUESTCHECK = 10017 
      AND
     CODE IN (771012, 782012, 774012, 775203, 775202) 
      AND
     DATE = '1/1/2018'
  ";

  $statement = $conn->prepare($query);
  $statement->execute();

  if ($statement->rowCount() > 0)
  {
    echo "Match found";

    $items = $statement->fetchAll();
    var_dump($items);
  }
  else
  {
    echo "Match not found";
  }

Example without getting rows from db:

  $query = "
    SELECT 1
    FROM `items` 
    WHERE 
     GUESTCHECK = 10017 
      AND
     CODE IN (771012, 782012, 774012, 775203, 775202) 
      AND
     DATE = '1/1/2018'
    LIMIT 1
  ";

  $statement = $conn->prepare($query);
  $statement->execute();

  if ($statement->rowCount() > 0)
  {
    echo "Match found";
  }
  else
  {
    echo "Match not found";
  }

p.s. make sure You've added index to GUESTCHECK and CODE fields to help DB engine to search for data effectively.

Check if row exists in the database before inserting

It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.

Just set a UNIQUE constraint on imdbid:

ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);

The reason for doing this is so that you don't run into a race condition.

There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.

Solution? Use constraints and check $DBH->error() for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.

I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. In this case, you don't need to check ->error() because PDO will throw an exception. Just wrap your execute with try and catch like this:

$duplicate = false;

try {
    $STH->execute();
} catch (Exception $e) {
    echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
    $duplicate = true;
}

if (!$duplicate)
    echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";

Check if row exists, The most efficient way?

By best I guess you mean consuming the least resources on both MySQL server and client.

That is this:

      SELECT COUNT(*) count FROM table WHERE id=1

You get a one-row, one-column result set. If that column is zero, the row was not found. If the column is one, a row was found. If the column is greater that one, multiple rows were found.

This is a good solution for a few reasons.

1. COUNT(*) is decently efficient, especially if id is indexed.
2. It has a simple code path in your client software, because it always returns just one row. You don't have to sweat edge cases like no rows or multiple rows.
3. The SQL is as clear as it can be about what you're trying to do. That's helpful to the next person to work on your code.

Adding LIMIT 1 will do nothing if added to this query. It is already a one-row result set, inherently. You can add it, but then you'll make the next person looking at your code wonder what you were trying to do, and wonder whether you made some kind of mistake.

COUNT(*) counts all rows that match the WHERE statement. COUNT(id) is slightly slower because it counts all rows unless their id values are null. It has to make that check. For that reason, people usually use COUNT(*) unless there's some chance they want to ignore null values. If you put COUNT(id) in your code, the next person to work on it will have to spend some time figuring out whether you meant anything special by counting id rather than *.

You can use either; they give the same result.

---

Related Topics