user1596165 user1596165 - 3 months ago 17
MySQL Question

Syntax for MySQL REGEXP CONCAT

I'm ultimately trying to make a PDO prepared statement using the REGEXP CONCAT function. In phpmyadmin I can run the following query which returns proper results for my database contents (the 'city' value (Denver, in this case)) will eventually be the bound parameter, but I'm putting it in there explicitly now for development):

SELECT `user_ID` as `ID`
FROM `usermeta`
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP '.*"city";.*s:[0-9]+:"Denver".*')


However, if I try to use the CONCAT function on the last line, the result is an empty set. In other words, MySQL isn't throwing any errors on the query itself, but the correct data isn't being selected:

SELECT `user_ID` as `ID`
FROM `usermeta`
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP CONCAT('\'.*"city";.*s:[0-9]+:"', 'Denver', '".*\''))


I've tried escaping the colon, semicolon and period characters with no luck. Any help is very much appreciated.

Answer

You are adding literal quotes to your expression:

AND (`meta_value` REGEXP CONCAT('\'.*"city";.*s:[0-9]+:"', 'Denver', '".*\''))    
                                  ^                                       ^

Those aren't in your first expression. The quotes in the first expression are encapsulating the SQL string. So:

AND (`meta_value` REGEXP CONCAT('.*"city";.*s:[0-9]+:"', 'Denver', '".*'))

I think would work. You also don't need to use the mysql concat. You could just concatenate the variable into the expression in the binding. Make the SQL:

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP ?)

Then build the binding like:

'.*"city";.*s:[0-9]+:"' . $city . '".*'

The leading and trailing .* are also not necessary. The rule will match without that because you aren't using anchors.