Query string works in Phpmyadmin and does not work in PHP

Question

Query string works in Phpmyadmin and does not work in PHP

Navigation

#1 by (2 votes)

1

I have the following query that takes a date, has been tested in phpmyadmin and in the HeidiSQL program successfully:

Query Mysql

set @rn:=0,@grp:=0,@prevdate:='';
from 
(select DataEncomenda,
   @rn:=@rn+1 as rownum, 
    @grp:=if(@prevdate=DataEncomenda,@grp,@grp+1) as descGrp, 
    @prevdate:=DataEncomenda as unused 
    from encomendas 
    order by DataEncomenda DESC 
) inR 
where descGrp=2 group by descGrp

When I try to use it in php it returns the error:

Fatal error: Call to a member function fetch_assoc () on boolean in xxx

The error returned by the bank via php is:

You have an error in your SQL syntax; SELECT Data FROM (SELECT Data Order, @rn: = @ rn + 1 as rownum 'at line 2

PHP code:

$PenultimaData = " 
set @rn:=0,@grp:=0,@prevdate:=''; 
SELECT DataEncomenda
FROM 
(   SELECT DataEncomenda,
    @rn:=@rn+1 as rownum, 
    @grp:=if(@prevdate=DataEncomenda,@grp,@grp+1) as descGrp, 
    @prevdate:=DataEncomenda as unused 
    FROM encomendas 
    ORDER by DataEncomenda DESC 
) inR 
where descGrp=2 group by descGrp";

$Rs = $mysqli->query($PenultimaData);
$Resultado = $Rs->fetch_assoc();

$PenulData = $Resultado['DataEncomenda'];

php mysql query mysqli

asked by anonymous 01.12.2016 / 15:01

1 answer

Fill in a list with Json's return Doubt with parameter

score 2 · Accepted Answer

The query() method executes only one SQL statement at a time, the query sends two of them, the first is the definition of the variables, the second is the select, the delimitation is done by a semicolon.

The simplest way to resolve this is to break the instructions (when possible) and send them individually.

$mysqli->query("set @rn:=0,@grp:=0,@prevdate:='';");

$sql = "SELECT DataEncomenda
FROM 
(   SELECT DataEncomenda,
    @rn:=@rn+1 as rownum, 
    @grp:=if(@prevdate=DataEncomenda,@grp,@grp+1) as descGrp, 
    @prevdate:=DataEncomenda as unused 
    FROM encomendas 
    ORDER by DataEncomenda DESC 
) inR 
where descGrp=2 group by descGrp";

$Rs = $mysqli->query($sql);
$Resultado = $Rs->fetch_assoc();
print_r($Resultado);

Another way for more complex cases is to use multiple_query () that allows sending multiple queries.

//envia 3 consultas para o banco
$db->multi_query('set @teste = 2015; select @teste + 1 as ano; select @teste + 1 as ano;') or die($db->error);

do{
    //armazena o resultada consulta
    if($result = $db->store_result()){
        while($row = $result->fetch_assoc()){// extrai o resultado do resource
            echo $row['ano'] .'<br>';
        }
    }   
}while($db->more_results() && $db->next_result());
// primeiro verifica se existem mais resultados se sim, 'avança' para o próximo.