Update and Insert arrays and more

Question

I have a MySQL db called table_music Here I store artist, song,... this is something I will update from a form I have on a page.

Now I want to add genres to this form as well, so I made a table called song_genre Here I have song_genre_id, song_id, genre_id

In my form I list the title of song from my table_music Then I have fields where I can update the composer, length, year, and so on. And there is also a checkbox list called genre from my song_genre table with all the genres sorted alphabetically. I have these in a different table, since I might want to add genres later.

But I don't know how to insert these checked genres into my song_genre table. I've never used arrays before, so I'm lost here.

First I want to UPDATE my table_music and that is working fine. Then I want to INSERT INTO my new table (song_genre) so I get the genre and the song_id in here.

I think that's the right way to do it, so I am able to change the genre later or add or remove one genre if I need that as well one day.

I tried the following: but it doesn't seem to work:

foreach(echo $row_rs_genres['genre_id'] as $checked_value)
{
   $query = "INSERT INTO song_genre (genre_id, song_id) VALUES ('$checked_value',    $_POST['song_id'])"; 
   $result = mysql_query($query) or die ("Unable to execute query: " . mysql_error());

}

score 1 · Answer 1 · answered Mar 30 '13 at 17:59

Remove the echo:

foreach($row_rs_genres['genre_id'] as $checked_value)

And you need to enclose the $_POST in curly brackets:

"INSERT INTO song_genre (genre_id, song_id) VALUES ('$checked_value', {$_POST['song_id']})"

Or break out of the string:

"INSERT INTO song_genre (genre_id, song_id) VALUES ('" . $checked_value . "', " . $_POST['song_id'] . ")"

Although you're susceptible to SQL injection here. You should escape your input or switch to prepared statements.

score 0 · Answer 2 · edited May 25 '19 at 14:25

0

First of all, as far as table structure, you should have a table for your genres that just stores the genre_id and genre_name (and any other data you might need). Then create a separate table to do the lookup. This table would just have song_id, genre_id, with a unique index on both fields.

Secondly, when you are capturing data from a public form and inserting into a database, make sure you are scrubbing the data first to prevent SQL injection and the like. For instance, if you know that a certain data value can only be numeric, then enforce that by using the intval() function before you try to use the data.

If you haven't yet, please read about not using mysql_* functions in new code. They are no longer maintained and are officially deprecated . Learn about instead, and use PDO or MySQLi; this article will help you decide which one to use. If you choose PDO, here is a good tutorial

edited May 25 '19 at 14:25

Zoe

27,060
21
118
148

answered Mar 30 '13 at 18:02

Revent

2,091
2
18
33

A little question, if I make that second table with just song_id and genre_id with unique fields. Sometimes a song can be right in two or even more genres. It might be both, jazz and blues, or maybe pop and instrumental. So, I guess the unique thing wouldn't work? – Brad Mar 30 '13 at 19:50
Forgot to say, I already have one table with all my genre_id and genre_name. That one is putting all the right ones into my form and sorted alphabetically. So, I have on large table with the music, artist, composer, label and so on. Another table with genre_id and genre_name. Then one, where I think I need to have the connection of the song_id and genre_id. This is the one I call song_genre. It also have one filed called song_genre_id – Brad Mar 30 '13 at 20:04

Update and Insert arrays and more

2 Answers2