SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Best way to insert arrays and update later?

    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...

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The term you want to research up on "database normalization".

    Essentially:
    Code:
    genres
    =====
    1 | rock
    2 | pop
    3 | blues
    4 | jazz
    
    songs
    =====
    23 | Hit me with your rhythm stick
    
    song_genre
    ========
    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....

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Courtesy of our friend here at SPF: http://r937.com/relational.html

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by brad62 View Post
    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?
    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.

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    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());


  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    645
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    //initialize
    $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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •