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]

Re: mysql update and checkboxes

frizzle wrote:[color=blue]
> 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.
>[/color]
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]

Re: mysql update and checkboxes

Message-ID: <1151197943.581 310.88860@b68g2 000cwa.googlegr oups.com> from
frizzle contained the following:
[color=blue]
>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
>inapproprait e to run an update query for each possible box wether it's
>checked or not.[/color]

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]

Re: mysql update and checkboxes

Message-ID: <upSdnV7otMxIdQ DZUSdV9g@ptd.ne t> from IchBin contained the
following:
[color=blue]
> [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.....[/color]

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]

Re: mysql update and checkboxes

Geoff Berrow wrote:[color=blue]
> Message-ID: <1151197943.581 310.88860@b68g2 000cwa.googlegr oups.com> from
> frizzle contained the following:
>[color=green]
>> 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.[/color]
>
> 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.
>
>
>[/color]

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]

Re: mysql update and checkboxes

Geoff Berrow wrote:[color=blue]
> Message-ID: <upSdnV7otMxIdQ DZUSdV9g@ptd.ne t> from IchBin contained the
> following:
>[color=green]
>> [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.....[/color]
>
> 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?
>[/color]

To me, programmaticall y, 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]

Re: mysql update and checkboxes

Geoff Berrow wrote:[color=blue]
> Message-ID: <upSdnV7otMxIdQ DZUSdV9g@ptd.ne t> from IchBin contained the
> following:
>
>[color=green]
>> [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.....[/color]
>
>
> 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?
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: mysql update and checkboxes

IchBin wrote:[color=blue]
> Geoff Berrow wrote:
>[color=green]
>> Message-ID: <1151197943.581 310.88860@b68g2 000cwa.googlegr oups.com> from
>> frizzle contained the following:
>>[color=darkred]
>>> 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.[/color]
>>
>>
>> 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.
>>
>>
>>[/color]
>
> 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-)[/color]


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.
jstucklex@attgl obal.net
=============== ===

[Geoff Berrow]

Re: mysql update and checkboxes

Message-ID: <BbWdnUHqd747Yg DZnZ2dnUVZ_vydn Z2d@comcast.com > from Jerry
Stuckle contained the following:
[color=blue]
>
>I'd suggest you read up on "database normalization" and how it prevents the
>problems which can be caused by your method.[/color]

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]

Re: mysql update and checkboxes


Geoff Berrow wrote:[color=blue]
> Message-ID: <BbWdnUHqd747Yg DZnZ2dnUVZ_vydn Z2d@comcast.com > from Jerry
> Stuckle contained the following:
>[color=green]
> >
> >I'd suggest you read up on "database normalization" and how it prevents the
> >problems which can be caused by your method.[/color]
>
> 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/[/color]

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]

Re: mysql update and checkboxes


frizzle wrote:[color=blue]
> Geoff Berrow wrote:[color=green]
> > Message-ID: <BbWdnUHqd747Yg DZnZ2dnUVZ_vydn Z2d@comcast.com > from Jerry
> > Stuckle contained the following:
> >[color=darkred]
> > >
> > >I'd suggest you read up on "database normalization" and how it prevents the
> > >problems which can be caused by your method.[/color]
> >
> > 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/[/color]
>
> 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.[/color]

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.