Converting two MySQL selects to one

Question

Converting two MySQL selects to one

Navigation

#1 by (7 votes)
#2 by (1 votes)

2

I have a small question about MySQL.

I have the following MySQL query:

sq1 = mysqli($conexao,"select * from tabela1");
$tr1 = mysqli_num_rows($sq1);

Then I make the famous loop:

for ($i=0;$i<$tr1;$i++)
{ 
$registro1 = mysqli_fetch_array($sq1);
$codigo1   = registro1['tabela1_codigo'];

From codigo1 , I make a new query to search for data in tabela2 :

sq2 = mysqli_query($conexao, "select * from tabela2 where 
tabela2_estrangeira='$codigo1'");

sq2 = mysqli_query($conexao, "select * from tabela3 where 
tabela3_estrangeira='$codigo1'");

// faço um outro 'for' aqui para listar os dados da tabela2:

// faço um outro 'for' aqui para listar os dados da tabela3:
 ...

}

My question is: How can I optimize these queries using only select ?

php mysql mysqli

asked by anonymous 31.07.2016 / 16:56

2 answers

Exit multiple loops without using goto How to add a DIV to every 3 paragraphs (p)

score 7 · Answer 1

You can do something like this:

$sql = 'select T1.* from tabela1 as T1
 LEFT JOIN tabela2 AS T2 ON T2.tabela2_estrangeira = T1.tabela1_codigo';

I did something generic because JOIN might be right RIGHT JOIN or you might also want to use INNER or OUTER .

Finally, so as not to complicate too much, use LEFT JOIN as above.

Be aware that in the example I applied aliases.

Table1 uses T1 and the second table uses T2 .

To access the columns of table1, do T1.nome_da_caluna

ex: T1.tabela1_codigo

score 1 · Answer 2

In your case, as you are not injecting any code, I do not see the need to pass a variable at risk of sql injection toa.

/* 
  Isso "t1.*,t2.*,t3.*" é o mesmo que isso "*",
  mas se você, de repente, quiser separar os valores,
  basta criar um alias, tipo: t1.nome as nom_tabela1, t2.nome as nom_tabela2
*/
    $sql = mysqli_query($conexao,
           'SELECT t1.*,t2.*,t3.*
            FROM tabela1 t1
            LEFT JOIN tabela2 t2
            on(t1.tabela1_codigo=t2.tabela2_estrangeira)
            LEFT JOIN tabela3 t3
            on(t2.tabela2_estrangeira=t3.tabela3_estrangeira)
            WHERE 1');