List calls with or without id

Question

List calls with or without id

Navigation

#1 by (0 votes)

0

Gentlemen. 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 do not have the id in the completed call It lists all, according to the client, however, if it does not have a valid filled id, it does not list that record

php sql codeigniter

asked by anonymous 12.06.2015 / 04:31

1 answer

Tab Scroll - Interrupt setInterval with jQuery.mouseup Sending files to google drive with .net

score 0 · Accepted Answer

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 in the chamada table even though funcionario does not exist.

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

Difference between INNER JOIN and OUTER JOIN

-

Parameter concatenation problem

Take care! This opens the security hole, one of which is running Sql Injection . An alternative to PHP with CodeIgniter is this here:

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

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

Source: link

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You do not have to remember to manually escape data; the engine does it automatically for you.