mysql query works when ran in phpmyadmin, but returns an error when ran in php

Question

Using the following function, I am attempting to update data to a database. The query works well when directly ran in phpmyadmin, but produces an error when running from php.

Here is the function

function edit_row($table, $columns, $where){
        db_connect();
        $query = "BEGIN WORK; SET AUTOCOMMIT=0; UPDATE $table SET $columns WHERE $where;    COMMIT;";
        echo $query; //this is to control for typing errors when testing in phpmyadmin
        mysql_query($query) or die ("Entry could not be made, " . mysql_error());
        db_close();
}

running this command:

edit_row("hello","test = 'some other string'", "test = 'somestring'");

echo outputs:

BEGIN WORK; SET AUTOCOMMIT=0; UPDATE hello SET test = 'some other string' WHERE test = 'some string'; COMMIT;

error produced:

Entry could not be made, 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 'SET AUTOCOMMIT=0; UPDATE hello SET test = 'some other string' WHERE test = 'so' at line 2

It appears to cut off the last bit of the query string, but not sure if this is a quirk of the die() method

John Parker · Accepted Answer · 2011-01-19 14:23:54Z

4

You cannot execute multiple queries in a single call to mysql_query - you need to break-up your query into four separate calls.

As per the PHP mysql_query documentation:

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.

The reason this works in phpMyAdmin, is because phpMyAdmin is in fact carrying out four separate queries in the background after de-constructing the statement(s) entered.

answered Jan 19, 2011 at 14:23

John Parker

54.5k11 gold badges133 silver badges131 bronze badges

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

10 Comments

Mild Fuzz Over a year ago

I see, but breaking this string apart into separate queries won't break the transaction?

John Parker Over a year ago

@Mild Fuzz As long as they're all executed within the same connection (i.e.: the resource returned by mysql_connect), you should be fine. :-)

Mild Fuzz Over a year ago

excellent. Now I just need to work out how to add a ROLLBACK on error.

John Parker Over a year ago

@Mild Fuzz Just be aware of the need to keep all such things within the same connection object and you should be good to go. :-)

Mild Fuzz Over a year ago

is this not technically making more database calls, and thus taking a performance hit?

|

Collectives™ on Stack Overflow

mysql query works when ran in phpmyadmin, but returns an error when ran in php

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related