Php split SQL in separate queries

Question

I want to split SQL in separate queries... but still does not work

$string = 'CREATE TABLE `articles` (
`id` int(9) auto_increment,
`context` text,
PRIMARY KEY (`id`)
) DEFAULT CHARSET='utf8' COLLATE='utf8_general_ci';

INSERT INTO `articles` VALUES (1, "insert; row1;"),
(2, "update; row2;")';


preg_match_all("/([^;\"]*?(\".*?\")?)*?;\s*/U", $string, $result);
print_r($result[0]);

Problem solved. Here is the link: stackoverflow.com/questions/24423260/… — edd
– edd, Commented Jan 16, 2015 at 10:53
Possible duplicate of split sql statements in php on semicolons (but not inside quotes) — mickmackusa
– mickmackusa ♦, Commented Mar 28, 2017 at 17:15

KyleK · Accepted Answer · 2015-01-14 09:39:55Z

2

Use explode:

$queries = explode(";\n", $string);

Or you can pre-filter the strings:

$strings = array();
function capture($match) {
    global $strings;
    $strings[] = $match[0];
    return '<<<' . count($strings) . '>>>';
}
function recopy($string) {
    global $strings;
    foreach($strings as $key => $value) {
        $string = str_replace('<<<' . $key . '>>>', $value, $string);
    }
    return $string;
}
$string = preg_replace_callback('#([\'"]).*(?<!\\\\)(?:\\\\\\\\)*\\1#isU', 'capture', $string);
$queries = array_map('recopy', explode(";", $string));

And escape ' with \' as says Rasmus.

edited Jan 14, 2015 at 9:39

answered Jan 14, 2015 at 8:09

KyleK

5,16920 silver badges35 bronze badges

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

3 Comments

KyleK Over a year ago

Yes I have already edited that with ;\n and here is a second complete solution.

KyleK Over a year ago

Code fixed, next time give the error message too when a code is failing. Plese less exclamation points.

emomaliev Over a year ago

This answer may help: stackoverflow.com/a/75439362/12296332

onegun · Accepted Answer · 2015-01-14 08:09:25Z

1

I think you can use explode(";",$string) or find the ; position then substr()

answered Jan 14, 2015 at 8:09

onegun

8011 gold badge11 silver badges28 bronze badges

2 Comments

KyleK Over a year ago

I just see the problem: queries contain ; "insert; row1;" so it does not do the trick.

onegun Over a year ago

is this query always in this format which means one create table then the rest are insert?

Epodax · Accepted Answer · 2015-01-14 08:12:04Z

1

Another issue is that you are using the same ' for both php and the query, instead of wrapping the query in ' try using " instead.

Example: CHARSET='utf8' <-- is using the same sign to tell sql that it's text, but you are allready using that sign to build your query

answered Jan 14, 2015 at 8:12

Epodax

1,8284 gold badges29 silver badges34 bronze badges

Collectives™ on Stack Overflow

Php split SQL in separate queries

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related