Run multiple MySQL queries from PHP | Why is this not working?

Question

I am trying to run multiple queries on some mysql dbs from a php file. However it is not working. Can anyone point out where I am going wrong?

Here is the contents of the php file:

<?
require_once('/home/xxxxxx/public_html/xxxxxx/dbconnect.php');
$query = "
TRUNCATE TABLE db2.table1;
INSERT INTO db2.table1
SELECT 
       column1, column2, column3, column4
       FROM db1.table1;

TRUNCATE TABLE db2.table2;
INSERT INTO db2.table2
SELECT 
       column1, column2, column3, column4 
       FROM db1.table2;
ANALYZE TABLE db2.table2;
";

$result = @mysql_query($query);    
?>

Thanks in advance for any help.

Pekka · Accepted Answer · 2011-07-04 16:30:16Z

4

The most important thing to do in such a case is debugging:

Remove the @ prefix to see errors
Add a echo mysql_error() if mysql_query() returns false to see mySQL's error messages

in your specific case, mysql_query() is not able to execute more than one statement. The easiest solution is to use two separate commands.

answered Jul 4, 2011 at 16:30

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

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

Comments

Davor Lucic · Accepted Answer · 2011-07-04 16:29:18Z

3

You are making only one query. Read docs for mysql_query

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.

You should split each query in it's own string and send it one by one to MySQL.

$query1 = "TRUNCATE TABLE db2.table1;";
$query2 = "INSERT INTO db2.table1
SELECT 
       column1, column2, column3, column4
       FROM db1.table1;";

$query3 = "TRUNCATE TABLE db2.table2;";
$query4 = "INSERT INTO db2.table2
SELECT 
       column1, column2, column3, column4 
       FROM db1.table2;";
$query5= "ANALYZE TABLE db2.table2;";

$result1 = @mysql_query($query1);  
$result2 = @mysql_query($query2);  
$result3 = @mysql_query($query3);  
$result4 = @mysql_query($query4);  
$result5 = @mysql_query($query5);

Or use some other interface function that supports multiple queries. Personally I don't know if there is such a thing.

answered Jul 4, 2011 at 16:29

Davor Lucic

29.5k9 gold badges68 silver badges77 bronze badges

1 Comment

grossvogel Over a year ago

If you have mysqli installed, you can execute multiple queries in one function call: php.net/manual/en/mysqli.multi-query.php

Collectives™ on Stack Overflow

Run multiple MySQL queries from PHP | Why is this not working?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related