Counting rows of multiple tables and returning result individually?

Question

I'm trying to count the number of rows in several tables and echo the results individually. I've tried using the below script and it returns the original value of $table1count, t1c. It never returns the actual count value. I think the syntax is wrong but all of the info I've found online is 8-10 years old. Could anyone help?

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbName = "database";

$table1count = 't1c';
$table2count = 't2c';

$conn = new mysqli ($servername, $username, $password, $dbName);

if(!$conn){
    die("Connection failed. ". mysqli_connect_error());
}

$sql  = "SELECT ";
$sql .= "(SELECT COUNT(*) FROM table1) AS $table1count, ";
$sql .= "(SELECT COUNT(*) FROM table2) AS $table2count; ";

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

if(mysqli_num_rows($result) > 0) {

    while($row = mysqli_fetch_assoc($result)){
        echo "table1count:" . $table1count . "|table2count:". $table2count . ";";

    }
}
?>

It looks like those variables in the SQL string (`$table1count` & `$table2count`) are undefined. After fixing that, in the echo, it should be `$row[$table1count]` and `$row[$table2count]` to access the values in the array. — Don't Panic, Oct 23 '18 at 17:31
It will help you debug this if you can enable error reporting and show errors from mysqli. https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments — Don't Panic, Oct 23 '18 at 17:37
@Don'tPanic Thank you, that mysqli_report code will help me so much in the future. I added $table1count and $table2count to the beginning of my script, and the error report says there is an undefined variable $table1count. I thought maybe I need to get rid of the $ on that line after declaring it, but then it says fatal error/error in sql syntax near line 1. — Jessica, Oct 23 '18 at 17:50
You defined them, like `$table1count='something'; `? They shouldn't be undefined if you defined them. (Just putting `$table1count;` doesn't define them.) Be sure to assign values that are valid MySQL identifiers. — Don't Panic, Oct 23 '18 at 17:52
It looks like from the error it's around the "SELECT " "(SELECT area. Is that the wrong syntax to be using? — Jessica, Oct 23 '18 at 17:54
@Don'tPanic No, I just did $table1count; I'm sorry I'm new to sql, I'm used to C#. How am I supposed to define it before returning the value? I'm editing my original post to show what the code looks like now. — Jessica, Oct 23 '18 at 17:55
It looks like you're new to SO, so a bit of advice - you should avoid changing the code in the question based on advice from the answer and/or comments. That ends up with an answer that addresses problems that the code in the question no longer has, so it won't make much sense to someone reading it later. I know that makes it a bit more difficult to show the current state if there's a little back and forth like we had here. If you need to do that, you can use an online source like 3v4l.org to store the altered code and link to that in a comment. — Don't Panic, Oct 23 '18 at 18:17
I'll do that in the future, thank you. I figured I shouldn't change the original code for the sake of people who may read it in the future, but like you said I wasn't sure how I could describe the changes I made without that. I'll use that site next time. You've been such a great help I appreciate it so much. Thank you again and have a great day. — Jessica, Oct 23 '18 at 18:36

score 0 · Accepted Answer · answered Oct 23 '18 at 18:05

$table1count; and $table2count; need to have values before they can be used in the $sql string. It doesn't really matter what the values are, as long as they are valid MySQL identifiers.

$table1count = 't1c';
$table2count = 't2c';

I just used assigned a string similar to the variable name because I'm not creative.

Those strings will be passed to MySQL with the query where they're used as aliases for the results of the count queries. The aliases will be returned with the query results, so they will be keys in the $row array returned by $row = mysqli_fetch_assoc($result).

You can access the count values in $row using those array keys in your echo statement.

echo "table1count:" . $row[$table1count] . "|table2count:". $row[$table2count] . ";";

Also, be sure to remove that trailing comma from the SQL string, it will cause an SQL syntax error.

Counting rows of multiple tables and returning result individually?

1 Answers1