Mysql variables do not work with php mysqli_fetch_array ()

-2

I set two variables in MySQL to simplify another Query that I want to deploy to my system. Using this direct command in the DBMS, it works fine, however, when I want to use as a query string in my php script, it does not work. Gives an error in the mysqli_fetch_array (connection, queryString) function. What could it be?

<?php 

$str = " /*Defino as variáveis*/    
SET @totVenda := (SELECT SUM(total) FROM tb_produto_item_estoque_saida); 
SET @totPorcentagem := (SELECT SUM((ROUND((total/@totVenda*100),2))) FROM tb_produto_item_estoque_saida); 

/*Query principal onde faço uso das variáveis*/
SELECT i.id, i.nome, SUM(ies.quantidade) AS quantidade, ies.valor, SUM(ies.total) AS total, 

@totVenda AS TotalGeral, 

SUM((ROUND((ies.total/@totVenda*100),2))) AS porcentagem

FROM tb_produto_item AS i 
LEFT JOIN tb_produto_item_estoque_entrada AS iee ON i.id = iee.idItem
LEFT JOIN tb_produto_item_estoque_saida AS ies ON i.id = ies.idItem 

GROUP BY i.id
ORDER BY porcentagem DESC";

$query = mysqli_query($conexao->conecta(),$str);

while($retorno = mysqli_fetch_array($query))
{
  //Listaria o resultado aqui! 
}

?>

Thank you in advance! Good week! :)

    
asked by anonymous 11.09.2018 / 15:11

1 answer

3

The problem is that they are separate queries:

  • SET @totVenda := (SELECT SUM(total) FROM tb_produto_item_estoque_saida);
  • SET @totPorcentagem := (SELECT SUM((ROUND((total/@totVenda*100),2))...
  • SELECT i.id, i.nome, SUM(ies.quantidade) AS quantidade, ies.valor, ...
  • And the mysqli_query function, even for the sake of security, does not execute a string in this format.

    If you want more than one query you should use this function:

    mysqli_multi_query($link, $query)
    
      

    link

    Or run separately:

    mysql_query($link, "SET @totVenda := (SELECT SUM(total) FROM  ... );"); 
    mysql_query($link, "SET @totPorcentagem := (SELECT ... uma por linha ... );")
    mysql_query($link, "SELECT i.id, i.nome, SUM(ies.quantidade) AS ... ");
    

    Since you use the same $link in all works because the variables are retained per connection.

        
    11.09.2018 / 23:50