SQL search multiple values in same field

Question

I m building a simple search algorithm and I want to break my string with spaces  and search my database on it  like so    search    Sony TV with FullHD support      search   explode        search     SELECT name FROM Products WHERE name LIKE   search 1   AND name LIKE   search 2   LIMIT 6   Is this possible

User · Accepted Answer

Yes  you can use SQL IN operator to search multiple absolute values   SELECT name FROM products WHERE name IN    Value1    Value2            If you want to use LIKE you will need to use OR instead   SELECT name FROM products WHERE name LIKE   Value1  OR name LIKE   Value2     Using AND  as you tried  requires ALL conditions to be true  using OR requires at least one to be true

User · Answer

This has been partially answered here  MySQL Like multiple values  I advise against       search   explode        search      and input them directly into the SQL query as this makes prone to SQL inject via the search bar  You will have to escape the characters first in case they try something funny like   --  DROP TABLE name       search   str replace            search      But even that is not completely safe  You must try to use SQL prepared statements to be safer  Using the regular expression is much easier to build a function to prepare and create what you want   function makeSQL search pattern  search        search pattern   false        escape the special regex chars      search   str replace             search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   str replace              search        search   explode       search       for   i   0   i  lt  count  search    i              if   i  gt  0  amp  amp   i  lt  count  search                  search pattern                            search pattern     search  i             return search pattern      search pattern   makeSQL search pattern  search    sql query    SELECT name FROM Products WHERE name REGEXP  search LIMIT 6   stmt   pdo- gt prepare  sql query    stmt- gt bindParam   search    search pattern  PDO  PARAM STR    stmt- gt execute      I have not tested this code  but this is what I would do in your case  I hope this helps

User · Answer

Try this  Using UNION   sql        count   0  foreach  search as  text      if  count  gt  0        sql    sql  UNION Select name From myTable WHERE Name LIKE    text       else       sql    sql  Select name From myTable WHERE Name LIKE    text         count        Using WHERE IN   comma separated          implode         search                1   2   3    sql    Select name From myTable WHERE name IN    comma separated

User · Answer

This will works perfectly in both cases  one or multiple fields searching multiple words   Hope this will help someone  Thanks  declare  searchTrm varchar MAX   one two three four    --select value from STRING SPLIT  searchTrm       where trim value  lt  gt    select   from Bols  WHERE EXISTS  SELECT value       FROM STRING SPLIT  searchTrm             WHERE          trim value  lt  gt            and              BolNumber like      value             or UserComment like      value             or RequesterId like      value

[mysql] SQL search multiple values in same field

Examples related to mysql

Examples related to search