mysql with single PDO result

Question

I’m trying to create a query as follows:

I have a table in the database with this structure:

name | address | status

The status field only has 2 types of values 1 or 0. I want to count all records that are status 0

I made the following query:

select count(*) as total from sv_mensagens where status = 0

In PHP I made the following code using PDO:

$this->pdo = ConnDB::conexao();

$query3 = "select count(*) as total from sv_mensagens where status = 0";
$conta2 = $this->pdo->prepare($query3);
$conta2->execute();

while ($result = $conta2->fetchAll()){
    echo $result['total'];
}

only that error occurs and does not work what I want to bring is the result of counting all records that are status in 0.

Answer 1

You are calling the function fetchAll within a loop.

The function fetchAll will return an array with all results.

In your case, you should be returning the following array:

$resultado = array(
  array(
    'total' => 'Algum Numero'
  )
);

Try using the function fetch. Note that you should also pass the type of Fetch you want.

The final code would look like this:

$this->pdo = ConnDB::conexao();

$query3 = "select count(*) as total from sv_mensagens where status = 0";
$conta2 = $this->pdo->prepare($query3);
$conta2->execute();

$result = $conta2->fetchAll(\PDO::FETCH_ASSOC));
echo $result[0]['total'];

Answer 2

Try this:

 select count(status) as total from sv_mensagens where status = 0