0

I have a column (attr) in mysql database with values like this:

   1- apples, red, big, UK,
   2- Green, apples, big, UK, 
    ETC ETC

Its basically a string of words and they can be in any order as you can see above.

I want to search inside that column using any given string. The strings can look like this:

UK, apples, Red, big, 
apples, UK, big, green, 
etc etc

what i need to do is to look into that column and display the results ONLY if the words given in the string matches the exact same words inside that column.

I've tried using this code:

$sql="SELECT * FROM `product_details` WHERE '$list' LIKE CONCAT('%', attr ,'%')";

where the $list is the given strings. like: UK, apples, Red, big,

as you can see, the words exist in the database but they are in different ORDER and that is why the PHP doesn't show any result!

Could someone please advise on this issue?

H.HISTORY
  • 520
  • 9
  • 28
  • That's pretty bad database design, you are just experiencing the tip of the problems you will be running into. Create a separate table for the attributes, add every attribute as a separate entry. – Gerald Schneider Oct 05 '15 at 13:59
  • @GeraldSchneider lol, that is exactly how I started and now after 2 days I ended up doing it this way... this is the only way I can do this as there are so many other factors coming into action which is out of the scope of this question and STO. – H.HISTORY Oct 05 '15 at 14:02

1 Answers1

0

Try to make an array of this

UK, apples, Red, big,

prepare condition in PHP and than put this data/words like in that topic:

Is there a combination of "LIKE" and "IN" in SQL?

Community
  • 1
  • 1
Michal Kómoch
  • 114
  • 5
  • I'm worried that if i make an array of the strings, it will make the search function slow because there are 1000's of columns to go through at once. – H.HISTORY Oct 05 '15 at 13:42
  • A ok you did not wrote about it. But you mean columns or rows? – Michal Kómoch Oct 05 '15 at 13:44
  • i mean column. the attr is a column. – H.HISTORY Oct 05 '15 at 13:47
  • Hmmm... In my opinion using full-text search field type is the best way to get the data you are interested in. This field type is much more efficient than standard "LIKE" command – Michal Kómoch Oct 05 '15 at 13:51
  • would full text search look int the column and find results even if they are in different order? – H.HISTORY Oct 05 '15 at 13:59
  • I think no. But I have other idea. If you can (SAVE/UPDATE) data alphabetically(words) in your DB and than prepare SQL condition in PHP alphabetically too. Than it should work. – Michal Kómoch Oct 05 '15 at 14:05