CODEWITHSUNDEEP
phpmysql
user475464
user475464

Reputation: 1763

How to update multiple queries as one

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

Upvotes: 1

Views: 85

Answers (4)

harryg
harryg

Reputation: 24077

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);

Upvotes: 1

KarelG
KarelG

Reputation: 5244

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.

Upvotes: 1

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

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);

Upvotes: 1

BLaZuRE
BLaZuRE

Reputation: 2406

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'";

Upvotes: 1

Related Questions