CODEWITHSUNDEEP
phpmysqlregex
user1596165
user1596165

Reputation: 191

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.

Upvotes: 0

Views: 5951

Answers (1)

chris85
chris85

Reputation: 23880

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.

Upvotes: 1

Related Questions