error mysql: You have an error in your SQL syntax;

Question

Im using query mysql in phpmyadmin it's works correctly.

SET @runtot=0;
SELECT p_reference, p_id, p_description, (@runtot := @runtot + p_quantity) AS runningTotal
FROM (
    SELECT p_reference, p_id, p_description, p_quantity
    FROM product_table
    WHERE p_description = 'product_1' AND p_quantity <= 21000
    ORDER BY p_reference
) AS l
WHERE @runtot + p_quantity <= 21000;

but I have problem when used in file php, 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 'SELECT p_reference, p_id, p_description, (@runtot := @runtot + p_quantity) AS ru' at line 2

THIS SOURCE CODE PHP

<?php

$connect = mysql_connect("localhost", "root", "");
mysql_select_db("datatest");

$query = mysql_query("SET @runtot=0;
SELECT p_reference, p_id, p_description, (@runtot := @runtot + p_quantity) AS runningTotal
FROM (
    SELECT p_reference, p_id, p_description, p_quantity
    FROM product_table
    WHERE p_description = 'product_1' AND p_quantity <= 21000
    ORDER BY p_reference
) AS l
WHERE @runtot + p_quantity <= 21000");

if($query === FALSE){
           die(mysql_error());
    }

while($data = mysql_fetch_assoc($query)){

echo $data['p_reference']." ".$data['p_quantity'];
}

?>

AND THIS SOURCE FOR CREATE product table

CREATE TABLE IF NOT EXISTS `product_table` (
  `p_id` int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  `p_description` varchar(50) NOT NULL,
  `p_reference` varchar(25) NOT NULL,
  `p_location` varchar(25) NOT NULL,
  `p_quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `product_table` (`p_id`, `p_description`, `p_reference`, `p_location`, `p_quantity`) VALUES
(1, 'Product_1', '1A00001', 'AP07', 7000),
(2, 'Product_1', '1A00001', 'AF05', 6000),
(3, 'Product_1', '1A00233', 'DS07', 7000),
(4, 'Product_1', '1A00233', 'SD10', 5000),
(5, 'Product_1', '1A00001', 'YB12', 7000),
(6, 'Product_1', '1A00001', 'AN01', 7000),
(7, 'Product_1', '1A00001', 'AP04', 7000),
(8, 'Product_1', '1A00245', 'AP01', 7000),
(9, 'Product_1', '1A00001', 'QD01', 7000),
(10, 'Product_1', '1A00001', 'SC01', 7000);

Don't use snippets with PHP. Only use them for JavaScript, HTML and CSS — SuperDJ, Dec 11 '14 at 15:05
use mysql_error() to find out what the problem is. Furthermore, consider using mysqli_* functions as mysql_* is deprecated. — t.h3ads, Dec 11 '14 at 15:05
`mysql_query` returned false because it didn't like your query. you are trying to extrapolate data from a boolean. — Flosculus, Dec 11 '14 at 15:05
`mysql_query` does not permit multiple semicolon-delimited queries (`SET;SELECT;`) per default. — mario, Dec 11 '14 at 15:09
this message using mysql_error(): 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 'SELECT p_reference, p_id, p_description, (@runtot := @runtot + p_quantity) AS ru' at line 2 — phptraining, Dec 11 '14 at 20:51

isa · Accepted Answer · 2014-12-12T09:42:44.563

1

Your query is wrong. Please echo your query before execute and run your query in phpmyadmin or similar.

--UPDATE--

In your statement, there is more than one query, concatenated by a semicolon. You cannot execute more than one query in mysql_query().

In order to achieve this, you should use mysqli_multi_query() function of php msqli extension.

edited Dec 12 '14 at 09:42

answered Dec 11 '14 at 15:07

isa

487
2
7
17

I excute my query in phpmyadmin it's correct but in file php I have problem – phptraining Dec 11 '14 at 20:19
@phptraining please check the update. – isa Dec 12 '14 at 09:38
thank you for your help I use mysqli_multi_query() it's very effictive – phptraining Dec 12 '14 at 12:06

score 0 · Answer 2 · answered Dec 11 '14 at 15:09

I would recommend trying to pin point where the fault is. From face value your SQL looks fine, but execute it directly with the database first.

I see that you have no check on mysql_connect. a simple check such as if(!$connect) { die(mysql_error()); } will confirm that the connection is correct. Your Error output of boolean is because a null or false value is returned from either mysql_connect or mysql_query

As Typoheads has suggested in the comments, use mysqli library instead as mysql is depreciated in the latest php

I excute my query in phpmyadmin it's correct but in file php I have problem — phptraining, Dec 11 '14 at 20:49

error mysql: You have an error in your SQL syntax;

2 Answers2