MySQL query works on mysqlworkbench but not on php environment

user_6256711 0
Viewed 385

I have a query that perfectly works under MySQL workbench program with MySQL 5.6 version, but when I try to test the same query in a PHP environment I got the error below:

{
  "code": 500,
  "response": {
    "error": {
      "description": "Query error",
      "error": {
        "errorInfo": [
          "42000",
          1064,
          "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')t1 HAVING price > 0 AND price < 804' at line 1"
        ]
      },
      "query": null
    }
  }
}

The query is this one:

SELECT * FROM(
   SELECT t.id, 
          t.user_id , 
          t.invoice_num, 
          t.registration, 
          SUM(t.price) as price
   FROM t_0 t 
   WHERE t.brandid=587
   GROUP BY t.invoice_num)t1 
   HAVING price > 0 AND price < 12
   ORDER BY id DESC LIMIT 100 OFFSET 0;

In the PHP environment I'm just using the PDO connection for execute the query in this way:

$test_query = "here the query that I have";
$result = $con->query($test_query)->fetchAll(PDO::FETCH_ASSOC);

Can someone explain to me what could cause the problem? From my side, it seems pretty much correct as syntax.

mysql php
1 Answers

Try by changing the query to something that would work in almost any database?
(almost, because LIMIT can be database specific. F.e. MS SQL Server uses TOP instead)

SELECT 
 MAX(t.id) AS id, 
 MAX(t.user_id) AS user_id, 
 t.invoice_num, 
 MAX(t.registration) AS registration, 
 SUM(t.price) as price
FROM t_0 t 
WHERE t.brandid = 587
GROUP BY t.invoice_num
HAVING SUM(t.price) BETWEEN 1 AND 11
ORDER BY id DESC 
LIMIT 100 OFFSET 0
user_40034190
Related