php Obtaining the number of rows in all database tables

You want to get the number of all rows in the database. Right now

 $sql = "SELECT table_name , table_rows FROM INFROMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = 'Table Name';

 $res = mysqli_query($conn, $sql);

 echo json_encode(array("RES" => $res));

I'm using this code

RES":{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type",null};

This code is returned.

+------------------+-----------------+
| | table_name       | exact_row_count |
+------------------+-----------------+
| | func             |               0 |
| | general_log      |               0 |
| | help_category    |              37 |
| | help_keyword     |             450 |
| | help_relation    |             990 |
| | help_topic       |             504 |
| | host             |               0 |
| | ndb_binlog_index |               0 |
+------------------+-----------------+

Is there a way to get the result value in json like this?

php mysql

2022-09-21 20:26

1 Answers

In fact, the result set you want is It looks like you can get it with just one pure MySQL syntax query, but there are many unfamiliar things like GROUP_CONCAT() or INTO @sql... It's got a PHP tag on it. Let's try PHP.

The key idea, as well as the reference information linked above, is to do this in two stages.

$rows_per_tables = [];

$sql1 = 'SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = :schema
    AND table_type = :type';
$query1 = $pdo->prepare($sql1);
$query1->execute([
    'schema' => DB schema name,
    'type' => 'BASE_TABLE'
]);

while ($row = $query1->fetch()) {
    $table = $row['table_name'];
    $sql2 = 'SELECT COUNT(*) as rows FROM :table';
    $query2 = $pdo->prepare($sql2);
    $query2->execute(compact('table'));
    $count = $query2->fetch();
    $exact_row_count = $count['rows'];
    $rows_per_tables[] = compact('table', 'exact_row_count');
}

echo json_encode(['RES' => $rows_per_tables]);

2022-09-21 20:26

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656