How to show the number records in PDO using COUNT( * )?

Asked

Viewed 528 times

1

inserir a descrição da imagem aqui

Hello I’m doing a system that lists in the database the latest updates , even it’s all right , only I wanted to print in the table the number of total updates that the user did . How can I do that ? Thanks in advance .

Type more or less using SELECT COUNT( * )

That’s the code that makes the Foreach

<?php
        $data           = $_POST["data"];
        $codusuario     = $_POST["codusuario"];
         $cRelatorios = new cRelatorios();
          $relatorio = $cRelatorios->RetornaAtualizacoesFuncionarios($data,$codusuario);
            //echo "<pre>"; print_r($relatorio);


            if(!empty($relatorio)){

                     Foreach($relatorio as  $value){
                      // echo "<pre>"; print_r($value); exit;
                        echo "<tr>";
                        echo "<td><center>". $value["usuario"] ."</center></td>";
                        echo "<td><center>". $value["data"] ."</center></td>";
                        echo "<td><center>". $value["codigo"] ."</center></td>";
                        echo "<td>". $value["nome"] ."</td>";
                        echo "<td>". $value[""] ."</td>";
                        echo count($relatorio);//Quero que imprima só uma vez dentro do <td>
                        echo "</tr>";

                                   }
    }

              ?>

And this is part of my PDO Query

public function RetornaAtualizacoesFuncionarios($data,$codusuario){ 
        $WHERE = array() ;
       if( !empty( $codusuario    ) ) {$WHERE[] = "codusuario = $codusuario";};
       if( !empty( $data   ) ) {$WHERE[] = "DATE_FORMAT(data,'%Y-%m') = '$data'";};
       $WHERE[] = "tipregistro = 'mysql'ORDER BY funcionarios.data DESC";
    try {

         $Query = "SELECT
                       COUNT(usuario) as qtd, 
                        DISTINCT(funcionarios.usuario),
                        date_format(funcionarios.data,'%d/%m/%Y %H:%i:%s') as data ,
                        codigo,
                        nome 
                            FROM funcionarios
                                      "; 

        if( !empty($WHERE) )$Query .= ' WHERE '.implode(' AND  ', $WHERE );

        // echo "<pre>"; print_r($Query);
         include_once $_SESSION['pmodel'].'/mysqlconnection_class.php';
               $p_sql = MysqlConnection::getInstance()->prepare($Query);

                $_retorno = array(); 
                if($p_sql->execute()) {
                    while ($_result = $p_sql->fetch(PDO::FETCH_ASSOC))  {
                        $_retorno[] = $_result; 
                    }
                }
                return $_retorno;
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
    }
  • group your updates of each user and then see the amount there is

  • @Gumball but how do I publish the result along with foreach tags ?

  • @Fbor It would be like for a reply as I could do it ?

2 answers

1

You have to make one count() in your query and recover in PHP.

$Query = "SELECT 
COUNT(codigo) AS qtd,
funcionarios.usuario,
date_format(funcionarios.data,'%d/%m/%Y %H:%i:%s') as data ,
codigo,
nome 
FROM funcionarios";

if( !empty($WHERE) )$Query .= ' WHERE '.implode(' AND  ', $WHERE );

$Query .= " GROUP BY funcionarios.usuario";

PHP

Within the foreach() that has to be lower case letter.

$value['qtd']
  • This is when I make this error SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'DISTINCT(funcionarios.usuario), date_format(employees.data,'%d/%m' at line 2

  • Put your new query in the question to see

  • I already posted the new query

  • Beauty. I posted a new way to do it. Forehead.

  • make the same mistake, buddy

  • Give an echo $Query and put the result here. And try to give a space after FROM

Show 1 more comment

0

 SELECT ultima_atualizacao , count(ultimaatualizacao) as total
  FROM suatable
 GROUP BY ultima_atualizacao

is just an example Focus on your context and try this way I believe it is easier

  • it give the same error as the above query . I tried to set several ways and nothing give the same error.

  • executed only the sql code in some test query ??

  • hurum mas ele sempre dar erro SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'DISTINCT(funcionarios.usuario), date_format(employees.data,'%d/%m' at line 2

Browser other questions tagged

You are not signed in. Login or sign up in order to post.