2

What I'm trying to do is to display all possible variations in the table on the screen. If $search = str, to search in the table for every word which contains 'str' in it. I tried a lot of variations to do it, but without success yet. What is wrong, and can i use something like "SELECT * FROM table WHERE * LIKE '[[:<:]]\"$search\"[[:>:]]' " ? 

$string = "SELECT * FROM table

WHERE (column1 = '[[:<:]]\"$search\"[[:>:]]') 
OR (column2 = '[[:<:]]\"$search\"[[:>:]]')
OR (column3 = '[[:<:]]\"$search\"[[:>:]]') 
OR (column4 = '[[:<:]]\"$search\"[[:>:]]')
OR (column6 = '[[:<:]]\"$search\"[[:>:]]')  
OR (column7 = '[[:<:]]\"$search\"[[:>:]]')";

Question: find all words that contain 'str'

Elyor
  • 5,396
  • 8
  • 48
  • 76
Bogdan Bogdanov
  • 386
  • 1
  • 9
  • What is your question? – Jay Blanchard Aug 10 '15 at 15:36
  • What is the result of your posted code? – kittykittybangbang Aug 10 '15 at 15:42
  • 2
    This smells a bit "search enginey" to me ... and if they were easy Google wouldn't be gazillionaires! Take a look at Solr : http://lucene.apache.org/solr/ – CD001 Aug 10 '15 at 15:48
  • @kittykittybangbang it is showing nothing. – Bogdan Bogdanov Aug 10 '15 at 15:49
  • @CD001 yea actually search engine is what i'm trying to do, but a simple one xax – Bogdan Bogdanov Aug 10 '15 at 15:51
  • full text search one column, or solr http://stackoverflow.com/a/31127600/1816093 or http://stackoverflow.com/a/30677347/1816093 – Drew Aug 10 '15 at 15:53
  • 2
    The simplest way to do it, with MySQL, is to use a MYISAM table with a FULLTEXT index on it to retrieve the results by relevance - then parse those results down to the values you want; weirdly it's better to have MySQL fetch *more* than you need initially and then have PHP filter it rather than made a really complex SQL query - it works out quicker. Take a look at Natural Language Searches in MySQL : http://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html – CD001 Aug 10 '15 at 15:54
  • it may be simple. but getting results you want aren't. rollup up your sleeves with fulltext, stop words, ranking, and then go to solr anyway – Drew Aug 10 '15 at 16:00

2 Answers2

1

EDIT

First, get all the columns in database who contains $strlike this :

"SELECT * FROM your_table WHERE UPPER(your_field) LIKE UPPER('%" . $str . "%')";

After,

Parse this results to find the $str in words.

Like this :

$explode = explode($results);

Then you use strpos(); and strstr(); combined to match $str in your array

Some function like this :

function getMultiPos($haystack, $needles, $sensitive=true, $offset=0){ foreach($needles as $needle) { $result[$needle] = ($sensitive) ? strpos($haystack, $needle, $offset) : stripos($haystack, $needle, $offset); } return $result; }

Source

wilson
  • 334
  • 1
  • 15
  • The question is to find all **words** that contain the substring - not all columns – CD001 Aug 10 '15 at 15:45
  • So you get ALL the columns who contains THIS word and after we parse the result to extract all word who contains THIS word. I edit my answer thx @CD001 – wilson Aug 10 '15 at 15:49
0

Try to use column1 LIKE '%str%' as condition

chaintng
  • 1,325
  • 3
  • 14
  • 26