0

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
user1596165
  • 191
  • 2
  • 13
  • 1
    done any basic debugging, like `select concat(...)` to see the pattern you are generating? – Marc B Sep 07 '16 at 14:26
  • Marc B: Yes, when I send just the select concat statement I get this: '.*"city";.*s:[0-9]+:"Denver".*' I'm just trying to get the concat function working correctly. In the end, 'Denver' will be replaced with a PDO parameter (? or :city). – user1596165 Sep 07 '16 at 14:28
  • using having could help I guess – Bobot Sep 07 '16 at 14:29
  • Give us an example of what should match. – Rick James Sep 09 '16 at 23:08

1 Answers1

1

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.

chris85
  • 23,846
  • 7
  • 34
  • 51
  • Thanks, Chris. You were right about the quotes. I'm using the concat function because I was under the impression that placeholders couldn't be enclosed in quotes. (http://stackoverflow.com/a/39171920/1596165) In production, the statement would be like concat('.*"city";.*s:[0-9]+:"', ?, '".*') Do you agree with this? – user1596165 Sep 07 '16 at 15:28
  • You can not quote the placeholder, that is correct. You can concatenate at the PHP level though where you tell the PDO what to bind. Bind the whole expression with the value and it should work. Note the last query, `AND (\`meta_value\` REGEXP ?)` that is how the placeholder should be written. Then you bind `'.*"city";.*s:[0-9]+:"' . $city . '".*'` you could do `execute(array('.*"city";.*s:[0-9]+:"' . $city . '".*'))` or use one of the other binding methods. – chris85 Sep 07 '16 at 15:30