PHP and SQL Parsing

Question

Say I have an array of table DDL queries and I want to get the name of each table being created. What would the best approach be for extracting the table name?

So for example:

$ddl = array(
    'CREATE TABLE tableOne ...',
    'CREATE TABLE tableTwo ...',
    'CREATE TABLE tableThree ...'
);

foreach($ddl as $tableDef) {
    $tableName = [???];
    echo $tableName;
}

That's just a simplified case, but I'm wondering how I would parse out the table name. Is it safe to assume it is whatever comes after "CREATE TABLE "? Are there any special cases I should be aware of?

Martin Jansen · Accepted Answer · 2009-07-02 20:30:04Z

2

MySQL supports CREATE TABLE IF NOT EXISTS foobar, which you will have to take into account. The following code should do the trick:

foreach ($ddl as $tableDef) {
    if (preg_match("/^CREATE\s+(?:TEMPORARY\s+)?TABLE\s+(?:IF NOT EXISTS\s+)?([^\s]+)/i", $tableDef, $matches)) {
        $tableName = $matches[1];
    }
}

answered Jul 2, 2009 at 20:30

Martin Jansen

2761 silver badge7 bronze badges

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

Comments

jmucchiello · Accepted Answer · 2009-07-02 20:29:52Z

1

If you control the array...

$ddl = array(
    'tableOne' => 'CREATE TABLE tableOne ...',
    'tableTwo' => 'CREATE TABLE tableTwo ...',
    'tableThree' => 'CREATE TABLE tableThree ...'
);

foreach ($ddl as $tablename => $sql) { ... }

Or a regex:

foreach ($ddl as $sql) {
    if (preg_match('/CREATE(\s+|\s+TEMPORARY\s+).*\s+TABLE(\s+|\s+IF NOT EXISTS\s+)(\w[\w\d_]*)\s/imU', $sql, $match)) {
        echo $match[3];
    }
}

answered Jul 2, 2009 at 20:29

jmucchiello

19k7 gold badges44 silver badges62 bronze badges

Comments

Mark Biek · Accepted Answer · 2009-07-02 20:30:54Z

0

According to the MySQL docs, the basic form of a CREATE TABLE call is

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

So you'll need to account for the possibility of the word TEMPORARY and the string IF NOT EXISTS. I believe it's also possible for the table name to be back-tick quoted in case it contains any funny characters (I may be wrong about that though).

Here's a first go at a regex:

/CREATE\s+(TEMPORARY\s+)*TABLE\s+(IF NOT EXISTS\s+)*(.*?)\s+/gi

edited Jul 2, 2009 at 20:30

answered Jul 2, 2009 at 20:25

Mark Biek

152k54 gold badges160 silver badges201 bronze badges

Collectives™ on Stack Overflow

PHP and SQL Parsing

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related