4

Some one told me rowCount not safe so I like to ask it here, I have 2 examples and like to know what is the safest and nice way to check if something exists?

$sql = "SELECT count(*) FROM users WHERE username = 'administrator'";
$result = $db->prepare($sql);
$result->execute();
echo $result->fetchColumn() ? 'true' : 'false';

or

$sql = "SELECT username FROM users WHERE username = ?";
$result = $db->prepare($sql);
$result->execute(array('administrator'));
echo $result->rowCount() ? 'true' : 'false';
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • 1
    If you look at the manual there is a note on the `rowcount` function about this exact issue. `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.`-http://php.net/manual/en/pdostatement.rowcount.php (example #2) – chris85 Jun 22 '16 at 04:50
  • @kamalpal getting the number of rows and checking for existence are two different tasks. – Your Common Sense Jun 22 '16 at 07:25
  • @YourCommonSense agreed, but the approach would be same. – kamal pal Jun 22 '16 at 07:38
  • @kamalpal not in the slightest. Not to mention that the answer you linked to suggests you to run 2 queries instead of one, which is a nonsense. – Your Common Sense Jun 22 '16 at 07:45
  • @YourCommonSense The answer I've linked, shows how to get rows count using PDO, which I think also an approach to check existence (saty has used the same approach) and it's done in single query `$nRows = $pdo->query('select count(*) from blah')->fetchColumn();`, However I've noticed this would be just one approach and there could be more, close vote taken back. – kamal pal Jun 22 '16 at 08:44
  • Both suggested dupe answer and Saty's answer explicitly suggest you to run an EXTRA count(*) query **in addition** to a query that selects the actual data. Which makes no sense at all. – Your Common Sense Jun 22 '16 at 09:01
  • 2
    @Theo If an answer resolves your question please remember to accept it, http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work. – chris85 Jun 22 '16 at 12:51
  • 1
    @chris85 even after a year, still nothing huh? – Funk Forty Niner Aug 13 '17 at 02:23
  • @Fred-ii- Guess they're not using links.. :(. – chris85 Aug 14 '17 at 10:49
  • @chris85 *Nope* – Funk Forty Niner Aug 14 '17 at 11:11

3 Answers3

3

The best way to check it with prepare and fetchColumn

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 users WHERE username = ?";// use `COUNT(*)`
$result = $db->prepare($sql);
$result->execute(array('administrator'));
echo $result->fetchColumn() ? 'true' : 'false';
Saty
  • 22,443
  • 7
  • 33
  • 51
0
SELECT 1 FROM users WHERE username = 'administrator' LIMIT 1
SIDU
  • 2,258
  • 1
  • 12
  • 23
0

Using rowCount() isn't unsafe, but just improper.

The #1 rule when working with databases is

Always select the exact data you need.

with as less post-processing as possible.

So if you need to check whatever data for existence, then ask your database to check and then fetch the result.

However, you have to keep in mind that there are 2 possible scenarios:

  1. In case you indeed need to check wherever something exists in a database, but don't need the data, then (assuming username has an unique index on it):

    $sql = "SELECT 1 FROM users WHERE username = ?";
$result = $db->prepare($sql);
$result->execute(array('administrator'));
echo $result->fetchColumn() ? 'true' : 'false';

  2. But often you need the data itself if it happens to be found. In this case you just select that data:

    $sql = "SELECT * FROM users WHERE username = ?";
$result = $db->prepare($sql);
$result->execute(array('administrator'));
$user = $result->fetch();
echo $user ? 'true' : 'false';

I am stressing on it because the wording of the other answer suggests that you have to run 2 queries: one to check the existence and one to get the data, which is a nonsense.

As of the rowCount() method - you need it too seldom to talk about.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345