SELECT COUNT(*) returns an object instead of an integer

Question

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.

Use an alias like `total` in the the SELECT clause and access the first row's `['total']` element. — mickmackusa, Jan 26 '19 at 14:32
If `$conn` is a PDO object, see https://stackoverflow.com/questions/883365/row-count-with-pdo — Martin Heralecký, Jan 26 '19 at 14:36
@MartinHeralecký It's a MySQLi object, as you can see from the properties in the object provided in the question - `num_rows` is a MySQLi result property. — Qirel, Jan 26 '19 at 14:38

score 3 · Answer 1 · answered Jan 26 '19 at 14:38

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

Derviş Kayımbaşıoğlu · Answer 2 · 2019-01-26T14:41:19.430

1

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

edited Jan 26 '19 at 14:41

answered Jan 26 '19 at 14:38

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

You cannot mix MySQL and MySQLi libraries. – Qirel Jan 26 '19 at 14:39
yes I fixed it sorry – Derviş Kayımbaşıoğlu Jan 26 '19 at 14:40
Also, when using the object oriented interface, `fetch_assoc()` is a method called on the object without any parameters. :-) – Qirel Jan 26 '19 at 14:40

v-moe · Answer 3 · 2019-01-26T15:01:06.040

1

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;

edited Jan 26 '19 at 15:01

answered Jan 26 '19 at 14:44

v-moe

1,203
1
8
25

score 0 · Answer 4 · answered Jan 26 '19 at 14:39

$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

SELECT COUNT(*) returns an object instead of an integer

4 Answers4