PHP PDO counting all table rows in Database

Question

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.

What does it do when you run it? That query is fine. How do you execute it? — Hanky Panky
– Hanky Panky, Commented Apr 21, 2016 at 3:50
Are the credentials you use in phpmyadmin the same as for php? You may be using incorrect permissions — user5051310
– user5051310, Commented Apr 21, 2016 at 3:51
@Hanky yeah totally fine. I execute it as it is <?php $stmt = $conn->query('SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = sample '); $row_count = $stmt->rowCount(); echo $row_count; ?> — RumRum
– RumRum, Commented Apr 21, 2016 at 3:53
rowCount() ? no this will return only one row. So you have to fetch the result and see what SUM(TABLE_ROWS) contains — Hanky Panky
– Hanky Panky, Commented Apr 21, 2016 at 3:54

cn0047 · Accepted Answer · 2016-04-21 05:47:53Z

3

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

edited Apr 21, 2016 at 5:47

answered Apr 21, 2016 at 5:42

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RumRum Over a year ago

i got it alreay haha. I almost having 4-5 hours of finding an answer to this matter. so our time is already 1:55pm PHIL time. anyways fetchAll is kinda for array therefore we'll be using foreach($result[0] as $child) { echo $child . "\n"; } to output in a nice way. **output now will be:** 125 Thanks Vladimir. and all the guys there. :)

cn0047 Over a year ago

You also can use $result = $sth->fetchColumn();.

Your Common Sense Over a year ago

You may add a note that this code would work only when emulation mode is turned on

Collectives™ on Stack Overflow

PHP PDO counting all table rows in Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related