Check if PDO prepare(), execute() returns at least one row

Question

Before moving to PDO, I used

$result = mysqli_query ($conn, 'SELECT * FROM mytable WHERE id = 54');
if (mysqli_num_rows($result) >= 1) { ... }

to check if the query returns at least one result.

Now with PDO, I've seen in many SO questions (like get number of rows with pdo) that there is no direct function in PDO to check the number of rows of a query (there are warnings about the use of$result->rowCount();), but rather solutions like doing an extra query:

SELECT count(*) FROM mytable WHERE id = 54

But this is maybe too much for what I want : in fact, I don't need the exact number of rows, but just if there is at least one.

How to check if a prepared statement query returns at least one row ?

$stmt = $db->prepare('SELECT * FROM mytable WHERE id = 54');
$stmt.execute();
... // HOW TO CHECK HERE?

What about http://php.net/manual/en/pdostatement.columncount.php — Funk Forty Niner, Mar 30 '15 at 15:22
@Fred-ii- why counting the columns and not the rows? I imagine it would work, but I don't understand the underlying philosophy... — Basj, Mar 30 '15 at 15:24
Couldn't say. PDO can be a strange animal at times when it comes to stuff like that, and I've never completely understood why. — Funk Forty Niner, Mar 30 '15 at 15:29
@Fred-ii- why not http://php.net/manual/en/pdostatement.rowcount.php ? — Basj, Mar 30 '15 at 15:30
@basj because `PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object. ` and yours is a `SELECT` — Hanky Panky, Mar 30 '15 at 15:32
Couldn't say. See the answer below. The manual states it works on certain functions, but others have had success with select, which for the life of me, do not know why it works for some, but not all. — Funk Forty Niner, Mar 30 '15 at 15:32
@Basj It makes sense, if there is no result set, `columnCount()` returns 0 so assuming that you are selecting columns (you always are...), any value higher than 0 will indicate a result set with at least one row. — jeroen, Mar 30 '15 at 15:35
@jeroen yes but why counting the columns and not the rows with `rowCount()` then ? — Basj, Mar 30 '15 at 15:36
@Basj Because the specifications don't dictate that `rowCount()` should return the total number of rows with a `SELECT` statement. Don't know why but that is just the way it is. — jeroen, Mar 30 '15 at 15:38

Hanky Panky · Answer 1 · 2015-03-30T15:37:01.470

3

$stmt = $db->prepare('SELECT * FROM mytable WHERE id = 54');
$stmt.execute();
... // HOW TO CHECK HERE?

It's so simple, you're almost there already.

$stmt = $db->prepare('SELECT * FROM mytable WHERE id = 54');
$stmt.execute();
$result = $stmt->fetchAll();  // Even fetch() will do
if(count($result)>0)
{
      // at least 1 row
}

And if you just want Yes/No answer then you should also add a LIMIT 1 to your query so mysql doesn't waste trying to look for more rows.

edited Mar 30 '15 at 15:37

answered Mar 30 '15 at 15:31

Hanky Panky

46,730
8
72
95

1

Thanks! Will this work as well : `$stmt->execute(); $row = $stmt->fetch(); if ($row) { ... // at least 1 } ` ? – Basj Mar 30 '15 at 15:37
2

@Basj It will, but you will have fetched the first row so you need to keep that in mind if at any point further on you want to loop over your results... – jeroen Mar 30 '15 at 15:39

Check if PDO prepare(), execute() returns at least one row

1 Answers1