Page 1 of 1

Unable to make regexp work...

Posted: Wed Nov 05, 2008 9:26 am
by crazycoders
Can't seem to make this work, what did i do wrong?

Code: Select all

 
$createstatement = 'CREATE TABLE `amsp_communiques` ( `id` int(10,3) unsigned NOT NULL auto_increment, `sys_validate` datetime NOT NULL default \'0000-00-00 00:00:00\', `body` text collate utf8_unicode_ci NOT NULL, `resume` varchar(250) collate utf8_unicode_ci NOT NULL default \'\', `title` varchar(150) collate utf8_unicode_ci NOT NULL default \'\', `sys_status` tinyint(3) unsigned NOT NULL default \'0\', PRIMARY KEY (`id`, `added`)) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci';
 
preg_match_all('#primary\s*key\s*\(\s*(`([\w]+[\w\d]*)`[\s,]*)*\)#i', $createstatement, $primarykeydefinition);
print_r($primarykeydefinition);
 
EDIT: The statement is not valid, some information has been changed from the original statement to produce effects to test other regexp, please don't discuss the format of the CREATE STATEMENT as it is effectively not valid in certain points but it should not affect the result of the regexp.

Outputs:

Code: Select all

 
Array
(
    [0] => Array
        (
            [0] => PRIMARY KEY (`id`, `added`)
        )
 
    [1] => Array
        (
            [0] => `added`
        )
 
    [2] => Array
        (
            [0] => added
        )
 
)
 
Expect:

Code: Select all

 
Array
(
    [0] => Array
        (
            [0] => PRIMARY KEY (`id`, `added`)
        )
 
    [1] => Array
        (
            [0] => `id`
            [1] => `added`
        )
 
    [2] => Array
        (
            [0] => id
            [1] => added
        )
 
)
 

Re: Unable to make regexp work...

Posted: Wed Nov 05, 2008 9:40 am
by prometheuzz
By adding a * after your grouping: (...)* the regex engine will only "remember" the last group, which is "added" in your case. If you always have two parameters, you could use a regex like this:

Code: Select all

'#primary\s+key\s*\(`([^`]+)`\s*,\s*`([^`]+)`\)#i'
Or if it can be more than 2 parameters, then you will have to do it in two steps: the first step you match the parameters (`id`, `added`, `another_param`) and the second step is split that previous match.

Re: Unable to make regexp work...

Posted: Wed Nov 05, 2008 9:49 am
by crazycoders
Thanks, that lead me to the correct anwser, i did it in double preg_match calls like this:

Code: Select all

 
$createstatement = 'CREATE TABLE `amsp_communiques` ( `id` int(10,3) unsigned NOT NULL auto_increment, `sys_validate` datetime NOT NULL default \'0000-00-00 00:00:00\', `body` text collate utf8_unicode_ci NOT NULL, `resume` varchar(250) collate utf8_unicode_ci NOT NULL default \'\', `title` varchar(150) collate utf8_unicode_ci NOT NULL default \'\', `sys_status` tinyint(3) unsigned NOT NULL default \'0\', PRIMARY KEY (`id`, `added`)) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci';
preg_match('#primary\s*key\s*\(.*\)#i', $createstatement, $primarykeydefinition);
preg_match_all('#`(\w[\w\d]*)`#', $primarykeydefinition[0], $primarykeydefinition);
print_r($primarykeydefinition);
 
The only problem is that the first matches the double )) at the end of the statement. I don't know if that could be dangerous in a different statement such as one with FKs or Indexes...

I'll have to test that or find a way to isolate the first statement better

Re: Unable to make regexp work...

Posted: Wed Nov 05, 2008 9:55 am
by prometheuzz
No problem. Be careful with those greedy DOT-STAR thingies!
This is a safer approach:

Code: Select all

preg_match('#primary\s*key\s*\([^)]+\)#i', $createstatement, $primarykeydefinition);
which will also omit the second closing parenthesis.