Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Trouble with two foreach loops combined

    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'.

    Code:
    <?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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Third table? Not quite sure I follow...

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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:
    Code:
    +---------------+           +--------------------+             +------------------+
    | 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.

  • #5
    New Coder
    Join Date
    Apr 2011
    Posts
    92
    Thanks
    26
    Thanked 0 Times in 0 Posts
    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!


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •