Mysql variables do not work with php mysqli_fetch_array ()

Question

Mysql variables do not work with php mysqli_fetch_array ()

Navigation

#1 by (3 votes)

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

php mysql

asked by anonymous 11.09.2018 / 15:11

1 answer

How to leave a persistent CRUD? [closed] How to deal with multiple logins in Laravel? [duplicate]

score 3 · Accepted Answer

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.