Jerum Labsan Jerum Labsan - 1 year ago 76
SQL Question

PHP PDO counting all table rows in Database

This MYSQL statement is functioning in MYSQL.

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sample'


But when I put into my PHP code it does not funtion. What am I gonna do? The image below encircled with red is what I want to output in my page. Thank you.

enter image description here

Answer Source

Your code looks fine, but in comment you wrote that where clause in your query looks like:
WHERE TABLE_SCHEMA = sample, but it's incorrect because sample must be passed like 'string'.

Here my code, and it works:

<?php

$dbh = new PDO('mysql:host=127.0.0.1;dbname=myDB', 'myUser', 'myPass');
$sth = $dbh->prepare("
    SELECT SUM(TABLE_ROWS) AS totalRowsCount
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'myDB'
");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
var_export($result);

and as result will be:

[0 => ['totalRowsCount' => '125']]