complex sql query using two tables and regexp

Question

I need some help for a complex query. I'm using autocomplete jquery-ui for search some users and php+mysql to search.

Users can register with his first_name, last_name and email (requiered fields) on a wordpress/buddypress page.

so, is easy search with regexp on an example that i find on stackoverflow, but i want a make a complex query to search by first_name or last_name or email.

first_name and email are in wp_users table and last_name is in wp_bp_xprofile_data table. i don't want make a custom meta to join names and emails so,

To obtain the first_name and email, the query is:

SELECT * FROM wp_users WHERE display_name REGEXP '^$param' OR user_email REGEXP '^$param'

and to obtain de last_name, the query is:

SELECT value FROM wp_bp_xprofile_data WHERE field_id = 5 AND user_id = ".$id."

(value is last_name's result and $id=wp_users.ID)

How can a mix this queries?
if we see this like one table it would be like :selec * from users where first_name=regexp or last_name=regexp or email=regexp (basically), but by the complex structure of wordpress/buddypress i can't do this. please help!!!

eg...3 users, the name of the first user is oscar, the last name of the second user is osbourne, and the email of the third user is [email protected], if the search starts with 'o', the query will find this 3 users.

PS: i forgot but i need to return the id, first name, last name and email.

Bonus: i'm using the regexp ^$params to find the string at the begin characters. what regexp is better to find this on the everywhere results?

Is there something preventing you from joining the two tables? — invertedSpear
– invertedSpear, Commented Aug 9, 2012 at 23:54

Mike Aski · Accepted Answer · 2012-09-11 15:44:26Z

1

Well, the right answer is:

SELECT u.ID, u.display_name, u.user_email, d.value
FROM wp_users u, wp_bp_xprofile_data d
WHERE
(
  u.ID IN (
        SELECT ID
        FROM wp_users
        WHERE display_name REGEXP '^$param'
  )
  OR u.ID IN (
        SELECT user_id as ID2
        FROM wp_bp_xprofile_data
        WHERE field_id = 5 AND value REGEXP '^$param'
  )
)
AND (u.ID=d.user_id AND field_id=5)

Thanks for your time

edited Sep 11, 2012 at 15:44

Mike Aski

9,2364 gold badges48 silver badges66 bronze badges

answered Aug 13, 2012 at 16:37

user1588888

112 bronze badges

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

Comments

Barmar · Accepted Answer · 2012-08-09 23:59:19Z

0

SELECT * FROM wp_users
WHERE display_name REGEXP '^$param'
   OR user_email REGEXP '^$param'
   OR concat('.',id,'.') IN 
      (SELECT user_id FROM wp_bp_profile
       WHERE field_id = 5 AND value REGEXP '^$param')

answered Aug 9, 2012 at 23:59

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

user1588888 Over a year ago

i forgot but i need to return the id, first name, last name and email. this solution don't work for last names

Collectives™ on Stack Overflow

complex sql query using two tables and regexp

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related