[RESOLVED] Insert Into HELP!!

fontener

Please Help!!

I have the following code which generates the error : warning: Invalid argument supplied for foreach() ..... it's probably WAY off from what I need, but to be pointed in the right direction would be wonderful

foreach($_POST['station_id'] as $stationID => $value) {
     $insertStatement .= "INSERT into table (`song_id`, `station_id`) VALUES ('" . $_POST['song_id'] . "','$stationID');\n"; 
}
$insertThem = mysql_query($insertStatement);

Basically what I need to do is insert into table the song_id and station_id so that for each station_id a new row is created. (The station_id is from a Select Multiple field, the song_id is only one specific song)

i.e.
song_id | station_id1
song_id | station_id2
song_id | station_id3

Thanks for any help you may have! I really appreciate it. Cheers!

dagon

$_POST['station_id'] is not an array

what's the results from print_r($_POST);

fontener

dagon;10947203 wrote:
$_POST['station_id'] is not an array

what's the results from print_r($_POST);

Array ( [asmSelect0] => Click to Select a Station [stations] => 1 [song_id] => 5 [submit] => Submit )

dagon

you don't need the foreach() as you only have 1 station\song_id pair

fontener

It should have more than one station per song... something is getting messed up when the information is passed... am i missing something with passing on multiple select options?

  <input type="submit" name="submit" value="Submit" class="btn">

</form>

<?php

if ($POST["submit"])
{
foreach($POST['stations'] as $stationID => $value) {
$insertStatement .= "INSERT into status (song_id, station_id) 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);
}
?>
thanks again

bradgrafelman

fontener wrote:
am i missing something with passing on multiple select options?

Yep: append '[]' to the end of the name of the SELECT entity so that it gets parsed as an array by PHP. In other words, $_POST['stations'] will be an array of values that the user selected.

EDIT: Also note that user-supplied data should never be placed directly into a SQL query string. Instead, you must first sanitize the data with a function such as [man]mysql_real_escape_string/man.

dagon

the name should be name="stations[]" then you will get the array you where expecting

fontener

one step closer fellas thanks.. I got the array to print.. but it's not being inserted into the database, and I'm not receiving errors....

dagon

echo out your db query and use mysql_error() to find the error

fontener

dagon;10947218 wrote:
echo out your db query and use mysql_error() to find the error

I feel dumb.. my brain just did not wake up with me today..

: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO status (song_id, station_id) VALUES ('5','1')INSERT INTO status ' at line 1

It looks like the INSERT INTO is looping but not closing?!?

bradgrafelman

The older 'mysql' PHP library can't handle more than one query at a time. Even if you were using MySQLi's [man]mysqli_multi_query/man, however, you can't just cram multiple queries together and expect MySQL to know where one ends and another begins... that's why there's a delimiter (a semicolon, by default).

Since you're inserting into the same table, however, you can combine all of these queries into a single query by using this syntax:

INSERT INTO table (col1, col2) VALUES ('val1a', 'val1b'), ('val2a', 'val2b'), ...

fontener

That makes sense, however I could be adding the song to 1 station or 50.. so how would I be able to set this up that way?

bradgrafelman

Not sure what you're asking.. doesn't $stationID define which station you're adding the song to?

EDIT: Oops, think I misread your post. You don't need to know the number - just loop over the array passed and keep concatenating values onto the end of the SQL query, much like you're doing now (only just a part of the query).

fontener

INSERT INTO table (col1, col2) VALUES ('val1a', 'val1b'), ('val2a', 'val2b'), ...

I guess im confused as well... what I mean is, I could have the song added to one station

song A ... station1

or added to 50 stations

song B ... station1
song B ... station2
song B ... station3

so.. if there isnt a set amount and it can fluctuate between 1 - 100+stations, how do I set the INSERT INTO to be flexible to this?

bradgrafelman

You just keep adding ", ('value', 'value')" onto the end of the query inside the loop.

fontener

yup, im lost... not a good day for me... :eek:

bradgrafelman

I'm confused as to how you could be getting confused... it's really simple. You just keep adding on ", ('val', 'val')" onto the end of the query for however many values there are. I've already shown you an example of how to insert 2 rows at a time, but here's another for 3 rows:

INSERT INTO mytable (col1, col2) VALUES ('val1a', 'val1b'), ('val2a', 'val2b'), ('val3a', 'val3b')

fontener

but the number of values can change for each song... one song may have 10 stations and another song could have 50 stations...

bradgrafelman

Right, so when then foreach() is finished looping through all of the stations that have been posted, you'll end up with an INSERT statement that has 10 or 50 or however many sets of values.

fontener

<?php

   if ($_POST["submit"])
   {
     foreach($_POST['stations'] as $stationID => $value) {
     $insertStatement .= "INSERT INTO status (`song_id`, `station_id`) VALUES . ('" . $_POST['song_id'] . "','$stationID');"; 
}
$insertThem = mysql_query($insertStatement);
if (!$insertThem) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  }
}
?>

so this would be converted to... ??

<?php
if ($_POST["submit"])
{
  for each($_POST['stations'] as $stationID => $value) {
?????????????????????????????????
}
}

I'm a visual learner ... so seeing it in action helps me understand it.. if that makes sense
?>