This question already has an answer here:
I have a database table timestamps_sessions containing timestamps of when a user begins an exercise on my webpage, and which is only updated when the user actually finishes it. Therefore, every row always has a value in the started column, but not always in the finished column. The latter is NULL by default.
My SELECT COUNT() statement works perfectly when I query it in Sequel Pro, and returns the correct integer of 11. That is to say: there are indeed only eleven rows that have values in both started and finished.
Yet when I execute it in PHP, it returns an object containing
{
current_field: null,
field_count: null,
lengths: null,
num_rows: null,
type: null
}
The statement I successfully query in Sequel Pro is the following:
SELECT COUNT(finished) FROM timestamps_sessions
The statement I unsuccessfully use in PHP is the following:
$sql = "SELECT COUNT(finished) FROM timestamps_sessions";
$result = $conn->query($sql);
$exercise['clears'] = $result;
There are several other SELECT queries being performed to the same database and same table without issue. It's only the COUNT() statement that seems to be malfunctioning.
What am I doing wrongly, and how should I do it instead?
My goal is to count the number of rows with a non-empty finished column, without passing on the actual data in order to preserve bandwidth. All I need is the integer.
</div>
First of all, $result is an object as expected. It's the result returned by the mysqli::query() method. Before you can access the data from this query, you need to fetch it. It will be easier if you give an alias to the count, as it will become easier to access the count.
$sql = "SELECT COUNT(finished) as cnt FROM timestamps_sessions";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$exercise['clears'] = $row['cnt'];
you have missing code mysql_fetch_array in order to fetch first record
$sql = "SELECT COUNT(finished) totals FROM timestamps_sessions";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total = $row['totals'];
$sql = "SELECT COUNT(finished) AS count_finished FROM timestamps_sessions";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo $exercise['clears'] = $row['count_finished'];
Give the count an alias like count_finished. Then from the result object you need to fetch the row. The row has your data in it.
Take a look at this https://www.w3schools.com/php/php_mysql_select.asp
The argument passed to the count function can be anything, since you just want the number of rows, not their data.
SELECT COUNT(1) FROM timestamps_sessions WHERE finished IS NOT NULL;