Error when trying to rowCount in class with PHP

Question

Error when trying to rowCount in class with PHP

Navigation

#1 by (1 votes)

0

I'm trying to make a rowCount using a class I myself developed for the connection and some functions involving 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 attempting to execute such codes, the following error is returned:

An error occurred: SQLSTATE [42S22]: Column not found: 1054 Unknown column 'Array' in 'field list'
An error occurred: SQLSTATE [42000]: Syntax error or access violation: 1065 Query was empty

And since I'm a beginner in PHPOO, some doubts have arisen:

I've tried to use the consultar() function with no array to test but not many changes have taken place. I believe the error is not in the class. O what can I be doing wrong?

I should use private or public in function __construct() or leave it as it is?

Thanks!

php pdo

asked by anonymous 18.04.2015 / 05:05

1 answer

Like Share Image and text Query ordering - sql server

score 1 · Accepted Answer

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

"Returns the number of rows 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   behavior is not guaranteed for all databases and should not   be invoked for portable applications. "

Second, notice the return of consultar :

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

Now look at the first function that you call in 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))

In short, what happens is this $this->pdo->query($this->pdo->query($sql)) ;

One suggestion for you is that there is a native php function that tells 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 bank functions.

$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 count_line can be thus, regardless of the result of another function. It only returns the record quantity that exists according to the past condition.

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();
    }
}