How to update multiple queries as one

Question

this is mu query

$sql_query= "insert into shortdb1.currency_code set currency_code = 'KWD', 
based_on = 'KWD', currency_rate = '1.00', 
last_update = '2013-07-25 11:41:33';
insert into shortdb1.currency set currency_code = 'KWD', 
language_code = 'EN', currency_name = 'Kuwaiti Dinar';";


    $conn_1->query($sql_query)

but i getting 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 'insert into shortdb1.currency set currency_code = 'KWD', language_code = 'EN',' at line 4

sorry its my mistake i enter there – user475464 Jul 25 '13 at 08:44 — user475464, Jul 25 '13 at 08:44

score 1 · Answer 1 · answered Jul 25 '13 at 08:43

Your queries need to be stored as Strings, i.e.:

$sql_query = insert into shortdb1.currency_code set currency_code = 'INR', based_on = 'KWD', currency_rate = '200', last_update = '2013-07-25 11:14:26';

needs to be

$sql_query = "insert into shortdb1.currency_code set currency_code = 'INR', based_on = 'KWD', currency_rate = '200', last_update = '2013-07-25 11:14:26'";

score 1 · Answer 2 · answered Jul 25 '13 at 08:43

1

You are missing a ton of quotes and semicolons at the end of both query and php sentences:

$sql_query = "insert into shortdb1.currency_code set currency_code = 'INR', based_on = 'KWD', currency_rate = '200', last_update = '2013-07-25 11:14:26';";

$sql_query .= "insert into shortdb1.currency set currency_code = 'INR', language_code = 'EN', currency_name = 'Indian Rupee', based_on = 'KWD', currency_rate = '200', last_update   = '2013-07-25 11:14:26';";

$conn_1->query($sql_query);

answered Jul 25 '13 at 08:43

Andrius Naruševičius

8,348
7
49
78

Did you put down the first line ending exactly like: `';";`? – Andrius Naruševičius Jul 25 '13 at 08:47
yes ike this "insert into shortdb1.currency_code set currency_code = 'INR', based_on = 'KWD', currency_rate = '200', last_update = '2013-07-25 11:14:26';"; – user475464 Jul 25 '13 at 08:48
Oh, right. Please take a look at this answer: http://stackoverflow.com/a/1307645/1167953 – Andrius Naruševičius Jul 25 '13 at 09:28

score 1 · Answer 3 · answered Jul 25 '13 at 08:59

if you want to send multiple update queries, you can use transaction

$firstQuery = "insert into shortdb1.currency_code set currency_code = 'KWD', based_on = 'KWD', currency_rate = '1.00', last_update = '2013-07-25 11:41:33'";
$secondQuery = "insert into shortdb1.currency set currency_code = 'KWD', language_code = 'EN', currency_name = 'Kuwaiti Dinar';";

try {
    // create transaction
    $conn->beginTransaction();

    // multiple query commands
    $conn->query($firstQuery);
    $conn->query($secondQuery);
    // ect...

    // if we are here, then the above query got passed correctly without exception
    // now we can commit the transaction
    $conn->commit();
} 
catch (Exception $e) {
    // if above commands fails, the db gets a rollback task
    $conn->rollback();
}

That's a good way to use multiple insert/update queries to the database in one sequence.

The power of transactions is that you can create an array of update queries and loop through that array in the above block, instead of multi-lined queries.

if an exception got thrown, it will be stored in $e variable. You can use it to log the error message. Just use $e->getMessage() to get the error message. — KarelG, Jul 25 '13 at 09:07
for example if there is error in the $firstQuery it updates $secondQuery or show both error? — user475464, Jul 25 '13 at 09:18
if the first query fails, then the program continues to the catch-block * and will ignore the commands after the 1st query. If the 2nd query fails, then the code will jump to the catch block **. In the catch block, it will send a rollback command to undo the previously done querytasks. At *, nothing is rollbacked. But at **, the first query will be rollbacked (thus not updated) — KarelG, Jul 25 '13 at 09:21

score 1 · Answer 4 · answered Jul 25 '13 at 09:04

1

I think your queries are badly formed. The SET keyword is used for UPDATE queries.

For an INSERT query it should be in this form:

INSERT INTO TABLE_NAME (column1, column2, column3)  
                VALUES (value1, value2, value3);

answered Jul 25 '13 at 09:04

harryg

23,311
45
125
198

How to update multiple queries as one

4 Answers4