Regex php get mysql insert values

Question

I want to take the values from a sql file with lots of inserts like this:

INSERT INTO "Messages" VALUES(7662,1,7333,'#foxy.giulia/$marcobonetti7;f966f2547124846f','marcobonetti7','marcobonetti7',NULL,X'6DF3228A75EEF69EB124C51BD0CA6EB7F3C35B33DC8062F479B60AC80442C21A','marcobonetti7',1386795144,61,NULL,0,NULL,NULL,NULL,NULL,'Domani durante il giorno sei a casa?',NULL,NULL,NULL,2,NULL,3,4,1,NULL,NULL,NULL,1198575,3063654961,309438692,NULL,NULL,NULL,NULL);

Complete SQL file is provided in this pastebin.

I have made this far preg_match_all('/insert into "([a-zA-Z]+)" values\((.*)[\);]{0}/isu'), but it doesn't take in account \r\n from Values(.*) and it doesn't detect it.

I need the values and the table name from a .sql file. The values can be only an entire string.

Why don't you just import the table into your database and then export what you want? — cmorrissey
– cmorrissey, Commented Feb 14, 2014 at 19:31
because its actual an sqlite file and Im tring to convert to mysql. — B.Petrus
– B.Petrus, Commented Feb 14, 2014 at 20:30
For that, take a look at this blog post for migrating a SQLite dump to a MySQL database. — Lucas Kahlert
– Lucas Kahlert, Commented Feb 14, 2014 at 21:55

Lucas Kahlert · Accepted Answer · 2014-02-14 21:45:56Z

2

This regular expression will match the table name and the whole list of values as string:

preg_match_all('/insert into "([a-zA-Z]+)" values\((.*?)\);/isu', $string, $matches, PREG_SET_ORDER);

After that you could split the values with:

foreach ($matches as $key => $match) {
    $matches[$key][2] = explode(',', $match[2]);
}

edited Feb 14, 2014 at 21:45

answered Feb 14, 2014 at 19:44

Lucas Kahlert

1,2579 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Regex php get mysql insert values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related