List calls with or without idFunctioned

Question

Sirs. I have the following function.

    $sql = "
            SELECT 
                c.*,
                cli.*,
                cli.razaosocial as nomeCliente,
                c.idCliente as idClienteChamada
            FROM 
                chamada as c, 
                cliente as cli 
            WHERE 
                c.idChamada = '".$id."' 
            AND 
                cli.idCliente = c.idCliente
    ";
     $consulta = $this->db->query($sql)->result();

    foreach($consulta as &$valor)
    {
        $sql = "
                    SELECT 
                        c.*,
                        cli.*,
                        f.idFuncionario,
                        f.*
                    FROM
                        chamada as c, cliente as cli, funcionario as f
                    WHERE
                        c.idCliente = '".$valor->idClienteChamada."'
                    AND
                        cli.idCliente = '".$valor->idClienteChamada."'
                    AND
                        c.idFuncionario = f.idFuncionario 
                    OR
                        c.idFuncionario = (NULL)
        ";  
         $valor->listaClientesView = $this->db->query($sql)->result();       
    }

I would like to list ALL calls even if you don’t have the function inside the call It lists all, according to the client, however, if it does not have a valid completed ID, it does not list that record

Answer 1

Change your SQL from

SELECT c.*,
       cli.*,
       f.idFuncionario,
       f.*
FROM chamada AS c,
     cliente AS cli,
     funcionario AS f
WHERE c.idCliente = '".$valor->idClienteChamada."'
  AND cli.idCliente = '".$valor->idClienteChamada."'
  AND c.idFuncionario = f.idFuncionario
  OR c.idFuncionario = (NULL)

To

SELECT c.*,
       cli.*,
       f.idFuncionario,
       f.*
FROM chamada AS c
INNER JOIN cliente AS cli ON c.idCliente = cli.idCliente
LEFT JOIN funcionario AS f ON c.idFuncionario = f.idFuncionario
WHERE c.idCliente = '".$valor->idClienteChamada."'

Attention to LEFT JOIN, in this situation it will bring everything that is on the table chamada even though there is funcionario.

Utilise JOIN is much more practical and readable, if you want to know more have this great answer here in ptSO.

Difference between INNER JOIN and OUTER JOIN

-

Problem of concatenation of parameters

Take care! This opens security breach, one of them is the execution of Sql Injection. An alternative in the PHP with CodeIgniter is this one:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; 

$this->db->query($sql, array(3, 'live', 'Rick'));

Source: https://ellislab.com/codeigniter/user-guide/database/queries.html

The Secondary Benefit of using Binds is that the values are Automatically escaped, producing Safer queries. You don’t have to Remember to Manually escape data; the engine does it Automatically for you.