cannot run multiple statements in one query

Question

I have created the MySql procedure which create multiple tables and insert data, and if data do not current then remove it and show the messages in mysql command line or my sql workbench.. For this i used variable and then display like following it is working fine in mysql.

SET _output = 'Generating table one...'; 
select _output;

create table ec_ms_yy_qt
(select * from table1);
call checkdata();

SET _output = 'Generating table tow..'; 
select _output;

If i called same from php it stop after first statement and do not execute the procedure. It only return 'Generating table one...'; . It mean stop running if found the select statement

$rs = mysql_query( ‘CALL processdata()’ );
while($row = mysql_fetch_assoc($rs))
{
  print_r($row);
}

Then I removed the select _output from store procedure it working fine. But this is not solution i want , user will not informed by any error occur and where it was raised. It also hanged the program till procedure finished and browse show on status bar waiting for localhost.

Are you sure it's not running the procedure? I suspect mysql_query() may only be able to fetch the rows from the first query. Everything else runs, you just can't get those rows. — Barmar
– Barmar, Commented Sep 14, 2013 at 10:01
mysql_* will only run one query at a time to help mitigate injection attacks. The first should evaluate like you said, but the others should be ignored if operating correctly. — David Houde
– David Houde, Commented Sep 14, 2013 at 12:50

Kaii · Accepted Answer · 2013-09-15 18:31:26Z

3

This is a feature of PHPs MySQL extension trying to prevent SQL injection (to some extend). Please note the mysql extension in PHP is deprecated, unmaintained and should not be used when writing new PHP scripts. The deprecation is documented in the manual. The documentation on mysql_query() also clearly states:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Note that the new mysqli extension, which is a replacement for the original mysql extension, has a special function to allow multiple statements in one query string: mysqli_multi_query()

edited Sep 15, 2013 at 18:31

answered Sep 14, 2013 at 10:08

Kaii

20.6k3 gold badges41 silver badges63 bronze badges

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

3 Comments

user1480032 Over a year ago

$mysqli = new mysqli("xxxxx", "root", "xxxxx", "xxxx"); $mysqli->connect_errno; $sql = "call processdata()"; $res=$mysqli->multi_query($sql) ; echo $res;

user1480032 Over a year ago

Thanks it is working...but one problem it is working on background on mysql server on browser it echo 1... and do not display the output messages which in between in procedure.. ? How can i store all the messsages one by on and display on the screen so user can know the current status of procedure (All query in one procedures) $mysqli = new mysqli("xxxxx", "root", "xxxxx", "xxxx"); $sql = "call processdata()"; $res=$mysqli->multi_query($sql) ; echo $res;

user1480032 Over a year ago

I add the following code and the output messages show on the screen all togther, but after complete all queries of the procedure and browse is hanged till this..$res=$mysqli->multi_query($sql) ; do { if ($res = $mysqli->store_result()) { var_dump($res->fetch_all(MYSQLI_ASSOC)); $res->free(); } } while ($mysqli->more_results() && $mysqli->next_result());

Collectives™ on Stack Overflow

cannot run multiple statements in one query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related