Search results from MySQL DB.

[joecooper]

Hi,

 

I'm having some trouble with my search feature on my site. Well my whole site is basically a search engine.

 

I have 2 main fields in my database which need to be searched, but in correct order. They are "SongName" and "SongDesc".

 

Say for example a user searches "Like Toy Soliders" (Eminem song), I would need the script to prioritze the results in order of the amount of words, but in the first field, then the 2nd.

 

Like this:

 

All 3 words in "SongName"

Any 2 words in "SongName"

Any Word in "SongName"

All 3 words in "SongDesc"

Any 2 words in "SongDesc"

Any Word in "SongDesc"

 

Matches would also need to be the whole word, not match "it" to "ditch" for example.

 

Thanks!

 

Joe

 

[Barand]

You need to look up FULL TEXT options in MySQL

[joecooper]

You need to look up FULL TEXT options in MySQL

 

I tried to use this statement:

 

$query = "SELECT * FROM urls WHERE MATCH(file, url) AGAINST('+eminem' IN BOOLEAN MODE);";

 

but got this instead:

 

 

The used table type doesn't support FULLTEXT indexes

[joecooper]

I managed to fix the issue with the table type. The results are displaying better, but not in a good enough order.

 

For example, searching "Summer of 69" will display "Bryan Adams - Summer of 69.mp3", but only in like the 60th position. at the top it displays:

 

Jemini Featuring Eddie Law.._Sweet Sweet Summer.mp3
Motels - Suddenly Last Summer.mp3
Dragonette-Our-Summer-Skeet™-Extended-Version.MP3.mp3
Tiga-What-You-Need-Midnight-Conspiracy-Summer-of-87-Remix.mp3
m-flo ft. ryohei - summer time love.mp3
Theme from A Summer Place.mp3

..... and so on

 

 

 

Edit:

 

As I think now my issue isn't about making a search, rather than getting help with the one I have made myself, I will create a new post to stop confusion

Edited April 2, 2013 by joecooper