Jerum Labsan Jerum Labsan - 7 months ago 18
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

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']]
Comments