0

I'm writing an simple Musicians database that has a search function that will e-mail details of musicians to people when they search for a specific instrument in that database. I have people who play more then one instrument therefore I have 5 tables, instrument, instrument2, instrument3, instrument4, instrument5.

I have a MYSQL query on my e-mail function but I cannot get it to search more then one table.

Here is the code:

$query = "SELECT * FROM instruments WHERE (instrument, instrument2, instrument3, instrument4, instrument5) = '$search'";

Am I being stupid with my syntax or am I going about the wrong way with this.

Many thanks.

Toby
  • 15
  • 5

3 Answers3

0

http://dev.mysql.com/doc/refman/5.0/en/multiple-tables.html

You should just have one table with fields instrument and email. It seems that is all you want.

Calum
  • 5,308
  • 1
  • 22
  • 27
0

Have you tried:

SELECT * 
FROM instruments 
WHERE '$search' IN (instrument, instrument2, instrument3, instrument4, instrument5)

? I assume this is what you want.

But a real solution would be to create a separate table with the instruments and not keep multiple columns for the same information.

You should have a table with musicians and one with instruments to hold general instruments and their info. And a 3rd one (used to resolve the many to many relationship between instruments and musicians) that only holds musician id and instrument id.

Here's a few useful links:

Community
  • 1
  • 1
Alin Purcaru
  • 43,655
  • 12
  • 77
  • 90
  • This sounds positive, I already have the realtionship between instruments and musicians via unique ID, the instruments themselves are in a long list of a enum datatype, how would I generate IDs for them? – Toby Apr 12 '11 at 12:00
  • Also - THANK YOU for the above code, it seems to do the trick for now, this is only a simple database and I'm going to work on the database normalization later because obviously seems a much more efficient way. – Toby Apr 12 '11 at 12:02
  • @Toby When no ID is evident you can use autoincrement. – Alin Purcaru Apr 12 '11 at 12:19
  • Yeah I already use autoincrement on both, thats how the instruments are linked to the musicians – Toby Apr 12 '11 at 12:21
0

Best would be use 2 tables - Musicians and Instruments. Each row in Instruments table has a field 'musician_id', which refers to the corresponding musician. This is called 1-N relation.

Now, when you want to query the database, you do:

SELECT * FROM musicians m LEFT JOIN instruments i ON (m.id = i.musician_id) WHERE i.name = "Oboe";

This JOINS the two tables together, returning to you a row for every instrument a person plays.

Or, if you already know the Musician ID, query only instruments:

SELECT * FROM instruments WHERE musician_id = 123;
Laas
  • 5,978
  • 33
  • 52
  • I already have two tables, people and instruments linked via unique IDs, i just had to create more then one instrument in the instruments table – Toby Apr 12 '11 at 11:53
  • Good, then the first query should do the job (I edited for an example). From the question I took that you had 5 tables. – Laas Apr 12 '11 at 12:04