Criteria for consultation with a wildcard character

Question

I am trying to use wildcard character as query criterion in my query, however the result is returned empty.

include_once(".. /inc/conexion.php");

$lj = "4";
$linha      = isset($_GET['ans'])?$_GET['ans']:"";
$data   = date("d-m-Y");
$partes = explode("-", $data);
$ano    = $partes[2];
$periodo = "$ano%";


$sql = "SELECT sjy_vendas.tipo, Sum(sjy_vendas.qt) AS qt, Sum(sjy_vendas.qtv) AS qtv, Sum(sjy_vendas.rprd) AS rprd, Sum(sjy_vendas.rsrv) AS rsrv, Sum(sjy_vendas.tprd) AS tprd, Sum(sjy_vendas.tsrv) AS tsrv, Sum(sjy_vendas.vlr_compra) AS vlr_compra
FROM sjy_vendas
WHERE sjy_vendas.empresa = $lj 
AND sjy_vendas.tipo = $linha
AND dt_nf CONCAT('%',:param,'%')
GROUP BY sjy_vendas.tipo";
$sql->bindParam(':param',$ano);
$consulta   = mysqli_query($conexao,$sql);
while($dados = mysqli_fetch_array($consulta)){

$qtv    = $dados['qtv'];
$rprd   = $dados['rprd'];

}

echo "Quantidade: " . $qtv . "<br />";
echo "Receita: " . $rprd . "<br />";

Answer 1

Change the sql variable to a mysqli_prepare function call by passing the sql string as parameter then use the msqli_bind_param function to attach the $year parameter. Follow an example, will have to adapt to work according to your needs:

$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$sql = mysqli_prepare($link, "SELECT sjy_vendas.tipo, Sum(sjy_vendas.qt) AS qt, Sum(sjy_vendas.qtv) AS qtv, Sum(sjy_vendas.rprd) AS rprd, Sum(sjy_vendas.rsrv) AS rsrv, Sum(sjy_vendas.tprd) AS tprd, Sum(sjy_vendas.tsrv) AS tsrv, Sum(sjy_vendas.vlr_compra) AS vlr_compra
FROM sjy_vendas
WHERE sjy_vendas.empresa = $lj 
AND sjy_vendas.tipo = $linha
AND dt_nf CONCAT('%',:param,'%')
GROUP BY sjy_vendas.tipo""));
mysqli_stmt_bind_param($sql, "s", $ano);

More detailed information on this link: http://php.net/manual/en/mysqli.prepare.php

Answer 2

If the problem is the concatenation of the string '%' in the variable of the parameter, you can try as in the following example using the CONCAT function of SQL:

$sql = "SELECT item_title FROM item WHERE item_title LIKE CONCAT('%',:param,'%')";
$sql->bindParam(':param', $paramentro );