LIKE/Wildcard trouble

[Walle]

Hi

How do I use LIKE correct, If I for example have table rows as

Row1: Apple iPod Nano 8GB
Row2: Apple iPod Touch 32GB

then I want get row2 only when submit ipod 32 , ipod t, ipod touch etc.

LIKE '%$q%' works with
ipod t
ipod touch
ipod touch 32

but with only ipod 32 doesn’t work.

How to fix this?

[BIOSTALL]

How about something like so:

$query = "SELECT * FROM`products` WHERE `name` LIKE '%".str_replace(" ", "%", $q)."%'";

Does that solve it?

[bradgrafelman]

Using 'LIKE' will only get you so far; if you really watch to search the database, you should ditch 'LIKE' and instead look into using full-text searching.

[Walle]

BIOSTALL;10968722 wrote:

How about something like so:

$query = "SELECT * FROM`products` WHERE `name` LIKE '%".str_replace(" ", "%", $q)."%'";

Does that solve it?

It work, thanks

[Walle]

bradgrafelman;10968754 wrote:

Using 'LIKE' will only get you so far; if you really watch to search the database, you should ditch 'LIKE' and instead look into using full-text searching.

as I understand biggest different with full-text searching is that is faster when handle huge table? I tried below but have same problem, for example search ipod 32 , ipod t 32, etc doesn’t work with table rows as

Row1: Apple iPod Nano 8GB
Row2: Apple iPod Touch 32GB

ipod touch, ipod touch 32gb work

I tried with this

$sql = "SELECT * FROM testtable1 WHERE MATCH(column1) AGAINST('+\"$q\"' IN BOOLEAN MODE)";

[bradgrafelman]

Why add the +"..." to the query? Just search using the query string itself (e.g. "ipod touch").

[Walle]

Well that’s not fix the problem, believe me, I tested lots and even like that you show. You can search "ipod touch", "ipod touch 32gb", but "ipod 32gb" won’t work and give no result. How do you mean this can solves with Full-text?

More I read about Full-text more I understand difficult people have with multiple-word and to search part of word. Also word less than 3 character doesn’t work, for example even if using * and search for "8gb" won’t work. Default is 3 or in my case seem to be less than 4 as default. There is some setting for it and not big deal but problem is search multiple words and part of words.

In this case "ipod touch" and "ipod touch 32gb" works fine, but "ipod t", "ipod touc" or "ipod 32gb" doesn’t work.

[sneakyimp]

I'm not sure because you haven't clearly defined your table, but it looks like you might have "ipod" and "32" in different fields in your database.

Generally speaking, to create a useful search feature from a plain old text input string, you generally have to break up the input string to determine what the keywords are and then construct an elaborate query to look in a variety of database columns for each of the keywords entered. The queries I've seen usually end up having a lot of 'fieldX LIKE '%$keyword1%' OR fieldX LIKE '%$keyword2%' and so on.

An alternative might be to use google custom search on your site.

[Walle]

It’s only a test table, I just testing and learn. In this case it is only in same column, ipod and 32 is on same title.

Row1: Apple iPod Nano 8GB
Row2: Apple iPod Touch 32GB

I got it work with using "IN BOOLEAN MODE" and this str_replace

$q = '+'.str_replace(' ', '* +', $q).'* ';

However, I’m not sure how god this is, it work as I want now (like I got with LIKE), in this case I get row2 only if I search for ipod t, ipod 32, apple t, apple 32 etc.

But, I noticed if I search for example "ipod touch blabla", "ipod blabla", "ipod blabla 32" then I don’t get any result. Because "blabla" not exist in the title or because in str_replace I have + before and *after all word.

Example if I search for "ipod touch blabla" string looks like "+ipod +touch +blabla", and because "+blabla" not exist I don’t get any result.

I don’t know how to fix this any idea?

[sneakyimp]

You are not being very clear about your database structure so it's hard to comment. Sounds like you are searching a single column in a single database table.

The problem you are having is that the logic you are creating requires all the words in your input string to be present. I haven't seen your PHP code so it's hard to comment beyond that.

In order to take an input string and find all matching records that match any of the words, you could try IN NATURAL LANGUAGE MODE instead of IN BOOLEAN MODE. Read the docs:
http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

[Walle]

Well, I don’t know, sneakyimp, and I’m sorry for my bad English, but did you read the thread, post #5, there it is, all your questions. I’m not good in english, as you allready for sure know but I’m do my best.

instead of (from post #5):

$sql = "SELECT * FROM testtable1 WHERE MATCH(column1) AGAINST('+\"$q\"' IN BOOLEAN MODE)";

It now (from post #9) looks

$q = '+'.str_replace(' ', '* +', $q).'* ';
$sql = "SELECT * FROM testtable1 WHERE MATCH(column1) AGAINST('$q' IN BOOLEAN MODE)";

I will read NATURAL LANGUAGE MODE ( instead of IN BOOLEAN MODE ) docs.

[sneakyimp]

What is the point of the str_replace statement?

[Walle]

Don’t know, sneakyimp, just testing and hanging around, I like PHP and it fun to play with, but it solved what I tried to do from beginning, but later discovered problems with it. It works good but if some word doesn’t exist in db, for example with row below

Row1: Apple iPod Nano 8GB
Row2: Apple iPod Touch 32GB

and you search for example apple (some word does not exist in db) 32gb then it will return nothing. Or for example ipod touch 32GB (some word does not exist in db).

soon some word does not exist in db doesn’t exist in a search string returns nothing.

[sneakyimp]

Try NATURAL LANGUAGE MODE without the str_replace and also try IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION.

NOTE that the natural language mode stuff might not be available in earlier versions of mysql. I think it was introduced in MySQL 5.1. Come to think of it, you haven't even said you were using MySQL.

If it doesn't work, you need to think about what your query is doing.

taking a phrase like this:
"mysql 32GB ipod"

and turning it into a pattern-matching expression similar to this:
"mysql32GBipod" doesn't really do what you want. It is going to require all of the words to be in any row for it to match. You would have to write some kind to split up your query into invidiual words and make a query with a whole bunch of OR statements and stuff. It's a total pain.