MySQL Forums
Forum List  »  Full-Text Search

Help with a search
Posted by: arial
Date: November 29, 2006 03:42PM

I'm having a problem. I have two tables setup for a DVD Database.

One table is called "dvd_data" it contains the DVD title, release year, cast, ect...

The second table is called "stars" it contains the stars full name along with a unique id.

The dvd_data table has a column called "stars" that has text like: ,1,2,3,4,

The stars table looks like:
ID Name
1 John Doe 1
2 John Doe 1
3 John Doe 1
4 John Doe 1

Right now the query I have to search for text is:
SELECT *, ( (1.3 * (MATCH(`title`) AGAINST ('$qstring' IN BOOLEAN MODE))) + (0.6 * (MATCH(`desc`) AGAINST ('$qstring' IN BOOLEAN MODE))) ) AS relevance FROM `dvd_data` WHERE ( MATCH(`title`, `desc`) AGAINST ('$qstring' IN BOOLEAN MODE) ) HAVING relevance > 0 AND `status`='1' ORDER BY relevance DESC LIMIT $start , $limit

However I would like for it to search the stars also. Since the names arent in the same table how could I go about it?

Currently I search for stars like this:

First query: SELECT * FROM `stars` WHERE name LIKE '$_GET[q]%' AND `status`='1'

Then after getting the stars unique id I do this: SELECT * FROM `dvd_data` WHERE `stars` LIKE '%,$r[id],%' AND `status`='1'

Any help is greatly appreciated.

Options: ReplyQuote

Written By
Help with a search
November 29, 2006 03:42PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.