36

I want to have a condition that will perform some action when the row doesn't exist at all.

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

Tried if (count($row) == 0) and if($stmt->rowCount() < 0) but none of them works.

Dharman
  • 30,962
  • 25
  • 85
  • 135
xperator
  • 2,743
  • 7
  • 34
  • 58
  • 1
    [`$stmt->rowCount()`](http://php.net/manual/en/pdostatement.rowcount.php) should be what you need, but checking if it is *less than* `0` won't help - it will be *equal to* `0` or *less than* `1` – DaveRandom Aug 15 '12 at 18:05
  • 2
    @DaveRandom The docs state that not all drivers cause `SELECT` to give a `rowCount`. Apparently you should actually be using `columnCount`. – Waleed Khan Aug 15 '12 at 18:07
  • 1
    @arxanas A fair point, but frankly running two queries just to get the row count (as the manual suggests) is a sucky solution. Even `$rowCount = count($rows = $stmt->fetchAll())` is a better solution than that, I feel - and it will still result in `0` if there are no rows. – DaveRandom Aug 15 '12 at 18:13

3 Answers3

87

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';
Dharman
  • 30,962
  • 25
  • 85
  • 135
Xeoncross
  • 55,620
  • 80
  • 262
  • 364
  • 10
    Your solution will work, but it is not true that `PDOStatement::fetch` returns an array; it returns the next result or `false` if there are no rows anymore. And that's why `$row` is false when there is an empty result set. – matthias.p Aug 15 '12 at 18:10
  • @matthias.p, thanks for the correction. I never use `fetch()` so my statement about an empty array was from `fetchAll()` – Xeoncross Aug 15 '12 at 18:12
  • Thanks! that's what i was looking for. Since I knew the result was return in an array, I was thinking it could be the best way to check the variable instead of using a function or doing another sql query. – xperator Aug 15 '12 at 19:21
  • Can I use `rowCount()` instead of `fetchColumn()` in the `if` condition? – Shafizadeh Sep 28 '15 at 12:52
  • @Sajad the [manual says no](http://php.net/manual/en/pdostatement.rowcount.php): "Returns the number of rows **affected** by the last SQL statement" – Xeoncross Sep 29 '15 at 15:28
12
if($stmt->rowCount() == 0)

should work fine, since the number of rows can't be less than zero in any event at all.

From the manual:

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. Your application can then perform the correct action.

I would suggest reading up on that here.

Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
DannyCruzeira
  • 564
  • 1
  • 6
  • 19
0

Heres what I use in my object classes:

function exists_by_id () {
    // check if object exists by id
    $stm = DB::$pdo->prepare('select count(*) from `table` where `column`=:column');
    $stm->bindParam(':column', $this->column);
    $stm->execute();
    $res = $stm->fetchColumn();

    if ($res > 0) {
        return true;
    }
    else {
        return false;
    }
}
kjdion84
  • 9,552
  • 8
  • 60
  • 87