Remove ReGex matches from string php-CodePudding

I am trying to remove all of the CONSTRAINTS from a SHOW CREATE query in MySQL.

Here is and example of a query (I've added line breaks for ease of reading):

CREATE TABLE `x2_action_meta_data` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`actionId` int(10) unsigned NOT NULL, 
`eventSubtype` varchar(100) DEFAULT NULL, 
`eventStatus` varchar(100) DEFAULT NULL, 
`etag` varchar(255) DEFAULT NULL, 
`remoteCalendarUrl` varchar(255) DEFAULT NULL, 
`remoteSource` int(11) DEFAULT '0', 
`emailImapUid` int(10) unsigned DEFAULT NULL, 
`emailInboxId` int(10) unsigned DEFAULT NULL, 
`emailUidValidity` int(10) unsigned DEFAULT NULL, 
`emailFolderName` varchar(250) DEFAULT NULL, 
PRIMARY KEY (`id`), U
NIQUE KEY `emailImapUid` (
`emailImapUid`,`emailInboxId`,`emailFolderName`,`emailUidValidity`
), KEY `actionId` (`actionId`)
, CONSTRAINT `x2_action_meta_data_ibfk_1` 
FOREIGN KEY (`actionId`) REFERENCES `x2_actions` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE ) 
ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8

The part that I want to remove is:

, CONSTRAINT `x2_action_meta_data_ibfk_1` 
FOREIGN KEY (`actionId`) REFERENCES `x2_actions` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE )

I've tested this using RegExer and it seems to work: http://regexr.com/6fj2p

Here is my PHP code to remove it (assume $this_table is already populated with my query:

$this_table = preg_replace("/[ ]*,[ ]CONSTRAINT[ ] .*?(?=[ ]ENGINE)/", "", $this_table);

It is not removing the text from my query. Any idea what I am doing wrong?

CodePudding user response：

Your regex works fine on the original query, before you added line breaks. But I think you should keep the last closing parenthesis ) just before ENGINE, because it matches with the very first CREATE TABLE ( opening parenthesis. To achieve that and make the regex work with or without added line breaks, try this:

$this_table = preg_replace("/,\s CONSTRAINT\s .*?(?=\)\s*ENGINE)/s", "", $this_table);

The s pattern modifier at the end of the regex ensures that a dot in the pattern also matches newlines.

CodePudding user response：

Thanks everyone. I have determined that the code is good with a couple of exceptions.

I have replaced the "`" coming from the query with "'".
I think there is something going on with my Docker Container that is running the code because it works when I test it with PHPFiddle.