Problem in SQL statement

Question

Problem in SQL statement

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a problem in my SQL code. Well, I have this function that is in class Usuario :

public function CS_GetUsuarios($sql_aux = false)
{
    global $objConexao;

    $strSql =   "SELECT U.*, A.*, G.*,
                (
                SELECT COALESCE(SUM(V.countView), 0) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
                ) as total_visitas,
                (
                SELECT COALESCE(SUM(F.countVotos), 0) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
                ) as total_votos
                FROM usuario U
                LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
                LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario
                WHERE U.indAtivo = 'S' AND G.indExibir = 'S'
                $sql_aux";

    $vetDados = $objConexao->query($strSql)->fetchAll();
    return $vetDados;
}

And in the index I'm calling the same:

$vet_dados_top = Usuario::CS_GetUsuarios("AND U.tipo_conta = '1' GROUP BY U.codUsuario ORDER BY total_visitas DESC LIMIT 12");

Well, my problem is that it only returns me the complete data of users who have visits (record) in the usuario_visitas table. When the user did not receive any visitors in the profile he specifically returns the codUsuario = null , and in my logic, I have programmed him to bring all the records, even from those users who do not have any visitors.

Giving var_dump() to $vet_dados_top the result is more or less this:

Visiting user

array (size=121)
  'codUsuario' => string '1182652231' (length=10)
  0 => string '1182652231' (length=10)
  'strLogin' => string 'fulano' (length=7)
  1 => string 'fulano' (length=7)
  'strSenha' => string 'XXXXXXXXXXXXX' (length=32)
  2 => string 'XXXXXXXXXXXXX' (length=32)
  [...]

User that does not have visits

array (size=121)
  'codUsuario' => null
  0 => string '1182652232' (length=10)
  'strLogin' => string 'ciclano' (length=5)
  1 => string 'ciclano' (length=5)
  'strSenha' => string 'XXXXXXXXXXXX' (length=32)
  2 => string 'XXXXXXXXXXXX' (length=32)
  [...]

The usuario_visitas table does not have the codes for all users, only the ones that have been visited. Visited, has been added to the table.

Where am I going wrong in my thinking?

php mysql

asked by anonymous 09.06.2017 / 20:02

2 answers

0

...
"SELECT U.*, A.*, G.*,
                (
                SELECT COALESCE(SUM(V.countView), 0) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
                ) as total_visitas,
                (
                SELECT COALESCE(SUM(F.countVotos), 0) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
                ) as total_votos
                FROM usuario U
                LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
                LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario
                      AND G.indExibir = 'S'     
                WHERE U.indAtivo = 'S'
                $sql_aux";
...

09.06.2017 / 20:09

How to create a .txt file on Android and make it available to open on computers? Doubt with layout Responsive bootstrap

score 1 · Accepted Answer

No where, you put the G table clause.

Also change the order of coalesce , within Sum() .

When passing parameters, do not report group by, there is no aggregate function in the query to be grouped.

Try the following:

"SELECT U.*, A.*, G.*, U.codUsuario as codigoUsuario,
            (
            SELECT SUM(COALESCE(V.countView, 0)) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
            ) as total_visitas,
            (
            SELECT SUM(COALESCE(F.countVotos, 0)) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
            ) as total_votos
            FROM usuario U
            LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
            LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario AND G.indExibir = 'S'
            WHERE U.indAtivo = 'S' 
            $sql_aux";

I added another column looking for the value of U.codUsuario and renaming it to UI, in your php, search for the UI value and see if it is correct