If inside if while inside while inside while (slow)

Asked

Viewed 143 times

0

I’m having a question about loop while:

I have the following query:

 $sql = "SELECT id,cod,nome,cnpj,insc_estadual,tributacao FROM empresas_base WHERE tributacao = '$filtro_regime_nome' AND responsavel = '$user' GROUP BY id ORDER BY id";

That one query starts a loop while, and within this loop I perform another consultation based on the company’s 'Cod'.

$sql2 = "SELECT * FROM controle_fiscal WHERE empresa = '$empresaCod' AND mes_correspondente = '$m' AND ano_correspondente = '$a' GROUP BY id ORDER BY id ";

And with that second query i perform one more loop while within the previous loop to pick up the values I need, and on top of that, within this second loop, there are several ifs inside ifs, what makes the consultation end up being slower than expected.

OBS: The loops and ifs shall be governed by the tasks of the classe model, are not linear, but nevertheless ifs

Doubt:

1 - It is right to do this ?

2 - There is another way to do this with just one query ?

php mysql

  • Correct is relative, in general can optimize the way to do, if post as you did so far it may be possible to provide a better suggestion.

  • Everything that works and brings a satisfactory result is correct. Of course, there are cases where we can optimize to gain a few nanoseconds or, not overload the server.

1 answer

3


The only way to do this with a query is by using the JOIN:

NOTE: I made an example of how it would look, but since I do not know your database, it may need some adjustments, I advise you to take a look at JOIN and see how would be better in your case.

SELECT e.id,e.cod,e.nome,e.cnpj,e.insc_estadual,e.tributacao, cf.*
FROM controle_fiscal cf
INNER JOIN empresas_base e ON cf.empresa = e.cod
WHERE e.tributacao = '$filtro_regime_nome' AND e.responsavel = '$user' AND cf.mes_correspondente = '$m' AND cf.ano_correspondente = '$a'
GROUP BY e.id 
ORDER BY e.id

Some points deserve attention, such as:

  • fields with the same name;

  • in which table do the Join;

  • the way you want the records returned;

Among others;

Here’s a link for you to take a look: http://www.devmedia.com.br/sql-join-entenda-como-funciona-o-retorno-dos-dados/31006

Browser other questions tagged php mysql

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