Mysql REGEXP 'o' OR 'aw'

Question

I'm trying to do a friend search app. So to do that I'm using regular expressions like:

SELECT ... 
 WHERE firstname REGEXP $firstname

And the $firstname variable is a string - for example:

(ch|k)ris

But for some reason it does not work. maybe the ( | ) doesn't work in MySQL?

What can I use instead of that operator to do the same thing?

Answer 1

This:

SELECT * 
  FROM users
 WHERE firstname REGEXP '(ch|k)ris'

...works fine for me on MySQL 5.1.49, using:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

INSERT INTO users 
VALUES (DEFAULT, 'Andrew'), 
       (DEFAULT, 'Chris'), 
       (DEFAULT, 'kris')

In my testing via MySQL Workbench, I get a 1064 error if the regex is not enclosed in single quotes. You'll need to provide information about how this isn't working for you -- is there actually data in the table that this query would return?

Answer 2

in PHP modify your line to this:

$query = "SELECT ... FROM users WHERE id NOT IN($alreadyfriend) AND firstname REGEXP '$firstname'"; //notice the single quotes