mysql/php search tag engine

Question

I got a database of two motorcycles:

yamaha R1 white 2000 and
yamaha R125 white 2005

When I send query:

SELECT * FROM bikes WHERE title LIKE '%$search%'

Then when I search for "yamaha R125" everything is ok but when you search "yamaha R1" then you will get two results yamaha R1 and yamaha R125.

Title anyone can specify as they want so title can be "yamaha R1 white 2005" but also "excellent condition yamaha R1".

Any idea how to sort it out?

do you want only yamaha R1 as result when you are searching for yamaha R1 ? — Hemantwagh07, Jan 10 '13 at 10:52
yes I want only results with yamaha R1 in that case only one — sieja, Jan 10 '13 at 10:53
it wouldn't work for example let say search: "white yamaha r1" — sieja, Jan 10 '13 at 11:31

score 1 · Answer 1 · answered Jan 10 '13 at 11:12

1

MySQL will not be able to understand as long as you are using LIKE. There are just two solutions:-

1. Either segregate model and color from that single field and keep it in separate field 

2. SELECT * FROM bikes where title like 'yamaha R1%' AND title like '%2000';

answered Jan 10 '13 at 11:12

Neeraj

8,625
18
60
89

that will work if people specific just name model and year of bike but when someone type "brand knew yamaha R1 2010 one owner" it wouldn't work – sieja Jan 10 '13 at 11:16

score 0 · Answer 2 · answered Jan 10 '13 at 10:53

0

why you are using like clause, use title=$search

SELECT * FROM bikes WHERE title='$search'

answered Jan 10 '13 at 10:53

Suhel Meman

3,702
1
18
26

but title looks like that "yahama R1 white 2000" when I type "yamaha R1" not full title just beginning still it's gonna work? – sieja Jan 10 '13 at 10:57

Hemantwagh07 · Answer 3 · 2013-01-10T10:59:54.593

0

Try this SELECT * FROM bikes WHERE title = '$search'

If you want only 1 result

Like clause is used for wildcard search.If you use % sign it search like wild card

Check following link to know know more about String comparison

edited Jan 10 '13 at 10:59

answered Jan 10 '13 at 10:54

Hemantwagh07

1,516
1
15
27

score 0 · Answer 4 · answered Jan 10 '13 at 10:58

the problem here is the $search value:

SELECT * FROM bikes WHERE title LIKE '%$search%'

with this you are trying to seach for titles that are equal to 'ANYTHING+$search+ANYTHING'

since you are searching for %yamaha R1% the results are:

yamaha R1 white 2000, yamaha R125 white 2005

Problem here is: if you use '%search' it won't work, cause you have the 'white 2000' at the end, why don't you set the color and the number in another field of the table and then search it like this:

SELECT * FROM bikes WHERE title LIKE '$search'

This should return all the info of yamaha R1, being one of the fields the color 'white' and another one the number '2000'

Basically title anyone can type as they want so can be "brand knew yamaha R1 2010 one owner" then is more complicated — sieja, Jan 10 '13 at 11:11

score 0 · Answer 5 · edited May 23 '17 at 11:52

0

If these don't help

Match only entire words with LIKE?

mysql SELECT LIKE must match whole words only to the variable

then I am not sure it is possible in basic Mysql without changing schema, user input etc.

If you are on a dedicated server, or have a very good web host, I happen to know that the Sphinx Search engine http://sphinxsearch.com/ can work in the way you require.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 10 '13 at 11:54

CodeMonkey

3,271
25
50

score 0 · Answer 6 · answered Jan 10 '13 at 15:54

0

This is what you want:

SELECT * FROM bikes WHERE  title REGEXP '[[:<:]]$search[[:>:]]' = 1;

answered Jan 10 '13 at 15:54

Amorphous

779
7
27

mysql_query("SELECT * FROM bikes WHERE title REGEXP '[[:<:]]'.$search.'[[:>:]]' = 1;"); gives "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in" – sieja Jan 10 '13 at 19:19

mysql/php search tag engine

6 Answers6