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?

Answer 1

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

Answer 2

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];
    }
}

Answer 3

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];
    }
}