0

I would like to count all rows in all tables, within a MySQL database. I am aware of the InnoDB issue. I would also like to point out that the connection to the database is perfectly fine, I am simply omitting that code as I deem it irrelevant to my issue.

Here is my code:

$sql = mysqli_query($c, "SELECT SUM(TABLE_ROWS)
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_SCHEMA = 'cpus_amd'");

I'm then echoing the figure using PHP echo like so:

echo "<p>This database contains". $sql ."processors.</p>";

And I'm receiving the following error in an error_log file:

PHP Catchable fatal error:  Object of class mysqli_result could not be converted to
string in <file> on <line>

I have read through the following questions, but I'm still confused and haven't directly found a solution:

Thank you for any help.

Dharman
  • 30,962
  • 25
  • 85
  • 135
DylRicho
  • 895
  • 1
  • 10
  • 25
  • 1
    `$sql` is a __resultset__.... you need to `fetch` the `record` from that `resultset`.... [PHP and MySQLi 101](http://php.net/manual/en/class.mysqli-result.php) – Mark Baker Aug 14 '15 at 09:08
  • If you want to echo the number of rows that the query has found use `mysqli_num_rows($sql)` – Hearner Aug 14 '15 at 09:16
  • @MarkBaker Hi Mark, thank you for the insight. I'm still new to SQL so I'm not too sure what I should do in terms of code. Which one is the correct one? Thank you. – DylRicho Aug 14 '15 at 09:31

2 Answers2

0

You could add something like this to your code :

$count = mysqli_num_rows($sql);

where $count is the number of rows returned by the request you made.

Then you don't neet to make a sum anymore. You could also keep the SUM and write : 

$row = mysqli_fetch_assoc($sql);

Then, assuming you introduced in your request "myrows" (SELECT SUM(TABLE_ROWS) myrows...), you could retrieve the result as follows :

$result = $row['myrows']
Fafanellu
  • 424
  • 4
  • 20
  • This appears to be what I was using before. I was trying to recreate the situation to post here, but I couldn't remember what I did (I'm still new to SQL). Nevertheless, `$count = mysqli_num_rows($sql);` is telling me that there is just 1 model, when I've run the code directly inside phpMyAdmin, and been told there are 48 models with `SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpus_amd'`. – DylRicho Aug 14 '15 at 09:28
  • 1
    if your SQL request is made to return a single value, then mysqli_num_rows will return a single row (containing the value 48 for instance). That is why you should either : modify your request without SUM() (then it will return several rows that you'll be able to count) or keep SUM(), give, in the request, a name to the field you will retrieve ("myrows"), use mysqli_fetch_assoc(), and retrieve $row['myrows'], as I shown above – Fafanellu Aug 14 '15 at 09:33
  • I'm now getting 48. Awesome! Thank you so much. I shall edit my question with the new code for others to see, and accept your answer. – DylRicho Aug 14 '15 at 09:40
-1

You can't print or echo a query, only string. Change your query like :

$sql = mysqli_query($c, "SELECT SUM(TABLE_ROWS) AS SumTableRow
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_SCHEMA = 'cpus_amd'");

(SumTableRow) is the result of SUM(TABLE_ROWS)

$sql is a query, and it can't be echoed. The result is :

$sqlResult = $sql['SumTableRow'];

This is the result you can echo. like :

echo "<p>This database contains".  $sqlResult ."processors.</p>";

Or you can do it in one query :

echo "<p>This database contains". $sql['SumTableRow'] ."processors.</p>";
Hearner
  • 2,711
  • 3
  • 17
  • 34
  • `$sql['SumTableRow'];` is giving me the following error: `Cannot use object of type mysqli_result as array` – DylRicho Aug 14 '15 at 09:22