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! :)