simple Insert into 2 tables Gone Wild!

[skaiwayne]

I have 2 mysql tables companies and owner.
1 company can have multiple owners. So far I can insert the company and one owner just fine. But I am having a hard time inserting more owners on the same company. I don't fully understand how to get my value out of the for loop
Here is my code:

/* Owner Info */

$totalowners = 0;

$ownergender1 = $_POST["q60_Gender"]; 
$ownername1 = $_POST["q34_OwnerName"]; 
if(!empty($ownername1)){
	$totalowners++;
}
$owneraddress1 = $_POST["q35_OwnerAddress"]; 
$ownercity1 = $_POST["q36_OwnerCity"]; 
$ownerstate1 = $_POST["q38_OwnerState"]; 
$ownerzip1 = $_POST["q48_OwnerZip"];
$sameaddress1 = $_POST["sameasbusiness"];

/* Owner 2 Info */
$ownergender2 = $_POST["q61_Gender"]; 
$ownername2 = $_POST["q39_OwnerName"]; 
if(!empty($ownername2)){
	$totalowners++;
}
$owneraddress2 = $_POST["q43_OwnerAddress"]; 
$ownercity2 = $_POST["q42_OwnerCity"]; 
$ownerstate2 = $_POST["q49_OwnerState"]; 
$ownerzip2 = $_POST["q50_OwnerZip"]; 
/* Owner 3 Info */
$ownername3 = $_POST["q18_OwnerName"]; 
if(!empty($ownername3)){
	$totalowners++;
}
$owneraddress3 = $_POST["q51_OwnerAddress"]; 
$ownercity3 = $_POST["q20_OwnerCity"]; 
$ownerstate3 = $_POST["q21_OwnerState"]; 
$ownerzip3 = $_POST["q27_OwnerZip"]; 
$ownergender3 = $_POST["q63_Gender"]; 
/* Owner 4 Info */
$ownername4 = $_POST["q54_OwnerName"]; 
if(!empty($ownername4)){
	$totalowners++;
//	echo "Last Owner right here" . $ownername4;
}
$owneraddress4 = $_POST["q57_OwnerAddress"]; 
$ownercity4 = $_POST["q58_OwnerCity"]; 
$ownerstate4 = $_POST["q56_OwnerState"]; 
$ownerzip4 = $_POST["q55_OwnerZip"]; 
$ownergender4 = $_POST["q60_Gender"]; 

require("inc/dbconnect.php");

if(!empty($fillingnumber)){
$sql = "INSERT INTO `Companies` (`fillingnumber` ,`businessname` ,`businessaddress` ,`datefiled`,`businesscity` ,`businessstate` ,`businesszip`) VALUES 		('$fillingnumber','$businessname','$businessaddress','$edate','$businesscity', '$businessstate', '$businesszip')";
mysql_query($sql,$con);

for ( $i = 1; $i <= $totalowners;) {

 	if (!empty($sameaddress[$i])) {
	$osql = "INSERT INTO `owner` (`ownerid`,`fillingnumber` ,`ownergender` ,`ownername`,`owneraddress`,`ownercity` ,`ownerstate` ,`ownerzip`) VALUES
	('','$fillingnumber[$i]','$ownergender[$i]',$ownername.$i','$businessaddress.$i','$businesscity.$i', '$businessstate.$i', '$businesszip.$i')";
	}else{
	$osql = "INSERT INTO `owner` (`ownerid`,`fillingnumber` ,`ownergender` ,`ownername`,`owneraddress`,`ownercity` ,`ownerstate` ,`ownerzip`) VALUES ('','$fillingnumber.$i','$ownergender.$i','$ownername.$i','$owneraddress.$i','$ownercity.$i', '$ownerstate.$i', '$ownerzip.$i')";
} 
 	mysql_query($osql,$con);


	
 $i++;
 
}

[skaiwayne]

Is there an advanced forum I should post this in?

[diafol]

From what I can see, you've created hard variables for each piece of info. Just put them into an array

e.g.

$owner['gender'][] = $_POST['q60_Gender']
$owner['name'][] = ...
$owner['address'][] = ... 
...(etc)...
$owner['gender'][] = $_POST["q61_Gender"]
$owner['name'][] = ...
$owner['address'][] = ...
...(etc)...

Now you have all your data in a tidy array. Now call a loop to insert the data:

$i = 0;
while($i < count($owner['gender'])){
   
... do your sql with $owner['gender'][$i] etc ...


$i = $i + 1;
}

Mind you, I don't think I would have created all those form fields with weird names. I didn't really get that bit. You can use $_POST arrays to do all the heavy lifting from duplicate names in a form:

<!--for first one-->
<input type="text" name="owner[gender][]" />
<input type="text" name="owner[name][]" />
<input type="text" name="owner[address][]" />
...(etc)...
<!--for second one-->
<input type="text" name="owner[gender][]" />
<input type="text" name="owner[name][]" />
<input type="text" name="owner[address][]" />

The $_POST variable will then give you:

$owner = $_POST['owner'];

The just do as the first bit of code to insert your SQL.

The beauty of this technique is that you could have a javascript function to add a new set of owner fields after you have finished entering the data for the previous owner. So you start with just the one set of owner fields. You could have a little button like 'add another owner' so that when this was pressed another set of owner fields would appear. This DOES NOT involve Ajax.

BTW: what did you mean 'a more advanced forum'? Just coz nobody replies, doesn't mean nobody can help. Your problem wasn't particularly difficult and my reply is a really quick fix. Undoubtedly there are a few hundred coders that peruse this forum that will be able to offer a far more succinct solution.

[djjjozsi]

It is called many-many relation.

If you have the two table for sompany and owner names:

owners:

CREATE TABLE `owners` (
  `ownerID` int(11) NOT NULL auto_increment,
  `Owner` varchar(255) NOT NULL,
  PRIMARY KEY  (`ownerID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Company

CREATE TABLE `Company` (
  `companyID` int(11) NOT NULL auto_increment,
`` varchar(255) NOT NULL,
  PRIMARY KEY  (`companyID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now you need to save the companyID and ownerID pairs in separate rows to be able to store the relations.
You need to have a connector table:

connector table

CREATE TABLE `owners_companies` (
  `companyID` int(11) NOT NULL,
  `ownerID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then you can list all the owners into checkboxes, where the variable name is an array. On the landing page you can save table the owners and the companyID into the connector table.

http://phpcode.hu/forumhelper/index.php?id=8