PHP Version: 5.6
Framework: PDO
I have the following database query:
$cIndus = array("ex1", "ex2");
$query = "SELECT 'nome_agr_corr' AS 'brita',
CASE
WHEN ? = 'usd' AND ? = 'externos' AND ? = 'toneladas'
THEN ROUND('valor_ex_ton',2)
WHEN ? = 'usd' AND ? = 'externos' AND ? = 'm3'
THEN ROUND('valor_ex_ton' * 'baridade',2)
WHEN ? = 'usd' AND ? = 'internos' AND ? = 'toneladas'
THEN ROUND('valor_in_ton',2)
WHEN ? = 'usd' AND ? = 'internos' AND ? = 'm3'
THEN ROUND('valor_in_ton' * 'baridade',2)
WHEN ? = 'aoa' AND ? = 'externos' AND ? = 'toneladas'
THEN TRIM(ROUND('valor_ex_ton' * ?))+0
WHEN ? = 'aoa' AND ? = 'externos' AND ? = 'm3'
THEN TRIM(ROUND('valor_ex_ton' * 'baridade' * ?))+0
WHEN ? = 'aoa' AND ? = 'internos' AND ? = 'toneladas'
THEN TRIM(ROUND('valor_in_ton' * ?))+0
WHEN ? = 'aoa' AND ? = 'internos' AND ? = 'm3'
THEN TRIM(ROUND('valor_in_ton' * 'baridade' * ?))+0
END AS 'preco'
FROM 'agregados'
LEFT JOIN 'valorun_interno_ton'
ON 'agr_id' = 'agr_bar_id'
LEFT JOIN 'valorun_externo_ton'
ON 'agr_id' = 'agr_bar_ton_id'
LEFT JOIN 'baridades'
ON 'agr_id' = 'agregado_id'
WHERE 'nome_agre' IN (?)
GROUP BY 'nome_agr_corr'
ORDER BY 'nome_agr_corr'
";
$rows = $this->db->prepare($query);
$rows->execute([$tipoTabela, $destino, $unidade,
$tipoTabela, $destino, $unidade,
$tipoTabela, $destino, $unidade,
$tipoTabela, $destino, $unidade,
$tipoTabela, $destino, $unidade, $cambioDoDia,
$tipoTabela, $destino, $unidade, $cambioDoDia,
$tipoTabela, $destino, $unidade, $cambioDoDia,
$tipoTabela, $destino, $unidade, $cambioDoDia,
$cIndus
]);
All variables are placed in their positions, I think, therefore, does not give any error. Only one blank table appears, when it should be filled. What am I doing wrong.
I have the show errors enabled: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
I tested with "placeholders" instead of "?" and the result is the same ... nothing!
$query = "SELECT 'nome_agr_corr' AS 'brita',
CASE
WHEN ':tipoTabela' = 'usd' AND ':destino' = 'externos' AND ':unidade' = 'toneladas'
THEN ROUND('valor_ex_ton',2)
WHEN ':tipoTabela' = 'usd' AND ':destino' = 'externos' AND ':unidade' = 'm3'
THEN ROUND('valor_ex_ton' * 'baridade',2)
WHEN ':tipoTabela' = 'usd' AND ':destino' = 'internos' AND ':unidade' = 'toneladas'
THEN ROUND('valor_in_ton',2)
WHEN ':tipoTabela' = 'usd' AND ':destino' = 'internos' AND ':unidade' = 'm3'
THEN ROUND('valor_in_ton' * 'baridade',2)
WHEN ':tipoTabela' = 'aoa' AND ':destino' = 'externos' AND ':unidade' = 'toneladas'
THEN TRIM(ROUND('valor_ex_ton' * ':cambioDoDia'))+0
WHEN ':tipoTabela' = 'aoa' AND ':destino' = 'externos' AND ':unidade' = 'm3'
THEN TRIM(ROUND('valor_ex_ton' * 'baridade' * ':cambioDoDia'))+0
WHEN ':tipoTabela' = 'aoa' AND ':destino' = 'internos' AND ':unidade' = 'toneladas'
THEN TRIM(ROUND('valor_in_ton' * ':cambioDoDia'))+0
WHEN ':tipoTabela' = 'aoa' AND ':destino' = 'internos' AND ':unidade' = 'm3'
THEN TRIM(ROUND('valor_in_ton' * 'baridade' *':cambioDoDia'))+0
END AS 'preco'
FROM 'agregados'
LEFT JOIN 'valorun_interno_ton'
ON 'agr_id' = 'agr_bar_id'
LEFT JOIN 'valorun_externo_ton'
ON 'agr_id' = 'agr_bar_ton_id'
LEFT JOIN 'baridades'
ON 'agr_id' = 'agregado_id'
WHERE 'nome_agre' IN (':cindus')
GROUP BY 'nome_agr_corr'
ORDER BY 'nome_agr_corr'
";
$rows = $this->db->prepare($query);
$rows->execute([':tipoTabela' => $tipoTabela,
':destino' => $destino,
':unidade' => $unidade,
':cindus' => $cIndus
]);
NOTE: Variable values are function parameters. ( public function getSQL_interno($cInd, $tipoTabela, $destino, $mesActual, $unidade)
) and a global variable ( cIndus
).