CODEWITHSUNDEEP
phpmysqlsqlmysqlicount
Lovelock
Lovelock

Reputation: 8095

SELECT COUNT(*) AS count - How to use this count

Instead of doing:

$cars = $mysqli->query("SELECT * FROM cars");
$count = $cars->num_rows();
if ($count) {
  // is rows
}

I want to not have to select all rows or a single column, I simply want the count.

In my head:

$cars = $mysqli->query("SELECT COUNT(*) as count FROM cars");

But then how do I use that count value? How can I get the result from COUNT(*) in PHP?

Upvotes: 2

Views: 40268

Answers (5)

Dharman
Dharman

Reputation: 33402

You have many options available to get the count from SQL.

If you are already on PHP 8.1, the simplest is this:

$count = $mysqli->query("SELECT COUNT(*) FROM cars")->fetch_column();

Here is a list of other possible methods:

  • Using associative array:
$count = $mysqli->query("SELECT COUNT(*) as count FROM cars")->fetch_array()['count'];
  • Using numerical array:
$count = $mysqli->query("SELECT COUNT(*) FROM cars")->fetch_array()[0];
  • Using an object notation
$count = $mysqli->query("SELECT COUNT(*) as count FROM cars")->fetch_object()->count;

Important point. If you need to use variable input in your query then you should use prepared statement. To get the count then it would look like this:

$stmt = $mysqli->prepare("SELECT COUNT(*) FROM cars WHERE category=?");
$stmt->bind_param('s', $category);
$stmt->execute();
$count = $stmt->get_result()->fetch_row()[0];

Upvotes: 2

Shreejibawa
Shreejibawa

Reputation: 1868

If result is an object then:

$cars = $mysqli->query("SELECT count(*) as count FROM cars");
$carRows = $cars->result();
echo $carRows[0]->count;

If you return array then:

$cars = $mysqli->query("SELECT count(*) as count FROM cars");
$carRows = $cars->result_array();
echo $carRows[0]['count'];

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

It's not recommended to use reserved words for names in SQL. So I call the count result cnt instead. As your function is scalar, i.e. you expect only one value back, you can use:

$count = $mysqli->query("select count(*) as cnt from cars")->fetch_object()->cnt;

Upvotes: 8

Tomas M
Tomas M

Reputation: 7353

You need to get first row of your result and see the 'count' column value:

$cars = $mysqli->query("SELECT COUNT(*) as count FROM cars");
$firstrow = $cars->fetch_assoc();

if ($firstrow['count'] > 0)
{
  // ...
}

Upvotes: 1

Ataboy Josef
Ataboy Josef

Reputation: 2101

Select queries always return a resultset. To take the count, use fetch_row()

$result = $db->query("SELECT COUNT(*) FROM `cars`");
$cars= $result->fetch_row();
echo '#: ', $cars[0];// or use $c= $cars[0];

Upvotes: 1

Related Questions