trying to post info from one form into different tables

[gocuse]

I am trying to get information from one PHP form to two different tables in MySQL. The form is gathering information about music bands. I am running into trouble when I try to collect info about individual band members. The form looks like this:

<p>Band members:<br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br>
Name: <input type="text" name="memberName" size="30" maxlength="128"> Instrument(s);<input type="text" name="instrument" size="30" maxlength="128"><br></p>

First, I need to insert the info into a table named Member--which has columns named mid (for member ID), memberName and instrument. I am thinking I need to use an array for mid, MemberName and instrument, but I have looked at the multi-dimensional array documentation on php.net and am lost. The Member table looks like this:

+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| mid | int(11) | | PRI | NULL | auto_increment |
| memberName | varchar(128) | | | | |
| instrument | varchar(255) | | | | |
+------------+--------------+------+-----+---------+----------------+

Then, once the mid is assigned in the Member table, I need the mid and the band ID to go to this table, which is named BandMember:

+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| bid | int(11) | | PRI | 0 | |
| mid | int(11) | | PRI | 0 | |
+-------+---------+------+-----+---------+-------+

Does anyone have any ideas? I can post more code, but I am trying to keep this short because there is a lot of other code on the page that is not related to this problem and the solutions I have tried so far have not worked.

[ahundiak]

Use name="memberName[]" and name="instrument[]"in the form.

Then
$memberNames = $_POST['memberName'];

Will be an array.

[gocuse]

Thanks for the reply ahundiak. I have made some progress. I can get some members inserted into the Member table by using this code:

$memberNames = $_POST['memberName'];

foreach($memberNames as $name){
	$insertMember = "INSERT INTO Member1 (memberName) VALUES ('$name')";
	$queryMember = mysql_query($insertMember);
		if (!$queryMember){
			print mysql_error();
			exit();
		}
	}

However, the problem with this is that empty values are being passed into the table. I have 9 input boxes. If three of the input boxes are filled in, the Member table gets three member names and six empty rows.

Also, to test this code, I dropped the instrument column from the Member table because I cannot figure out how to "merge" the instrument and memberName arrays.

[ahundiak]

$memberNames = $_POST['memberName']; 
$instruments = $_POST['instrument'];
for($i = 0; $i < count($memberNames); $i++)
{
  $name = $memberNames[$i];
  $instrument = $instruments[$i];

  /* Make sure have a name */
  if (strlen($name) > 0))
  {
    /* Do the insert */
    ...
  }
}

Of course you will probably want to do a better job of validating the name but I think you get the idea.

[gocuse]

Thank you for your help so far, ahundiak. I was able to get the memberName and instruments into the Member table, but I am running into another problem. Since the relationship between bands and members is many-to-many, I would like to place the band IDs and member IDs into a BandMember table. Here is the code I have so far:

$memberNames = $_POST['memberName'];
$instruments = $_POST['instrument'];

$insertBand = "INSERT INTO Band SET bandName='$bandName', ".
"formedYear='$formedYear', formedTown='$formedTown', ".
"active='$active', website='$website', email='$email', ".
"description='$description', contactPhone='$contactPhone', ".
"contactEmail='$contactEmail', listInfo='$listInfo', ".
"approve='$approve'";

if (@mysql_query($insertBand)) {
    echo("<p>New band added</p>");
  } else {
    die("<p>Error adding new band: " . mysql_error() . "</p>");
  }

$bid = mysql_insert_id();

foreach($genreName as $genreValue){
	$insertBandGenre = "INSERT INTO BandGenre (bid, gid) ".
"VALUES ('$bid', '$genreValue')";
	$queryBandGenre = mysql_query($insertBandGenre);
		if (!$queryBandGenre){
			print mysql_error();
			exit();
		}
	}

for($i = 0; $i < count($memberNames); $i++) { 

$name = $memberNames[$i]; 
  $instrument = $instruments[$i]; 

  if (strlen($name) > 0) {

  $insertMember = "INSERT INTO Member ".
"(memberName, instrument) VALUES ('$name', '$instrument')";
	$queryMember = mysql_query($insertMember);
	/* get number of new members to use below for BandMember process */
                $memberRows = mysql_affected_rows($queryMember); 
			if (!$queryMember){
			print mysql_error();
			exit();
			}

$members = mysql_query("SELECT mid FROM Member ORDER BY mid"); 
$numMembers = mysql_num_rows($members); // get total number of members in table
$newMembers = $numMembers - $memberRows; // get number of new members in table

// get new member IDs using $newMembers and make result set into an array
$newMembersQuery = mysql_query("SELECT mid FROM Member ".
 "WHERE mid >= $newMembers ORDER BY mid");
	$row = mysql_fetch_array($newMembersQuery);

$mids = $row['mid']; // get mids

// insert band ID and member IDs into BandMember table
foreach($mids as $mid){
	$insertBandMember = "INSERT INTO BandMember (bid, mid) ".
"VALUES ('$bid', '$mid')";
		$queryBandMember = mysql_query($insertBandMember);
		if (!$queryBandMember){
			print mysql_error();
			exit();
		}
	} 
	}
}

The thing I'm trying to do is retrieve the member IDs that were just inserted into the Member table. I'll describe the process below. I think I'm using a roundabout way to accomplish a fairly simple task.

$memberRows is supposed to retrieve the number of rows added to the Member table in the INSERT query. Then, I run a SELECT query and store the total number of rows ($numMembers) in the Member table. I then subtract these numbers to get the number of the first Member ID that had been inserted ($newMembers). Then, I try to run a SELECT query using $newMembers to get the new Member IDs that had been inserted. This result is supposed to be stored in an array and then inserted into BandMember.