SQL Database problem (With searching)

DJMK4 · 10 Apr 2006 at 19:27

see here:

http://www.caldicot-pc-helpdesk.co.uk/fcv/index4.php

as you can see i have a feature on there to search a particular table, if you look at the dvd department, then try and search for the catagory of the film (just type in comedy) it shows no results even though there is items in the database.

I have SQL's fulltext searching feature on, on the following fields: Catagory, title, certificate.

I have worked out what the problem is but not sure how to solve it. When i have one entry with (comedy) it dont work, if i have 2 entries with (comedy) it works and displays both results. If i enter 3 or more entries into the table and try and search for (Comedy) it wont display any results.

Yet if i search for (action) it will find a number of results??

Does anyone know anything about this?

robmiller · 10 Apr 2006 at 19:35

RTFM!

http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

In addition, words that are present in more than 50% of the rows are considered common and do not match.

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more.

Fulltext seems silly for this. Let the user enter their own query for the title and description of the movie, then let them filter that search to a specific category using a drop-down box, generated by just doing something like

Code:

SELECT name
FROM categories

presuming you've actually normalised your database structure.

DJMK4 · 10 Apr 2006 at 19:48

robmiller said:
RTFM!

http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

Fulltext seems silly for this. Let the user enter their own query for the title and description of the movie, then let them filter that search to a specific category using a drop-down box, generated by just doing something like

Code:

SELECT name FROM categories

presuming you've actually normalised your database structure.

Is doing this the only way around it? tbh a quick fix solution will be better as this is part of an assignment that has to be in soon...

Beansprout · 10 Apr 2006 at 20:02

Didn't I tell you all about this approximately 3 weeks ago?

DJMK4 · 10 Apr 2006 at 20:03

Beansprout said:
Didn't I tell you all about this approximately 3 weeks ago?

You told me that i needed more than 50% of an entry for it to display, i didnt realise i couldt have any more?

DJMK4 · 10 Apr 2006 at 20:04

So what i need is:

Search field
Dropdown box (Catagory of film)
Dropdown box (table to search)

?

robmiller · 10 Apr 2006 at 20:05

[Sniper][Wolf] said:
Is doing this the only way around it? tbh a quick fix solution will be better as this is part of an assignment that has to be in soon...

Well, yeah. It's not a "way round it", it's "a better way of doing it".

Beansprout · 10 Apr 2006 at 20:28

[Sniper][Wolf] said:
You told me that i needed more than 50% of an entry for it to display, i didnt realise i couldt have any more?

I told you the 50% part rob quoted and you said you'd read the fulltext page