Need Search two values in one field Mysql

4 comments

1. Here you go

   SQL Fiddle

   Query:

   SELECT first_name, last_name
   FROM 
     (
       (
         SELECT meta_value AS first_name, id AS a_id 
         FROM wp_usermeta 
         WHERE meta_name = 'first_name'
       ) as a
     )
   JOIN
     (
       (
         SELECT meta_value AS last_name, id AS b_id 
         FROM wp_usermeta 
         WHERE meta_name = 'last_name'
       ) as b
     )
   WHERE a_id = b_id-1
   

   Results:

   | FIRST_NAME | LAST_NAME |
   |------------|-----------|
   |    pritesh |   mahajan |
   |     ritesh |      jain |
   |       john |         a |
   |      Mambo |     Nombo |
   

   With this, you can easily search using the two virtual columns first_name and last_name. You just need to add additional conditions to the WHERE clause. something like this:

     WHERE a_id = b_id-1
     AND first_name LIKE "%tesh%"
     AND last_name LIKE "%jai%"
   

   will produce

   | FIRST_NAME | LAST_NAME |
   |------------|-----------|
   |     ritesh |      jain |
   

   NOTE

   The big assumption I have made here (based on your sample data) is that the related first_name and last_name are always going to have successive id‘s. (two successive records in the table).

   Log in to Reply

2. Can you try this,

    SELECT * FROM `wp_usermeta` WHERE (`meta_key` = 'last_name' OR `meta_key` = 'first_name' ) and (`meta_value` LIKE  '%mahajan%' OR `meta_value` LIKE  '%a%')
   

   Log in to Reply

3. I think that both rows have to share the same key. Maybe it’s ID, or user_id (not shown in your question?). If this is the case, you could use this query to return the IDs you are looking for:

   SELECT ID
   FROM `wp_usermeta`
   WHERE
     (`meta_key` = 'last_name' AND `meta_value` LIKE  '%mahajan%')
     OR (`meta_key` = 'first_name' AND `meta_value` LIKE  '%a%)
   GROUP BY ID
   HAVING COUNT(*)=2
   

   please substitute ID with the actual ID.

   Log in to Reply

4. Schema

   CREATE TABLE wp_usermeta 
       (
        id int auto_increment primary key, 
        meta_name varchar(20), 
        meta_value varchar(30)
       );
   
   INSERT INTO wp_usermeta
   (meta_name, meta_value)
   VALUES
   ('first_name', 'pritesh'),
   ('last_name', 'mahajan'),
   ('first_name', 'ritesh'),
   ('last_name', 'jain'),
   ('first_name', 'john'),
   ('last_name', 'a'),
   ('first_name', 'Mambo'),
   ('last_name', 'Nombo'); 
   

   And Query should be like this….

   SELECT meta_name, meta_value
   FROM wp_usermeta
   WHERE 
   (meta_name = 'last_name' AND meta_value LIKE  '%mahajan%' )
     OR
   (meta_name = 'first_name' AND meta_value LIKE  '%a%'); 
   

   Log in to Reply