Error trying rowCount in PHP class

Question

I’m trying to make a rowCount using a class I developed for the connection and some functions involving the bd.

I have the following PHP class:

class Banco {
    private $debug;
    private static $pdo;

    function __construct($debug = true, $banco = "BANCO", $usuario = "USUARIO", $senha = "SENHA") {
        $this->debug = $debug;

        try {
            $this->pdo = new PDO("mysql:host=HOST;dbname=".$banco, $usuario, $senha);

            if($debug) {
                $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }

            $this->pdo->query("SET NAMES 'utf8'");
            $this->pdo->query("SET character_set_connection=utf8");
            $this->pdo->query("SET character_set_client=utf8");
            $this->pdo->query("SET character_set_results=utf8");
        } catch (PDOException $e) {
            if($this->debug) {
                echo "Ocorreu um erro de conexão: " .$e->getMessage();
            }
        }
    }

    public function consultar($tabela, $colunas, $condicao, $agrupamento, $ordenacao, $limite) {
        try {
            if(is_array($colunas)) {
                $colunas = implode(", ", $colunas);
            }

            $sql = "SELECT " .$colunas. " FROM " .$tabela;

            if($condicao != false) {
                $sql .= " WHERE " .$condicao;
            }

            if($agrupamento != false) {
                $sql .= " GROUP BY " .$agrupamento;
            }

            if($ordenacao != false) {
                $sql .= " ORDER BY " .$ordenacao;
            }

            if($limite != false) {
                $sql .= " LIMIT " .$limite;
            }

            return $this->pdo->query($sql);
        } catch (PDOException $e) {
            echo "Ocorreu um erro: " .$e->getMessage();
        }
    }

    public function contaLinha($sql) {
        try {
            $var = $this->pdo->query($sql);

            return $this->pdo->rowCount($var);
        } catch (PDOException $e) {
            echo "Ocorreu um erro: " .$e->getMessage();
        }
    }
}

and another file, also in PHP, that needs this rowCount:

$bd = new Banco();

$colunas = array("nome", "email", "teste", "codigo", "estatus");

$sql = $bd -> consultar("TABELA", "$colunas", "codigo = '$getT' AND estatus = 0", false, false, 1);

if($bd -> contaLinha($sql)) {
    echo "Contou!";
} else {
    echo "Retornou 0!";
}

However, when trying to execute such codes, the following error is returned:

Error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Array' in 'field list'

Error occurred: SQLSTATE[42000]: Syntax error or access Violation: 1065 Query was Empty

And since I’m a beginner in PHPOO, some questions have arisen:

1. I’ve tried using the function consultar() without array to test but not many changes have occurred. I believe the error is not in the class. The I might be doing wrong?
2. Should I use private or public in function __construct() or leave as is?

Thank you!

Answer 1

First, from what I read in documentation rowCount function does not receive parameter.

"Returns the number of lines affected by the last DELETE, INSERT or UPDATE executed by the corresponding Pdostatement object.

If the last SQL statement executed by the associated Pdostatement was a SELECT statement, some databases may return the number of lines returned by this statement. However, this behaviour is not guaranteed for all databases and should not be invoked for portable applications."

Second, note the return of consultar :

return $this->pdo->query($sql);

and now look at the first function you call on the line account

$var = $this->pdo->query($sql);

now check the order of the calls you make;

$sql = $bd -> consultar("TABELA", "$colunas", "codigo = '$getT' AND estatus = 0", false, false, 1);

//o contaLinha está esperando uma String pois dentro dele vc chama a função $this->pdo->query($sql)
if($bd -> contaLinha($sql)) 

Summing up what happens is this $this->pdo->query($this->pdo->query($sql));

A suggestion for you is that there is a native php function that counts how many elements an array has, the function is called Count

Solution

class Banco{

    //removi o static  
    private $pdo;

    public function consultar($tabela, $colunas, $condicao, $agrupamento, $ordenacao, $limite) {
        try {
            if(is_array($colunas)) {
                $colunas = implode(", ", $colunas);
            }else{//caso nao for array, vc pode tratar de outra forma
                $colunas =" * ";
            }

            $sql = "SELECT " .$colunas. " FROM " .$tabela;

            if($condicao != false) {
                $sql .= " WHERE " .$condicao;
            }

            if($agrupamento != false) {
                $sql .= " GROUP BY " .$agrupamento;
            }

            if($ordenacao != false) {
                $sql .= " ORDER BY " .$ordenacao;
            }

            if($limite != false) {
                $sql .= " LIMIT " .$limite;
            }

            //modifiquei o retorno 
            $result = $this->pdo->query($sql);
            return $result->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            echo "Ocorreu um erro: " .$e->getMessage();
        }
    }

The other file that calls the functions of the bank.

$bd = new Banco();

$colunas = array("nome", "email", "teste", "codigo", "estatus");

//aqui eu tirei as aspas da variavel $coluna pois estava dando erro
$result = $bd->consultar("TABELA", $colunas, "codigo = '$getT' AND estatus = 0", false, false, 1);

//verifico se a quantidade é maior que zero
if(count($result)>0){
    echo "Contou!";
} else {
    echo "Retornou 0!";
}

//se quiser navegar nos registros faça assim
if(count($result)>0){
    foreach ($result as $value) {
        echo $value['nome'];
        echo $value['email'];
        //code...
    }
}

Its function conta_line can thus be independent of the result of another function. It only returns the amount of record that exists according to the condition passed.

public function conta_linha($tabela, $condicao) {
    try {

        $sql = "SELECT count(id) as cont FROM " .$tabela;

        if($condicao != false) {
            $sql .= " WHERE " .$condicao;
        }

        //modifiquei o retorno 
        $result = $this->pdo->query($sql);
        return $result->fetch(PDO::FETCH_ASSOC);

    } catch (PDOException $e) {
        echo "Ocorreu um erro: " .$e->getMessage();
    }
}