search across multiple records?

Hello,

I have an HTML file with <form etc in which the names of composers are
listed. Say Beethoven, Mozart, etc.

In the MySQL database I have records

composer1 Beethoven
composer1work1 Symphony No.4

composer2 Mozart
composer2work1 Piano Sonata No.2

etc etc.

How can I form the SQL statement so that the search will find
composers and their works in different records?

Any pointers please?!

Cheers,

Geoff

Geoff <Cox> wrote:
> I have an HTML file with <form etc in which the names of composers are
> listed. Say Beethoven, Mozart, etc.
>
> In the MySQL database I have records
>
> composer1 Beethoven
> composer1work1 Symphony No.4
>
> composer2 Mozart
> composer2work1 Piano Sonata No.2
>
> etc etc.
>
> How can I form the SQL statement so that the search will find
> composers and their works in different records?


Set it up like this:

TABLE composers
composer_id int
composer_name varchar

TABLE works
work_id int
work_name varchar
composer_id int

Or possibly, for allowing several composers for a work, move composer_id
to a relational table:

TABLE work_composers
work_id
composer_id

List composers:
SELECT composer_id,composer_name FROM composers

List works of a particular composer:
SELECT work_id, work,name FROM works WHERE composer_id = <id of the
composer>

Or, if you went with the relational table:
SELECT wc.work_id, w.work_name
FROM work_composers wc
JOIN works w
ON w.work_id = wc.work_id
WHERE wc.composer_id = <id of the composer>

--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

On Sat, 10 Mar 2007 17:31:32 +0100, Rik <luiheidsgoeroe@hotmail.com>
wrote:

>Geoff <Cox> wrote:
>> I have an HTML file with <form etc in which the names of composers are
>> listed. Say Beethoven, Mozart, etc.
>>
>> In the MySQL database I have records
>>
>> composer1 Beethoven
>> composer1work1 Symphony No.4
>>
>> composer2 Mozart
>> composer2work1 Piano Sonata No.2
>>
>> etc etc.
>>
>> How can I form the SQL statement so that the search will find
>> composers and their works in different records?
>
>
>Set it up like this:
>
>TABLE composers
> composer_id int
> composer_name varchar
>
>TABLE works
> work_id int
> work_name varchar
> composer_id int
>
>Or possibly, for allowing several composers for a work, move composer_id
>to a relational table:
>
>TABLE work_composers
> work_id
> composer_id

Food for thought! Thanks Rik.

Cheers

Geoff












>
>List composers:
>SELECT composer_id,composer_name FROM composers
>
>List works of a particular composer:
>SELECT work_id, work,name FROM works WHERE composer_id = <id of the
>composer>
>
>Or, if you went with the relational table:
>SELECT wc.work_id, w.work_name
> FROM work_composers wc
>JOIN works w
>ON w.work_id = wc.work_id
>WHERE wc.composer_id = <id of the composer>

Rik,

Just thought of another, simpler way,

I have added a field to each record in which I have added the names of
the composers so I can now make a "Like" select on this field and get
all the records for each particular composer.

Seems to work OK - see any problems?!

Cheers

Geoff

Geoff <Cox> wrote:

> Rik,
>
> Just thought of another, simpler way,
>
> I have added a field to each record in which I have added the names of
> the composers so I can now make a "Like" select on this field and get
> all the records for each particular composer.
>
> Seems to work OK - see any problems?!

Yes, consistency:

J.S. Bach
Johan Sebastian Bach
JS Bach
Bach
j.s. bach

Seems the same composer to me, not to your current database setup. Look up
normalisation among others. Also, you idea is not necessarily a 'simpler'
way. True, you have only ont table, but getting good results from it is
tricky. What if you want a list of composers and the number of works for
instance? Would be much simpler, and above all blindingly faster with the
proposed setup.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

On Sat, 10 Mar 2007 19:37:24 +0100, Rik <luiheidsgoeroe@hotmail.com>
wrote:

>Geoff <Cox> wrote:
>
>> Rik,
>>
>> Just thought of another, simpler way,
>>
>> I have added a field to each record in which I have added the names of
>> the composers so I can now make a "Like" select on this field and get
>> all the records for each particular composer.
>>
>> Seems to work OK - see any problems?!
>
>Yes, consistency:
>
>J.S. Bach
>Johan Sebastian Bach
>JS Bach
>Bach
>j.s. bach
>
>Seems the same composer to me, not to your current database setup. Look up
>normalisation among others. Also, you idea is not necessarily a 'simpler'
>way. True, you have only ont table, but getting good results from it is
>tricky. What if you want a list of composers and the number of works for
>instance? Would be much simpler, and above all blindingly faster with the
>proposed setup.

OK! Had better have a go with your approach.

Cheers

Geoff