Query works in MySQL Workbench but not in PHP
Get the solution ↓↓↓
I have a query that works perfectly in MySQL Workbench but returns some columns as NULL when executed in my PHP webpage.
EverSQL validator says there is a syntax error without specifying what it is, which is not helpful. Can someone tell me what is wrong?
$query = "
SELECT CONCAT(ade.`type`, ' ', ade.subtype) AS `type`,
COUNT(*) AS count,
@total := SUM(ade.total_co2_kg) AS total,
ROUND(SUM(ade.raw_materials) / @total * 100, 2) AS raw_materials,
ROUND(SUM(ade.supplying) / @total * 100, 2) AS supplying,
ROUND(SUM(ade.making) / @total * 100, 2) AS making, ROUND(SUM(ade.assembly) / @total * 100, 2) AS assembly,
ROUND(SUM(ade.distribution) / @total * 100, 2) AS distribution
FROM glpi_plugin_gtm_computermodels_association AS ass_mod
JOIN glpi_plugin_gtm_ademe_co2_emissions_for_manufacture AS ade
ON ass_mod.gtm_ademe_id = ade.id
GROUP BY ade.`type`, ade.subtype
ORDER BY total DESC";
$results = $DB->request($query);
foreach ($results as $result) {
echo implode(', ', $result);
}
Result in MySQL Workbench:
'Computer Laptop', '1', '156.00000', '76.92', '1.21', '0.48', '1.22', '20.32'
Result in PHP webpage:
Computer Laptop, 1, 156.00000, , , , ,
Answer
Solution:
I couldn't find the reason why it would not work properly in PHP, but I found a workaround by not using the SUM operator and multiplying the results by the count afterwards...
Answer
Solution:
MySQL doesn't guarantee that expressions in theSELECT list will be evaluated from left to right. So you can't safely use a variable that's assigned in one expression in a later expression.
Instead of the@total variable, write out its expression each time.
$query = "SELECT
CONCAT(ade.`type`, ' ', ade.subtype) AS `type`,
COUNT(*) AS count,
SUM(ade.total_co2_kg) AS total,
ROUND(SUM(ade.raw_materials) / SUM(ade.total_co2_kg) * 100, 2) AS raw_materials,
ROUND(SUM(ade.supplying) / SUM(ade.total_co2_kg) * 100, 2) AS supplying,
ROUND(SUM(ade.making) / SUM(ade.total_co2_kg) * 100, 2) AS making, ROUND(SUM(ade.assembly) / SUM(ade.total_co2_kg) * 100, 2) AS assembly,
ROUND(SUM(ade.distribution) / SUM(ade.total_co2_kg) * 100, 2) AS distribution
FROM glpi_plugin_gtm_computermodels_association AS ass_mod
JOIN glpi_plugin_gtm_ademe_co2_emissions_for_manufacture AS ade ON ass_mod.gtm_ademe_id = ade.id
GROUP BY ade.`type`, ade.subtype
ORDER BY total DESC";
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: call to undefined function str_contains()
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.