[Resolved] Search string larger than field strings

noobie34

I am using php to search a MySQL table. The search string is "one two". The table tbl_name contains a field called "name" and one row contains the string "one two three four"

The php select statment would be something like:
SELECT * FROM tbl_name WHERE name LIKE "%$keyword%";

Here $keyword = "one two";

But what if the search string is "one two three four" and the field name contains the string "one two" and I want to find out when the field name matches the search string? ie. what should I do when the field contents are smaller than the search string and I want to find out when there is a match?

nemonoman

database content 'one two' will not in any case match search string 'one two three four'.

What you're describing is an OR'ed search.

$searchwords = explode (' ', $searchstring);
foreach($searchwords as $test){
$orstring=" OR name LIKE '%$test%' ";
}

$query="SELECT * FROM mytable WHERE 1 $orstring;"

Will produce something like:
$query="SELECT * FROM mytable WHERE 1 $orstring;"

SELECT *
FROM mytable
WHERE 1
OR name LIKE '%one%'
OR name LIKE '%two%'
OR name LIKE '%three%'

etc...
PS

WHERE 1
is a place holder search clause that is always true, so you can start adding OR clauses.

Without it, it gets a little harder, as

WHERE ## Note missing 1 <<
OR name LIKE '%one%'

Will fail

xblue

hi noobie34,

the "one OR two OR three OR four" approach may be what you actually wanted - it would return results that contain one or more of the keywords, which is probably quite a common task -, but I think it was not exactly what you descibed.

If you want to find "one two" with the keyword "one two three four" and "zero one two" but not with "one five" (so, only if the database content is an exact substring of the keyword), your query maybe something like

SELECT * FROM tbl_name WHERE '$keyword' LIKE CONCAT('%', name, '%')

noobie34

I can explain with an example. Say, in Google Adwords, as an advertiser I put in the keywords "computer USA". When a user searches "computer dealer USA" or "computer software USA" my ad shows up, but not if the user searches "computer dealers Canada"

Here the entry in the database table is "computer USA" and it is smaller than "computer dealer USA" or "computer software USA"

nemonoman

If you are trying to replicate a google-type weighted search, and you have to ask this question, you are officially out of your depth. Swim in to shore and read up on mysql fulltext searches:

You're in for exciting times if you try to implement this, my friend.

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

noobie34

Ah, I should have realised the solution was not simple.

Thanks for pointing me in the right direction.

Originally posted by nemonoman
If you are trying to replicate a google-type weighted search, and you have to ask this question, you are officially out of your depth. Swim in to shore and read up on mysql fulltext searches:

You're in for exciting times if you try to implement this, my friend.

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html