BD Results Count for pagination

Question

I’m having a problem, I want to know how much data there is in the database, but with this code is returning -1 I have already used this code for other jobs (only with mysql), in this case it is with SQL Server

$pag = (isset($_GET['pag'])) ? strip_tags((int)$_GET['pag']) : '1';

                                $maximo = '6';//produtos por pagina

                                $stmt2 = $conn->prepare("SELECT * FROM cl");
                                $stmt2->execute();
                                $totalRegistros2 = $stmt2->rowCount();
                                $paginas2 = ceil($totalRegistros2/$maximo);

                                if(isset($_GET['pag'])){
                                    if (($_GET['pag']>$paginas2)||($_GET['pag']<1)){
                                        $pag=1;
                                    }
                                }

                                $inicio = ($pag * $maximo) - $maximo;
                        echo $totalRegistros2;
                        echo $count;

Answer 1

From the PHP documentation regarding the function rowCount()

If the last SQL statement executed by the Associated Pdostatement was a SELECT statement, some Databases may Return the number of Rows returned by that statement. However, this behaviour is not Guaranteed for all Databases and should not be relied on for Portable Applications.

If the last instruction is a SELECT it is not guaranteed that rowCount() returns the number of records. It depends on the DBMS.

If you want to know the total number of records you can always do

$sql = "SELECT count(*) AS total_records FROM cl";
$rs_result = $db_connection->query($sql);
$total_records = $rs_result->fetch_assoc()['total_records'];

Answer 2

You can use SELECT count(*) FROM cl to know how many rows/records this table has.

Answer 3

It’s right here with PDO

$stmt2 = $conn->prepare("SELECT count(*) AS total_records FROM cl");
$stmt2->execute(); 
$total_records = $stmt2->fetch(PDO::FETCH_ASSOC)['total_records'];