Searching MySQL??

[mark_and_co]

Hi Guys,

I am thinking about developing a search engine which would need to span across 3 tables in one database.
The Website is www.review-music.com, the search facility on this site is fairly limited by searching artist or album. I want to be able to search artist, album, tracks and lyrics in one search.

Therefore if the user typed Eminem My Name Is it would bring up The Slim Shady LP.

Any pointers on how to approach this would be highly grateful.

Mark

[raymie]

If I am understanding your query correctly then what you need to do is have the search predicates on a select which joins the 3 tables together

for example

SELECT * from 
   artist as a, 
   album as b, 
   track as c 
  where a.artist = b.artist 
      and b.album = c.album

once the tables are joined like this (matching the foreign keys) you then can add the other clauses for example

(
      and a.artist like '%Scorp%'
      and b.album like '%First Sting%'
)

Hope that this helps.