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: http://stackoverflow.com/questions/24423260/split-sql-statements-in-php-on-semicolons-but-not-inside-quotes — edd, Jan 16 '15 at 10:53
Possible duplicate of [split sql statements in php on semicolons (but not inside quotes)](http://stackoverflow.com/questions/24423260/split-sql-statements-in-php-on-semicolons-but-not-inside-quotes) — mickmackusa, Mar 28 '17 at 17:15

KyleK · Answer 1 · 2015-01-14T09:39:55.660

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 '15 at 09:39

answered Jan 14 '15 at 08:09

KyleK

4,643
17
33

Yes I have already edited that with ;\n and here is a second complete solution. – KyleK Jan 14 '15 at 08:25
Code fixed, next time give the error message too when a code is failing. Plese less exclamation points. – KyleK Jan 14 '15 at 09:41
This answer may help: https://stackoverflow.com/a/75439362/12296332 – emrdev Feb 13 '23 at 18:26

score 1 · Answer 2 · answered Jan 14 '15 at 08:09

1

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

answered Jan 14 '15 at 08:09

onegun

803
1
10
27

I just see the problem: queries contain ; "insert; row1;" so it does not do the trick. – KyleK Jan 14 '15 at 08:18
is this query always in this format which means one create table then the rest are insert? – onegun Jan 14 '15 at 09:33

score 1 · Answer 3 · answered Jan 14 '15 at 08:12

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

Php split SQL in separate queries

3 Answers3