1

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

Mild Fuzz
  • 29,463
  • 31
  • 100
  • 148

1 Answers1

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.

John Parker
  • 54,048
  • 11
  • 129
  • 129
  • I see, but breaking this string apart into separate queries won't break the transaction? – Mild Fuzz Jan 19 '11 at 14:28
  • 1
    @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. :-) – John Parker Jan 19 '11 at 14:31
  • excellent. Now I just need to work out how to add a ROLLBACK on error. – Mild Fuzz Jan 19 '11 at 14:50
  • @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. :-) – John Parker Jan 19 '11 at 14:53
  • is this not technically making more database calls, and thus taking a performance hit? – Mild Fuzz Jan 19 '11 at 16:00
  • @Mild Fuzz Technically yes, but largely meaninglessly so. – John Parker Jan 19 '11 at 16:03
  • is there not a way to chuck them all down at once, it seems a bit clunky if I have lengthy sql transactions. – Mild Fuzz Jan 19 '11 at 16:14
  • @Mild Fuzz Sorry, but this the way PHP's mysql_query function works. (I don't believe the Mysqli or PDO interfaces are any different in that respect, but I could be wrong.) At a guess, one of the reasons is because concepts like the "last insert ID" becoming meaningless if you allow multiple statements to be executed. Irrespective, there's no reason why you can't do what phpMyAdmin does and explode each block of statements prior to executing them individually. – John Parker Jan 19 '11 at 16:21
  • ahh, well is that's what phpMyAdmin does, then I guess that's the best way (famous last words?) – Mild Fuzz Jan 20 '11 at 08:59
  • @Mild Fuzz As long as you don't need any intermediate results (such as my mysql_insert_id example), then it should be OK. – John Parker Jan 20 '11 at 09:06