MySQL Regular Expressions fail in php script

Question

If I run the following query in MySQL Workbench, or the mysql terminal, it works just fine:

SELECT
  `P`.`human_name` AS `permissionName`,
  IF(`PD`.`descriptor_text` REGEXP '.*role\\((-4,)?-3.*',
    (SELECT group_concat(`id`) FROM `users`),
    IF(`PD`.`descriptor_text` REGEXP '.*user\\([0-9,]+)$',
      SUBSTR(
        `PD`.`descriptor_text`,
        INSTR(`PD`.`descriptor_text`, 'user(') + 5,
        LENGTH(`PD`.`descriptor_text`) - INSTR(`PD`.`descriptor_text`, 'user(') - 5
      ),
      NULL
    )
  ) AS `user_ids`
FROM
  `documents` AS `D`
INNER JOIN `permission_lookups` AS `PL`  ON `D`.`permission_lookup_id` = `PL`.`id`
INNER JOIN `permission_lookup_assignments` AS `PLA` ON `PL`.`id` = `PLA`.`permission_lookup_id`
INNER JOIN `permission_descriptors` AS `PD`  ON `PLA`.`permission_descriptor_id` = `PD`.`id`
LEFT JOIN `permission_descriptor_roles` AS `PDR` ON `PDR`.`descriptor_id` = `PD`.`id`
LEFT JOIN `permissions` AS `P`   ON `PLA`.`permission_id` = `P`.`id`
WHERE `D`.`id` = 74

But as soon as I put it into a php mysql_query() it fails with the error: Got error 'parentheses not balanced' from regexp

My PHP code is as follows:

$permSQL = "
SELECT
  `P`.`human_name` AS `permissionName`,
  IF(`PD`.`descriptor_text` REGEXP '.*role\\((-4,)?-3.*',
    (SELECT group_concat(`id`) FROM `users`),
    IF(`PD`.`descriptor_text` REGEXP '.*user\\([0-9,]+)$',
      SUBSTR(
        `PD`.`descriptor_text`,
        INSTR(`PD`.`descriptor_text`, 'user(') + 5,
        LENGTH(`PD`.`descriptor_text`) - INSTR(`PD`.`descriptor_text`, 'user(') - 5
      ),
      NULL
    )
  ) AS `user_ids`
FROM
  `documents` AS `D`
INNER JOIN `permission_lookups` AS `PL`  ON `D`.`permission_lookup_id` = `PL`.`id`
INNER JOIN `permission_lookup_assignments` AS `PLA` ON `PL`.`id` = `PLA`.`permission_lookup_id`
INNER JOIN `permission_descriptors` AS `PD`  ON `PLA`.`permission_descriptor_id` = `PD`.`id`
LEFT JOIN `permission_descriptor_roles` AS `PDR` ON `PDR`.`descriptor_id` = `PD`.`id`
LEFT JOIN `permissions` AS `P`   ON `PLA`.`permission_id` = `P`.`id`
WHERE `D`.`id` = ".$argv[1];

$permQry = mysql_query($permSQL) or die('MySQL Error: ' . mysql_error());
while ($row = mysql_fetch_object($permSQL)) {
  echo $row->user_ids;
}

What would cause the query to work fine with anything other than PHP?

Aillyn · Accepted Answer · 2011-10-06 16:32:28Z

2

That is because PHP understands \\ as a escape sequence, converting that to a single backslash \. To print two literal backlashes, you'll need four:

$permSQL = "
SELECT
  `P`.`human_name` AS `permissionName`,
  IF(`PD`.`descriptor_text` REGEXP '.*role\\\\((-4,)?-3.*',
    (SELECT group_concat(`id`) FROM `users`),
    IF(`PD`.`descriptor_text` REGEXP '.*user\\\\([0-9,]+)$',

answered Oct 6, 2011 at 16:32

Aillyn

23.9k24 gold badges63 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

MySQL Regular Expressions fail in php script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related