brad62 — 2013-03-28T13:31:05-04:00 — #1
I've been lucky to not been forced to use arrays in my life so far. But I guess they are quite good, so now I'm planning to use it for the first time.
I've been reading stuff about it and I think I understand how to use it and work with it. But there is something I just haven't found the answer to so far.
Let's say I have a table of a music collection. There are 100 songs in table_music
This one have the name of the song, the artist and then what type of genre.
So, I thought I would use arrays for the genres and have them stored in a table called table_genre
How should I build the genre_table? Is there a row for every type of genre?
And what if there are a new music genre coming up one day. Should I just add one more field to my table?
When I insert a new song to my table, I want all the genres to be listed alphabetically. And then I can check the ones that are right for the type of music. Maybe on song is both jazz and blues. Or one track is both pop, rock and soft rock.
What is the best way to store these genres and what if I want to update the settings one day (maybe changed my mind about a song or a new genre is added that is perfect for that old song in my database).
Can someone just give me a little hint on what would be the best for this example?
Just don't want to start in the wrong end and end up with something that might not work later...
cups — 2013-03-28T15:49:53-04:00 — #2
The term you want to research up on "database normalization".
1 | rock
2 | pop
3 | blues
4 | jazz
23 | Hit me with your rhythm stick
23 | 1
23 | 4
You then use an sql JOIN to get a list of genres for song number 23.
Gotta dash, come back with Qs....
kylewolfe — 2013-03-28T16:36:21-04:00 — #3
Courtesy of our friend here at SPF: http://r937.com/relational.html
brad62 — 2013-03-28T16:47:15-04:00 — #4
I will try this. Now I made a table in phpmyadmin with two INT fields. One called song_id and one called genre_id.
I guess that's all I need in that table. But when that table is done, it tells me that I have no index.
Do I need this for a table like this or should I just ignore that warning?
kylewolfe — 2013-03-28T16:52:50-04:00 — #5
Is it referring to an index or a primary key?
Every table should have a unique identifier (primary key), whether its an auto incremented number, or something that you supply that will ensure a normalized table.
brad62 — 2013-03-30T07:40:15-04:00 — #6
I changed it to include one auto increment as well, since the other two fields might end up including the same sometimes.
Now I need to update my tables. Not sure how yet. Is it possible to update more than one table from one form? Never done that before.
So, the fields that are connected to my song_table will be updated and also these in the new song_genre table fields after pressing the UPDATE button in my form?
brad62 — 2013-03-30T09:08:24-04:00 — #7
Sorry. I mean. I will update my song_db from when pressing SUBMIT in my form and then I like to INSERT what is checked as genres in my form.
How is that part done the best way?
Since the genres might grow one day and contain more genres than today.
brad62 — 2013-03-30T09:31:54-04:00 — #8
The track_id is the song id from the table with all songs. So, I want that id to be connected in my song_genre table together with the checked genre from my checkboxes part of the form.
I tried the following code right below my UPDATE part, but it's not working.
foreach(echo $row_rs_genres['genre_id'] as $checked_value)
$query = "INSERT INTO send_genre (genre_id, song_id) VALUES ('$checked_value', $_POST['track_id'])";
$result = mysql_query($query) or die ("Unable to execute query: " . mysql_error());
brad62 — 2013-03-31T10:14:13-04:00 — #9
Now I found some things that I modified and it is doing what I want (almost).
The code is generating the music genre from a table called table_genre with genre_id and genre_name.
These are included in a form as checkboxes.
The user can check one or more boxes that is correct for the music.
Then I want to input this into my table called songs_genre, but right now I only have it on my screen separated with comma.
$song_genre = count($_POST['song_genre']) ? $_POST['song_genre'] : array();
//echo out their choices separated by a comma
echo count($song_genre) ? implode(', ',$song_genre) : 'Nothing!';
I picked code from something I found when I googled it. But I want my output to be inserted into my db called song_genre.
Here I like to insert the song_genre from the form and also one called $song_id so the genres selected are connected to the song that is used on the actual form.
Can anyone tell me how to change the code above, so it is inserted as separated lines together with the song_id into my table called song_genre