INSERT INTO stumbling block

[fontener]

Hi all, I'm stuggling with my logic today  and can't seem to figure this problem of mine out. Ok, here goes: I have an element, let's say a song name and I want to add it to "multiple stations" from a multiple select list and create a new row for each station with the given song name...

 

so table structure would be:

• ID
  
• song name
  
• station name
  

 

therefor, i want it to build the database like so..

 

ID1 | song name | station_name_A

ID2 | song name | station_name_B

ID3 | song name | station_name_C

 

currently I have:

 

<?php
   if ($_POST["submit"])
   {
      $stations = $_POST["stations"];
      $id = $_POST["song_id"];
      
      $result = mysql_query("INSERT INTO table (song_id, station_id) VALUES ('$id', '$stations')") or die(mysql_error());
}
?>

 

Thanks for any help you can provide  cheers!

[o3d]

$result = mysql_query("INSERT INTO table (song_name, station_name) VALUES ((select song_name from tbl_song where id = '$id'), '$stations')") or die(mysql_error());

 

You were not very specific but if I understood correct you have a table containing song data e.g. tbl_song.

[fontener]

sorry... i meant:

 

ID1 | song_id | station_id

ID2 | song_id | station_id

ID3 | song_id | station_id

 

the song is a single value and the station_id is from a multiple select

 

so I want to add a new row for each station_id but the song_id is the same (the ID1, ID2, ID3,... is auto increment)

 

so let's say I have song A and I want to add it to stations 1,2,3 ...

I want the table to be formed like:

ID1      |        song A        |        station 1

ID2      |        song A        |        station 2

ID3      |        song A        |        station 3

 

does this clear things up?

 

 

[Zane]

a multiple select will give you an array in your POST.. from which you will need to loop through each of them and INSERT a new row.

 

Fortunately, the INSERT statement can be bundled with multiple INSERTs.. so you can just populate one variable with all your inserts and perform ONE query; inserting them all

 

something like this

foreach($_POST['station_id'] as $stationID => $value) {
     $insertStatement .= "INSERT into table (`song`, `station`) VALUES ('" . $_POST['song_id'] . "','$stationID');\n";  
/*The semicolon inside is IMPORTANT.. the \n is just to put them on separate lines if you feel like looking at them */
}
$insertThem = mysql_query($insertStatement);

[ignace]

The below does not work due to prevent SQLi

 

foreach($_POST['station_id'] as $stationID => $value) {
     $insertStatement .= "INSERT into table (`song`, `station`) VALUES ('" . $_POST['song_id'] . "','$stationID');\n"; 
/*The semicolon inside is IMPORTANT.. the \n is just to put them on separate lines if you feel like looking at them */
}
$insertThem = mysql_query($insertStatement);

 

ID1 | song_id | station_id

ID2 | song_id | station_id

ID3 | song_id | station_id

 

It's sufficient to write

 

song_id (PK) | station_id (PK)

 

As I doubt a station would have the same song twice even if so it shouldn't be documented in this table.

[fontener]

Ok, I understand the code but I keep getting this warning: Invalid argument supplied for foreach()

 

does this mean that my multiple select is NOT getting posted as an array?!? AAhhhhh !!