Mysql variables do not work with php mysqli_fetch_array()

Question

I set two Mysql widables to simplify one more Query that I want to deploy on my system. Using this command directly in SGBD, it works very well, however, when you want to use as a query string in my php script, it doesn’t work. Gives error in function mysqli_fetch_array(connected, queryString). What could 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 from now on! Good week! :)

Answer 1

The problem is that they are darlings separated:

1. SET @totVenda := (SELECT SUM(total) FROM tb_produto_item_estoque_saida);
2. SET @totPorcentagem := (SELECT SUM((ROUND((total/@totVenda*100),2))...
3. SELECT i.id, i.nome, SUM(ies.quantidade) AS quantidade, ies.valor, ...

And the function mysqli_query, even for the sake of security, does not execute a string in this format.

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

mysqli_multi_query($link, $query)

https://secure.php.net/manual/en/mysqli.multi-query.php

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 ... ");

As long as you wear the same $link in all works, because the variables are retained by connection.