mysql query error in PHP but working in phpmyadmin

Question

I have write a query in mysql and it is running in phpmyadmin, but in PHP it giving me 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 docno, docdate, doctype, narration, drcr, (case when drcr = 'Dr' then amo' at line 2

My Query is in PHP is :

$sql = "set @runtot := 0; select docno, docdate, doctype, narration, drcr, (case when drcr = 'Dr'
then amount else 0 end) as debit, (case when drcr = 'Cr' then amount else 0 end) as 
credit, concat(abs((@runtot := @runtot + (case when drcr = 'Dr' then amount else amount*-1  
end))), (case when @runtot < 0 then ' Cr' else ' Dr' end) ) as balance from (select docno,  
docdate, doctype, narration, amount, drcr from ledger where accode = 1 )as q1";

what is wrong here with php?

You can't run multiple statements using one mysql_query() call. — Pekka
– Pekka, Commented Jun 22, 2013 at 8:57
use mysqli_* functions, in this you can run multiple queries. — Yogesh Suthar
– Yogesh Suthar, Commented Jun 22, 2013 at 8:57

sybear · Accepted Answer · 2013-06-22 09:02:52Z

1

You have to use transactions to perform several commands in MySQL. You create a varible first and then execute another command.

Using just single function mysqli_query you can perform only one command and commands are separated by ;.

When you paste the code into PHPMyAdmin, it will work though as it is still supposed to be a transaction.

You can read more about transactions.

edited Jun 22, 2013 at 9:02

answered Jun 22, 2013 at 8:57

sybear

7,7921 gold badge25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query error in PHP but working in phpmyadmin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related