Update A Dynamic Array

[unemployment]

How can  I use php to update a dynamic amount of input fields and tell mysql which ones to update respectively?  Essentially I have a list of occupations and if someone wants to update one of them I don't know how to tell php which one they've changed in a dynamic way.

 

<?php

if(!empty($occupations))
{

?>
<h3 id="job_function">Job Functions</h3>
<?php

$i = 0;

foreach($occupations as $occupation)
{

?>
<div class="formElement">
	<div class="formFieldLabel ">
		<label for="occupation_<?php echo $i; ?>"><?php echo $occupation['companyname']; ?></label>
	</div>
	<div class="formField">
		<input id="occupation_<?php echo $i; ?>" type="text" name="occupation_<?php echo $i; ?>" value="<?php echo $occupation['function']; ?>" />
	</div>
</div>
<?php

$i++;

}
}

?>

[AbraCadaver]

How are the occupations stored in your database?

[unemployment]

I have updated my inputs with...

 

<input id="occupation_<?php echo $i; ?>" type="text" name="occupation_<?php echo $i; ?>" value="<?php echo $occupation['function']; ?>" />
<input type='hidden' name='occupation[]' value='<?php echo $occupation['companyid']; ?>' />

 

My employees table is setup with...

user_id

company_id

occupation

 

 

[Psycho]

Since you state you already have records in the database, you need to provide the database primary key (i.e. the record id) as part of the data being sent via POST otherwise you have no way to determine which filed should update which record. Do not use seaparte fields (one for id and another for the input). You can incorporate both into a single input field as follows:

echo "<input id='occupation_{$i}' type='text' name='occupation[{$occupation['companyid']}]' value='{$occupation['function']}' />";

 

If you want to allow the user to update multiple records on one page, then you have no choice but to run an update query for all the records because you have no way of knowing which ones the user updated. You can run all the updated via a single query using the MySQL CASE statement.

[Psycho]

Here is a revise of your previous code implementing the changes needed. Also, I separated the "logic" from the "presentation". It helps to put all your processing logic in the head of your document and then put the output (i.e. presentation) in the bottom of the script (or even another file).

 

<?php

$functionsList = '';
if(!empty($occupations))
{
    foreach($occupations as $occupation)
    {
        $id = $occupation['companyid'];
        $name = $occupation['companyname'];
        $function = $occupation['function'];

        $functionsList .= "<div class='formElement'>\n";
        $functionsList .= "  <div class='formFieldLabel'><label for='occupation_{$id}'>{$name}</label></div>\n";
        $functionsList .= "  <div class='formField'>\n";
        $functionsList .= "  <input id='occupation_{$id}' type='text' name='occupation[{$id}]' value='{$function}' />\n";
        $functionsList .= "  </div>\n";
        $functionsList .= "</div>\n";
    }
}

?>
<h3 id="job_function">Job Functions</h3>
<?php echo $functionsList; ?>

 

Then your processing logic could look something like this:

<?php 

if(isset($_POST['occupation']))
{
    //Create array to hold the update values
    $updateValuesAry = array();

    //Loop through the post data
    foreach($_POST['occupation'] as $id => $function)
    {
        //Parse input data
        $id       = intval($id);
        $function = mysql_real_escape_string(trim($function));

        //Check if record is valid
        if($id==0 || empty($function))
        {
            //Update record is invalid - need to insert error handling code. You can:
            // 1. Skip the record entirely
            // 2. Don't process any records and provide error message
            // 3. Only process valid records (and provide error message)
            // 4. ???
        }
        else
        {
            //Add update clause to array
            $updateValuesAry[] = "WHEN '{$id}' THEN '{$function}'"
        }
    }

    //Create one update query for all records
    $updateValuesStr = implode("\n", $updateValuesAry);
    $query = "UPDATE `table_name` SET `function` =
                  CASE `companyid`
                    {$updateValuesStr}
                  ELSE
                    `function`
                  END";
    $result = mysql_query($query);
}

?>

 

Note: none of this is tested, but the logic is valid

[unemployment]

Very nice logic.  I wouldn't have thought to use a CASE

[Psycho]

Very nice logic.  I wouldn't have thought to use a CASE

 

Well, if you don't use a case, you are stuck with running multiple queries in a loop - which is very bad for performance and scalability. Personally, I try to refrain from creating pages where you can update multiple records concurrently and instead give a list of records with a button/link for each record to go to a page to edit that individual record.