Trouble with two foreach loops combined

Juniper747

01-31-2012, 08:11 AM

I am trying to use two different foreach loops to do the following:

1) generate a list of names (potential) stored in a session variable array (potentials) to be placed next to input fields when the page is first loaded
2) after the form is posted, I want to store each input value which corresponds to the 'potential' variable into a database table where a column already has the name potential stored in it

Here's what I have so far. Everything is working, except only the LAST value of 'temp_name' gets stored into the multiple rows of the different 'potential' values in the database. What I want is all the different values of 'temp_name' getting stored into the different rows in the table, next to the corresponding 'potential'.

<?php
//session started and connect to db already started

if(isset($_SESSION['potentials_array'])){
$potentials = $_SESSION['potentials_array'];
foreach($potentials as $potential){
$c_list .= '
<label>' . $potential . '</label>
<input id="status" name="input_names[]" type="text" maxlength="50" />
';
}
$submit_buttons = '<input type="submit" value="Save Names!"/>';
if (isset($_POST['input_names'])){
foreach($potentials as $potential){
$temp_names = $_POST['input_names'];
foreach($temp_names as $temp_name) {
$sql = mysql_query(" UPDATE potentials SET temp_name='$temp_name' WHERE candidate='$potential' ") or die (mysql_error());
}
}
$c_list = "Names Saved!";
$submit_buttons = '';
unset($_SESSION['potentials_array']);
}
}
?>
<html>
<body>
<form action="form.php" method="POST" enctype="multipart/form-data">
<?php echo $c_list; ?>
<?php echo $submit_buttons; ?>
</form>
</body>
</html>

How can I fix this?

Fou-Lu

01-31-2012, 01:44 PM

This is correct. You are overwriting the value of the potentials table corresponding to the candidate field with each iteration. Given the possible many to many relationship, you should normalize this by flattening to a third table taking a key of the candidate and tmp_name (give them names that make more sense). The alternative is to treat this as a comma separated list, which if a relation exists between one more more field to another corresponding table field should be avoided to prevent anomalies.

Juniper747

02-01-2012, 02:06 AM

Third table? Not quite sure I follow...

Fou-Lu

02-01-2012, 02:29 PM

If the relationship is many to many, you cannot represent this properly between two tables without creating guaranteed anomalies. The two in specific you will get are modify and delete. If you use comma separated text, you will not be able to index this data (the way it should be) so you will end up having to iterate most records in the dataset if you need to delete or update, and it will consume more disk space. My assumption for this is that you are attempting to write multiple entries into a single field represented by the $potential and the data within the $temp_names.
Three tables allows you to normalize the data between the two to represent a many to many relationship without anomalies. I don't know what your data is, so I'll just use a simple user and product example:

+---------------+ +--------------------+ +------------------+
| User | | UserProduct | | Product |
+---------------+ +--------------------+ +------------------+
| userID [PK] | | userID [PK][FK] | | productID [PK] |
| username |>o-------|<| productID [PK][FK] |>|---------o<| description |
| password | | quantity | | price |
| email | +--------------------+ +------------------+
+---------------+

This allows you to find all products for a specific user, all users for a specific product, counts of said product sales, etc etc. These should enforce referential integrity so in MySQL you'll want to use INNODB database types, and you can optionally add a cascade for both delete and update, so if you delete a product it will delete the associated entries, and the same from the user side.

Juniper747

02-01-2012, 10:01 PM

I think you may have misunderstood my original problem. However I was able to solve it by modifying my for each statement. Anyhow, thank you for the info regarding many to many tables!