[SOLVED] insert query and looping thru a form

[Bhaal]

There are two tables:

comp_main and comp_items.

 

comp_main has an auto increment ID field: 'comp_id';

comp_items has a 'foreign key' field: 'CompID'.

 

One-to-many: comp_main to comp_items based on that ID.

 

I'm attempting to build a data entry form that allows the user to enter the comp_main info AND the comp_items info.

 

Getting the info into comp_main is the easy part:

$qa = "insert into comp_main set
comp_name = '$_POST[comp_name]',
comp_desc = '$_POST[comp_desc]' ";
mysql_query($qa);

 

The difficult part (for me) is the comp_items info.

 

There are only four fields in the comp_items table:

- an auto increment ID field;

- artist name;

- song name;

- CompID (the 'foreign key' from comp_main).

 

The form has 25 text boxes for 'artist name' and 25 text boxes for 'song name' (this is the way the client wants it, so they can enter 'artist name', 'song name' then tab to the next set and enter 'artist name', 'song name' on down the form - up to 25 times).

 

I know the comp_items foreign key field (CompID) will be the last inserted ID from the first query:

$lastID = mysql_insert_id();

 

So I'm trying to loop through the 25 sets of artist and song text boxes and only insert those that are filled in:

 

if (count($_POST['artist']) > 0) {
foreach ($_POST['artist'] as $key => $val) {
	$song  = $_POST['song'][$key];
	$artist = $val;
	$q = "insert into comp_items (compitem_artist, compitem_song, CompID ) values ('$artist', '$song', '$lastID')";
	mysql_query($q);
}
}

 

That loop does insert the artist name, song name and CompID correctly, but it also inserts multiple 'blank' record with $lastID as CompID.  For instance, if the user enters two artist names and song names, there are 25 records entered into comp_items (instead of just 2) with 23 of those records having artist name and song name blank but with CompID as $lastID.

 

It should only enter as many records as there are posts for $artist and $song.

 

(Man I hope I'm explaining this accurately enough!)

 

So, how can I loop through the form, grab the entered values and insert them into comp_items w/o any 'blank' records?

 

[Maq]

Sounds to me like you need to check if the values are empty.

 

Try something like:

 

if (count($_POST['artist']) > 0) {
   foreach ($_POST['artist'] as $key => $val) {
      if(!empty($val) || !empty($key)) {
         $song  = $_POST['song'][$key];
         $artist = $val;
         $q = "insert into comp_items (compitem_artist, compitem_song, CompID ) values ('$artist', '$song', '$lastID')";
         mysql_query($q);
      }
   }
}

[Bhaal]

Sounds to me like you need to check if the values are empty.

 

Try something like:

 

if (count($_POST['artist']) > 0) {
   foreach ($_POST['artist'] as $key => $val) {
      if(!empty($val) || !empty($key)) {
         $song  = $_POST['song'][$key];
         $artist = $val;
         $q = "insert into comp_items (compitem_artist, compitem_song, CompID ) values ('$artist', '$song', '$lastID')";
         mysql_query($q);
      }
   }
}

 

Thanks for the help!

 

The query quoted above has the exact same results as the original: 25 records inserted with many blanks (when there should be only as many records as text boxes with values).

 

Playing around with it yielded different results...sort of.

 

This version inserts records for all the values filled in (posted) EXCEPT for the first set:

if (count($_POST['artist']) > 0) {
foreach ($_POST['artist'] as $key => $val) {
	$song  = $_POST['song'][$key];
	$artist = $val;
	if(!empty($val) & !empty($key)) {
		$q = "insert into comp_items (compitem_artist, compitem_song, CompID ) values ('$artist', '$song', '$lastID')";
		mysql_query($q);
	}
}
}

 

So if the user enters three sets of 'artist name', 'song name' only the last two are saved.  If they enter 24 sets, only the last 23 are saved.  But no blank records, so it's much closer.

 

But having 'or' (as in " if(!empty($val) || !empty($key)) { " ) enters blank records.

 

Darn - so close...kinda frustrating (nope: ignorance is not bliss).

[Maq]

Yes, you want to check to make sure they both aren't empty, so use double '&&' for and statements:

 

      if(!empty($val) && !empty($key)) {

 

 

[Bhaal]

Yes, you want to check to make sure they both aren't empty, so use double '&&' for and statements:

 

      if(!empty($val) && !empty($key)) {

 

 

 

Double '&&' has the same results: it skips the first value pair.

 

I checked the names of the text fields just to make sure - all of them are as follows:

<li>Artist: <input type="text" size="25" name="artist[]" class="enter" >  Song: <input type="text" size="45" name="song[]" class="enter" ></li>

 

So why would the first set be skipped?  Very strange.

[Maq]

Yeah, try this:

 

if(!empty($song) && !empty($artist)) {

 

Before were were checking the array, we want to check the values from the array, artist & song.

[Bhaal]

Yeah, try this:

 

if(!empty($song) && !empty($artist)) {

 

Before were were checking the array, we want to check the values from the array, artist & song.

 

Brilliant!  I'd never have figured this out.  Thank you SO MUCH!