PHP PDO rowCount not working? I think

Question

So I am grabbing the amount of rows in a specific table where the username is already in the database like so:

$second_sql = $db->prepare("SELECT * FROM users WHERE username = :username");    
$second_sql->bindParam(':username', $username);    
$second_sql->execute();

if($second_sql->rowCount() == 1) {
  $db = null;
  header("Location: ../login/");
} else {
  $statement->execute();
  $db = null;
}

The problem is it's not working. If you need more of the script just tell me.

Answer 1

Some databases does not report the row count with PDO->rowCount() method.

SQLite, for instance.

So don't use rowCount(); doing so makes your code less portable.

Instead use the COUNT(*) function in your query, and store the result in a variable.

Finally, use that variable to fetch the one and only column (users) using the fetchColumn() method.

So you can play with this:

try {
    $second_sql = $db->prepare("SELECT COUNT(*) from users WHERE username = :username");
    $second_sql->bindParam(':username', $username, PDO::PARAM_STR);
    $second_sql->execute();
    $count = $second_sql->fetchColumn();
} catch (PDOException $e) {
    // Here you can log your error
    // or send an email
    // Never echo this exception on production
    // Only on development fase
    echo "Error: " . $e->getMessage();
}

if ($count) {
    $db = null;
    header("Location: ../login/");
} else {
    $statement->execute();
    $db = null;
}

Perhaps you wanna test you condition for a single row:

if ($count == 1)

Hope this helps you.

Cheers!

Answer 2

Short answer: I wanted this idea to be as short as 1 line of code:

$stmt = $PDO->query( "SELECT * FROM my_table" ); 
$rows = (int) $PDO->query('SELECT FOUND_ROWS()')->fetchColumn();

• use SELECT FOUND_ROWS(), then fetchColumn(), then class it as an (int)

---

TLDR Version:

Since PHP8 I have just been using rowCount() with impunity, no problems. Then when working on a stack of WP updates across other peoples servers I find rowCount() failing to return the proper number of rows

Of course, I google it and find a decades long debate. I did not like most answers, so I read the documentation https://www.php.net/manual/en/pdostatement.rowcount.php

According to docs DELETE, INSERT, or UPDATE work with rowCount(), but even on the PHP.NET examples page as far back as 11 years ago folks have been wrestling with the best/easiest way to just count the result.

After testing about 20 answers (stackOverflow to Php.net) the final script [below] worked best to patch 32 WP sites across 20 different servers, 5 hosts and several versions of PHP ranging from 7.4 to 8.1 (GoDaddy, Bluehost, WPengine, InMotion, Amazon AWS Lightsail)

Note that $opts turned out to be important (shared, cloud, dedicated)

    $db_host = "localhost"; //DB_HOST in WP
    $db_name = "yours";     //DB_NAME in WP
    $db_charset = "utf8mb4";//usually
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=$db_charset";
    $opt = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        PDO::MYSQL_ATTR_LOCAL_INFILE => true, 
        // on/off based on your need: https://www.php.net/manual/en/ref.pdo-mysql.php
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
        //makes sure addons like counting rows is enabled
    ];
   $PDO = new PDO($dsn, $db_user, $db_password, $opt);
   
   // Run the Jewels - do your stuff here
   $stmt = $PDO->query( "SELECT * FROM wp_posts WHERE post_status='publish' " );

   // one-line count, no shenanagans
   $rows = (int) $PDO->query('SELECT FOUND_ROWS()')->fetchColumn();

   echo "tell me something useful: $rows";