CODEWITHSUNDEEP
phpregex
MagePsycho
MagePsycho

Reputation: 2004

Regex for extracting table name and fields from Alter SQL statement

I have a big SQL string, for example:

...
-- sales_address
ALTER TABLE sales_address
  ADD COLUMN `cod_fee` decimal(12,4) DEFAULT NULL,
  ADD COLUMN `base_cod_fee` decimal(12,4) DEFAULT NULL;

-- sales_item
ALTER TABLE sales_item
  ADD COLUMN `is_free` tinyint(4) NOT NULL DEFAULT '0';

-- sales_payment
ALTER TABLE sales_payment
  ADD COLUMN `payment_no` varchar(255) NOT NULL,
  ADD COLUMN `payment_sid` varchar(255) NOT NULL,
  ADD COLUMN `payment_trx` varchar(255) NOT NULL;
...

And want to extract table names and columns as array, for example:

$finalOutput = array(
    'sales_address' => array(
       'cod_fee',
       'base_cod_fee'
    ),

    'sales_item' => array(
       'is_free'
    ),

    'sales_payment' => array(
       'payment_no',
       'payment_sid',
       'payment_trx'
    )
);

May be we need to do the nesting preg_match_all or else. So far I tried to use regex as:

preg_match_all('/what-regex-goes-here/', $SQL, $matches);

Thanks in advance.

Upvotes: 0

Views: 591

Answers (1)

ZiTAL
ZiTAL

Reputation: 3581

<?php
$sql = "
-- sales_address
ALTER TABLE sales_address
  ADD COLUMN `cod_fee` decimal(12,4) DEFAULT NULL,
  ADD COLUMN `base_cod_fee` decimal(12,4) DEFAULT NULL;

-- sales_item
ALTER TABLE sales_item
  ADD COLUMN `is_free` tinyint(4) NOT NULL DEFAULT '0';

-- sales_payment
ALTER TABLE sales_payment
  ADD COLUMN `payment_no` varchar(255) NOT NULL,
  ADD COLUMN `payment_sid` varchar(255) NOT NULL,
  ADD COLUMN `payment_trx` varchar(255) NOT NULL;
...";

// separate tables
$tables = preg_split("/\n\n/", $sql);

$result = array();
foreach($tables as $table)
{
        // get table name
        preg_match("/ALTER TABLE ([^\s]+)\n/", $table, $table_name);
        $result[$table_name[1]] = array();

        // get all columns
        preg_match_all("/ADD COLUMN `[^\s]+`/", $table, $columns);
        foreach($columns[0] as $column)
        {
                preg_match("/ADD COLUMN `([^\s]+)`/", $column, $c);
                $result[$table_name[1]][] = $c[1];
        }
}

print_r($result);

Upvotes: 1

Related Questions