mysql update and checkboxes

[frizzle]

Hi there,

I'm building a music site with a mysql backend. It has a many to many
relational database.
I use this to match music genres with certain artists, to maintain the
possibility to add multiple genres to a singe artist.

Now i've searched google, but can't find a solution on how to update
rows with checkboxes.
If an artist gets his genre updated as follows:
from

- [_] classic
- [X] rock
- [_] ballad
- [X] 80's
- [_] 90's
- etc.

to

- [_] classic
- [_] rock
- [_] ballad
- [_] 80's
- [X] 90's
etc.

wha t i wonder is how could i best create the backend. it would seem
inappropraite to run an update query for each possible box wether it's
checked or not.

frizzle.

[IchBin]

frizzle wrote:

Hi there,

I'm building a music site with a mysql backend. It has a many to many
relational database.
I use this to match music genres with certain artists, to maintain the
possibility to add multiple genres to a singe artist.

Now i've searched google, but can't find a solution on how to update
rows with checkboxes.
If an artist gets his genre updated as follows:
from

- [_] classic
- [X] rock
- [_] ballad
- [X] 80's
- [_] 90's
- etc.

to

- [_] classic
- [_] rock
- [_] ballad
- [_] 80's
- [X] 90's
etc.

wha t i wonder is how could i best create the backend. it would seem
inappropraite to run an update query for each possible box wether it's
checked or not.

frizzle.

In the old days, in system programing, you would maintain one byte using
bit masking and you could set multiple bit setting by the sum of their
values.

Translating this... you could have say one int bucket column that can be
set to all or combinations of selections. You would have only one column
to check You would know what the values are by the total added together.

[X] classic = 1
[X] rock = 2
[_] ballad = 4
[_] 80's = 8
[X] 90's = 13

Example, above the bucket value would be 16

if you had 6 the you know it is 'rock' and 'ballad'
if you had 10 the you know it is 'rock' and '80's'
.... and so on.....

You just need a function to physically set you GUI based on the size of
the bucket. You will not have to check on an update..

So when it is time yo update you do not have to worry about updating
multiple columns. You just sum up the values and save that.

Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.awardspace.com
__________________________________________________ ________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

[Geoff Berrow]

Message-ID: <11*********************@b68g2000cwa.googlegroups. com> from
frizzle contained the following:

I'm building a music site with a mysql backend. It has a many to many
relational database.
I use this to match music genres with certain artists, to maintain the
possibility to add multiple genres to a singe artist.

Now i've searched google, but can't find a solution on how to update
rows with checkboxes.
If an artist gets his genre updated as follows:
from

- [_] classic
- [X] rock
- [_] ballad
- [X] 80's
- [_] 90's
- etc.

to

- [_] classic
- [_] rock
- [_] ballad
- [_] 80's
- [X] 90's
etc.

wha t i wonder is how could i best create the backend. it would seem
inappropraite to run an update query for each possible box wether it's
checked or not.

It depends how you have structured your db. For a many to many
relationship like this you ideally need three tables, one for artists,
one for genres and one to link the two.

In the case above you would start with two rows in the link table.
Updating would require removing a row, though it may be simpler to
remove them all and then write a new one.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Geoff Berrow]

Message-ID: <up********************@ptd.net> from IchBin contained the
following:

[X] classic = 1
[X] rock = 2
[_] ballad = 4
[_] 80's = 8
[X] 90's = 13

Example, above the bucket value would be 16

if you had 6 the you know it is 'rock' and 'ballad'
if you had 10 the you know it is 'rock' and '80's'
... and so on.....

Wow that's neat. But as we have explode and implode wouldn't it be
simpler to just store the genres as a comma separated list?

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[IchBin]

Geoff Berrow wrote:

Message-ID: <11*********************@b68g2000cwa.googlegroups. com> from
frizzle contained the following:

I'm building a music site with a mysql backend. It has a many to many
relational database.
I use this to match music genres with certain artists, to maintain the
possibility to add multiple genres to a singe artist.

Now i've searched google, but can't find a solution on how to update
rows with checkboxes.
If an artist gets his genre updated as follows:
from

- [_] classic
- [X] rock
- [_] ballad
- [X] 80's
- [_] 90's
- etc.

to

- [_] classic
- [_] rock
- [_] ballad
- [_] 80's
- [X] 90's
etc.

wha t i wonder is how could i best create the backend. it would seem
inappropraite to run an update query for each possible box wether it's
checked or not.

It depends how you have structured your db. For a many to many
relationship like this you ideally need three tables, one for artists,
one for genres and one to link the two.

In the case above you would start with two rows in the link table.
Updating would require removing a row, though it may be simpler to
remove them all and then write a new one.

I do not think you need three tables. Genres is an adjective describing
either a song or the singer singer. A Genre should be added to both
tables because each singer could have more that one Genre but one song
can only be one Genre. But then again one song can have multiple singers.
Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.awardspace.com
__________________________________________________ ________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

[IchBin]

Geoff Berrow wrote:

Message-ID: <up********************@ptd.net> from IchBin contained the
following:

[X] classic = 1
[X] rock = 2
[_] ballad = 4
[_] 80's = 8
[X] 90's = 13

Example, above the bucket value would be 16

if you had 6 the you know it is 'rock' and 'ballad'
if you had 10 the you know it is 'rock' and '80's'
... and so on.....

Wow that's neat. But as we have explode and implode wouldn't it be
simpler to just store the genres as a comma separated list?

To me, programmatically, no.. it's to easy doing it this way and its all
contained in the program and database.

Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.awardspace.com
__________________________________________________ ________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

[Jerry Stuckle]

Geoff Berrow wrote:

Message-ID: <up********************@ptd.net> from IchBin contained the
following:

[X] classic = 1
[X] rock = 2
[_] ballad = 4
[_] 80's = 8
[X] 90's = 13

Example, above the bucket value would be 16

if you had 6 the you know it is 'rock' and 'ballad'
if you had 10 the you know it is 'rock' and '80's'
... and so on.....

Wow that's neat. But as we have explode and implode wouldn't it be
simpler to just store the genres as a comma separated list?

Yes, that was done in the old days because memory was at a premium. Bit it also
caused problems - like the Y2K bug.

Nowadays, it's better to have a many-to-many table to connect the artist to the
genre. Then it's a simple matter to fetch all the genre's for an artist and
store them in a PHP array. Check the option to see if it's stored in the array;
if it is you can check the box before displaying it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

IchBin wrote:

Geoff Berrow wrote:

Message-ID: <11*********************@b68g2000cwa.googlegroups. com> from
frizzle contained the following:

I'm building a music site with a mysql backend. It has a many to many
relational database.
I use this to match music genres with certain artists, to maintain the
possibility to add multiple genres to a singe artist.

Now i've searched google, but can't find a solution on how to update
rows with checkboxes.
If an artist gets his genre updated as follows:
from

- [_] classic
- [X] rock
- [_] ballad
- [X] 80's
- [_] 90's
- etc.

to

- [_] classic
- [_] rock
- [_] ballad
- [_] 80's
- [X] 90's
etc.

wha t i wonder is how could i best create the backend. it would seem
inappropraite to run an update query for each possible box wether it's
checked or not.

It depends how you have structured your db. For a many to many
relationship like this you ideally need three tables, one for artists,
one for genres and one to link the two.

In the case above you would start with two rows in the link table.
Updating would require removing a row, though it may be simpler to
remove them all and then write a new one.

I do not think you need three tables. Genres is an adjective describing
either a song or the singer singer. A Genre should be added to both
tables because each singer could have more that one Genre but one song
can only be one Genre. But then again one song can have multiple singers.
Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.awardspace.com
__________________________________________________ ________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

I'd suggest you read up on "database normalization" and how it prevents the
problems which can be caused by your method.

Also, a song may have multiple genre's. For instance - it could be "Rock" and
"80's". Or, depending on how it was played/sung, it could be "Country" or "Rock".

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Geoff Berrow]

Message-ID: <Bb******************************@comcast.com> from Jerry
Stuckle contained the following:

I'd suggest you read up on "database normalization" and how it prevents the
problems which can be caused by your method.

Indeed, and the OP should make sure that this is correct before doing
any coding.

As a lecturer I've seen students tackle this many times. You solo
artists, have bands with members, artists who perform as solo artists
and part of bands, albums and tracks, compilation albums, singles, DVDs
and concert performances. Quite a lot to consider. A properly thought
out entity relationship diagram will save a lot of grief later on.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[frizzle]

Geoff Berrow wrote:

Message-ID: <Bb******************************@comcast.com> from Jerry
Stuckle contained the following:

I'd suggest you read up on "database normalization" and how it prevents the
problems which can be caused by your method.

Indeed, and the OP should make sure that this is correct before doing
any coding.

As a lecturer I've seen students tackle this many times. You solo
artists, have bands with members, artists who perform as solo artists
and part of bands, albums and tracks, compilation albums, singles, DVDs
and concert performances. Quite a lot to consider. A properly thought
out entity relationship diagram will save a lot of grief later on.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Well, i'wanted to keep the information as little as possble, but i
think i kept it too little.

What it comes to bands with multiple artists, who'd might go single,
swap labels and style, i've thought about this, but the site should be
quite simple (for now).

a rough sketch:

Artists / bands -> have multiple songs, have multiple genres
Songs -> have a single artist*, have multiple emotions/moods
Genres -> have multiple artists
Emotions/moods -> have multiple songs

*songs with collaborations etc could be added as a new artist.

The tables roughly look like this:
Artists -> info about artist etc.
Genres -> small info with genre
Combo_ar_ge -> combination table, uses artists id, and genre's id to
combine
Songs -> little info about the song, linked to artist by artist id
Moods -> small info about some emotions/mood
Combo_so_mo -> combination table, uses songs id, and mood's id to
combine
So the data structure isn't as deep as it could/should be. I'm trying
to build it focussed on user-friendly-ness (?)
Artists/bands can be added in a flash, without structuring any bands
first, so you'd have to be sure all individual band members are in the
database first. The same goes for songs, i've also thought about albums
with various artists, artist feat.artist etc., but i think the user
would have to enter so much info it would force them to take more time.
And that would make, i believe, the website too unfriendly and would
raise the effort it takes to simply add a song. (i do want to build
such an application in the (near) future though)

The first reaction to this topic would'nt work in this case AFAIK
because the amount of Genres is dynamic. Why i built it many to many,
is because i wanted to make sure that near-infinite genres could be
added to an artist/band. Also AFAIk i know this doesn't go against any
normilazation standards ...

Frizzle.

[frizzle]

frizzle wrote:

Geoff Berrow wrote:

Message-ID: <Bb******************************@comcast.com> from Jerry
Stuckle contained the following:

I'd suggest you read up on "database normalization" and how it prevents the
problems which can be caused by your method.

Indeed, and the OP should make sure that this is correct before doing
any coding.

As a lecturer I've seen students tackle this many times. You solo
artists, have bands with members, artists who perform as solo artists
and part of bands, albums and tracks, compilation albums, singles, DVDs
and concert performances. Quite a lot to consider. A properly thought
out entity relationship diagram will save a lot of grief later on.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Well, i'wanted to keep the information as little as possble, but i
think i kept it too little.

What it comes to bands with multiple artists, who'd might go single,
swap labels and style, i've thought about this, but the site should be
quite simple (for now).

a rough sketch:

Artists / bands -> have multiple songs, have multiple genres
Songs -> have a single artist*, have multiple emotions/moods
Genres -> have multiple artists
Emotions/moods -> have multiple songs

*songs with collaborations etc could be added as a new artist.

The tables roughly look like this:
Artists -> info about artist etc.
Genres -> small info with genre
Combo_ar_ge -> combination table, uses artists id, and genre's id to
combine
Songs -> little info about the song, linked to artist by artist id
Moods -> small info about some emotions/mood
Combo_so_mo -> combination table, uses songs id, and mood's id to
combine
So the data structure isn't as deep as it could/should be. I'm trying
to build it focussed on user-friendly-ness (?)
Artists/bands can be added in a flash, without structuring any bands
first, so you'd have to be sure all individual band members are in the
database first. The same goes for songs, i've also thought about albums
with various artists, artist feat.artist etc., but i think the user
would have to enter so much info it would force them to take more time.
And that would make, i believe, the website too unfriendly and would
raise the effort it takes to simply add a song. (i do want to build
such an application in the (near) future though)

The first reaction to this topic would'nt work in this case AFAIK
because the amount of Genres is dynamic. Why i built it many to many,
is because i wanted to make sure that near-infinite genres could be
added to an artist/band. Also AFAIk i know this doesn't go against any
normilazation standards ...

Frizzle.

What i forgot to mention:
I should be able to enquere the DB for all songs belonging to a certain
mood,
or artists to a genre.

Frizzle.