MySQL Search Over Multiple Tables

Question

I'm in the midsts of constructing some database tables, but a possible search issue has just come to mind.

The two tables in question are Genres, a 2 column table holding a list of music genres identified by an ID field, i.e. 1 = Dance, 2 = Rock, and so on. And a Music table, a multi column table with Title, Artist, and Genre_ID fields. And yes you've guest it, Genre_ID refers to the ID of the Genre table.

My question is, if I have a search box on the site powered by PHP, and that search box queries the key fields, so Title, Artist, and Genre to yeld the best result, how can I get that to function correctly in a search, when the Genre name itself is in a separate table, and not in the Music table.

An example search would be, "rock music by ACDC".

if you're doing that kind of natural free-form search, you'd better be using a fulltext index. and since indexes can't span across tables, you'll have to conduct TWO separate fulltext searches and `union` the results. — Marc B, May 15 '14 at 21:53
Join the two tables and then search, or create a view that joins them and search the view. — developerwjk, May 15 '14 at 21:56
Join seems to be the way, possibly using view as well. Both Santhos and Bill's answers below put me on the right track. But should I create a view or just search a joined table? And do either support Full text searches. — mattauckland, May 15 '14 at 23:45
You cannot use fulltext search on views, see my answer. I did an update. — Santhos, May 17 '14 at 06:33

Santhos · Answer 1 · 2014-05-17T06:32:48.357

Create a view where you join both of the tables. Then use SELECT with LIKE in WHERE clause or better use a fulltext search to do the searching job.

The view

create view ViewMusicWithGenre as
select "*"
from Music as m
left join Genre as g on m.genre_id = g.id;

Search option with like

select "*"
from ViewMusicWithGenre
where Title like '%<what_you_search>%'
  or Artist like '%<what_you_search>%'
  or Genre like '%<what_you_search>%';

I wrote the asterisk in "" because I KNOW that you WILL NOT use an asterisk. Left join is there because you want the row even without specified genre (very likely).

The fulltext search

This usually depends on the database you use. This is for instance Microsoft SQL Server 2014:

Fulltext search - http://technet.microsoft.com/en-us/library/ms142571.aspx

Fulltext index - http://technet.microsoft.com/en-us/library/ms187317.aspx

Querying fulltext search - http://technet.microsoft.com/en-us/library/ms142583.aspx

EDIT: for MySQL database

MySQL does not support fulltext indeces on views. So you are left with couple of choices:

use the LIKE statement - could be ineffective, also more work later on
create the fulltext index on Music table and omit the genre - not good enough
create a new table that resembles the join and fill it on say daily basis with a job (or something like that) a do the fulltext search on that table - best solution in long terms, but more work to begin with and includes data duplicity

You also have to bear in mind that fulltext indeces only work on MyISAM storage engine.

The create statement for the joint table

create table fulltextSearchTable (
    Music_ID int not null primary key,
    Music_Title varchar(1024) not null,
    Music_Artist varchar(1024) not null,
    Genre_ID int not null,
    Genre_Title varchar(1024) not null,
    fulltext(Music_Title, Music_Artist, Genre_Title)
) engine=MyISAM;

The select with fulltext search

select "*"
from fulltextSearchTable
where match(Music_Title, Music_Artist, Genre_Title) against ('your_keyword');

Thanks Santhos. I'll be using MySQL in a Linx (CentOS 6) box. Database won't be massive at first, but does have the prospect of growing over time. — mattauckland, May 15 '14 at 23:49
I used to work with MySQL databases but that were the times I did not not anything about fulltext search, so this is what I found: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html — Santhos, May 17 '14 at 06:06

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

To connect multiple tables in a query, you should look at using "join" statements. Rather than reinventing the wheel, the first answer to this post does a good job of explaining them... When to use a left outer join

edited May 23 '17 at 11:50

Community

1
1

answered May 15 '14 at 22:00

Bill

2,623
1
27
41

Ah, that seems so simple when you look at that example. Genius. But can you then do a full text search on that join? – mattauckland May 15 '14 at 23:42

score 0 · Answer 3 · answered May 16 '14 at 00:25

You can try INNER JOIN like this:

$result=mysqli_query($YourConnection,"SELECT music.title, music.artist FROM music 
INNER JOIN genres ON music.genre_id=genres.genre_id
WHERE music.title LIKE '$searchword'
OR music.artist LIKE '$searchword'
OR genres.genre LIKE '$searchword'");

And then print the results like this:

while($row=mysqli_fetch_array($result)){
echo $row['title']." - ".$row['artist']."<br>";
}

MySQL Search Over Multiple Tables

3 Answers3