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


Subject
Views
Written By
Posted
Help with a search
3017
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.