How to run several queries in a single query?

Question

DELETE FROM Table WHERE TableR = '1';
INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '1', 'test1');
INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '2', 'test2');
INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '3', 'test3');

For connect we use code:

$mysqli = new mysqli($this->MysqlHost,$this->MysqlUser,$this->MysqlPassword,$this->MysqlDatabase);
$result = $mysqli->query("SET NAMES utf8");
$result = $mysqli->query("set character_set_client='utf8'");
$result = $mysqli->query("set collation_connection='utf8_general_ci'");
$result = $mysqli->query($query);

Now we need use several query for run all query.

But i would be like run only one query, ie:

$query = "
        DELETE FROM Table WHERE TableR = '1';
        INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '1', 'test1');
        INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '2', 'test2');
        INSERT INTO Table (TableR, Seet, Keyword) VALUES ('1', '3', 'test3');
    ";

Tell me please how to run several queries in a single query ?

@PeterMmm we use mysqli..

user2881809
– user2881809

2014-07-01 13:07:38 +00:00
Commented Jul 1, 2014 at 13:07 — user2881809
– user2881809, Commented Jul 1, 2014 at 13:07
@Kermani why nee use left joing anf other ??

user2881809
– user2881809

2014-07-01 13:08:13 +00:00
Commented Jul 1, 2014 at 13:08 — user2881809
– user2881809, Commented Jul 1, 2014 at 13:08

Leo Loki · Accepted Answer · 2014-07-01 13:23:35Z

1

$result = $mysqli->multi_query($query);

Enjoy!

answered Jul 1, 2014 at 13:23

Leo Loki

2,5956 gold badges26 silver badges31 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:30:01Z

1

With multiquery?

You can check the following documentation:

http://php.net/manual/en/mysqli.multi-query.php

If you are trying to insert multiple rows, you can check:

Batch insertion of data to MySQL database using php

(look for LOAD DATA INFILE solution...)

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jul 1, 2014 at 13:07

lpg

4,9371 gold badge18 silver badges16 bronze badges

Comments

jlgsoftware · Accepted Answer · 2014-07-01 13:37:51Z

0

I hope this help you: http://www.php.net/manual/en/mysqli.quickstart.multiple-statement.php

If you use it, be careful about security.

Example #1 Multiple Statements

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$sql = "SELECT COUNT(*) AS _num FROM test; ";
$sql.= "INSERT INTO test(id) VALUES (1); ";
$sql.= "SELECT COUNT(*) AS _num FROM test; ";

if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

output:

array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "0"
  }
}
array(1) {
  [0]=>
  array(1) {
    ["_num"]=>
    string(1) "1"
  }
}

edited Jul 1, 2014 at 13:37

answered Jul 1, 2014 at 13:09

jlgsoftware

16810 bronze badges

1 Comment

Antonio Over a year ago

Please summarize the content of the URL in your answer, so if the link goes offline the answer will still be useful.

Collectives™ on Stack Overflow

How to run several queries in a single query?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related