在mysql表中使用相同的FK更新多个值

Question

I have a form that has fields that auto populate with data from mysql table. These input fields get their values from mysql table called person. I am able to successfully display these values. But I am not able to update these values correctly. The values share a foreign key called academy_id. When I update the fields it changes the values to all the same value. How can I successfully update each value? EXAMPLE

Table values before form submit:

+----+------------+------------+-----------+
| ID | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | Person1    | Last1     |
|  2 |         15 | Person2    | Last2     |
+----+------------+------------+-----------+

After trying to change the value

+----+------------+------------+-----------+
| ID | academy_id | first_name | last_name |
+----+------------+------------+-----------+
|  1 |         15 | Person2    | Last2     |
|  2 |         15 | Person2    | Last2     |
+----+------------+------------+-----------+


//DATBASE SELECT QUERY 
$id = 15; 
$db_select3  = $db_con->prepare("
SELECT     a.name, 
           a.academy_id,
           p.first_name,
           p.last_name
    FROM academy a
    LEFT JOIN person p ON a.academy_id = p.academy_id
    WHERE a.academy_id = :id
");
if (!$db_select3) return false;
if (!$db_select3->execute(array(':id' => $id))) return false;
    $results3 = $db_select3->fetchAll(\PDO::FETCH_ASSOC);
    if (empty($results3)) return false;
    $result3 = '';
echo "<strong>Personel Information:</strong>";
$s = 1;
foreach ($results3 as $value3){ 
    echo "<ul id=\"pq_entry_".$s."\" class=\"clonedSection\">";
    echo "<li><input id=\"person_fname_".$s."\" name=\"person_fname_".$s."\" placeholder=\"Person #1 - First Name\" type=\"text\" value='" . $value3['first_name'] ."'/></li>";
    echo "<li><input id=\"person_lname_".$s."\" name=\"person_lname_".$s."\" placeholder=\"Last Name\" type=\"text\" value='" . $value3['last_name'] ."'/></li>";
    echo "</ul>";
$s++;   
}   
echo "<input type='button' id='btnAdd' value='add another Person' />
<input type='button' id='btnDel' value='Delete' /></br>";

//UPDATE VALUES IN DATABASE
if(isset($_POST['submit'])) {
        $f = 1;
        while(isset($_POST['person_fname_' . $f]))
        {

            $person_fname = $_POST['person_fname_' . $f];
            $person_lname = $_POST['person_lname_' . $f];

            $query_init3 = "UPDATE person SET academy_id=:id, first_name=:person_fname, last_name=:person_lname WHERE academy_id=:id;";
            $query_prep3 = $db_con->prepare($query_init3);
            $query_prep3->execute(array(
                "id" => $id,
                "person_fname" => $person_fname,
                "person_lname" => $person_lname
            ));

            $f++;
        }
}

Answer 1

Get rid of that $s variable and directly associate the person.ID with the inputs, like so:

$db_select3  = $db_con->prepare("
    SELECT  a.name,
            a.academy_id,
            p.id as person_id,
            p.first_name,
            p.last_name
    FROM academy a
    LEFT JOIN person p ON a.academy_id = p.academy_id
    WHERE a.academy_id = :id ");

/* ... */ 

foreach ($results3 as $value3){
    $id = $results3['person_id'];
    $first_name = $results3['first_name'];
    $last_name = $results3['last_name'];

    echo '<ul id="pq_entry_'.$s.'" class="clonedSection">';
    echo '<li><input id="person_fname_'.$s.'" name="person['.$id.'][fname]"
         placeholder="Person #1 - First Name" type="text" value="'
         $fname.'/></li>';
}

Then you will process $_POST['person'] on the update page something like:

foreach($_POST['person'] as $person_id => $person) {
    /* 
       UPDATE person SET academy_id=:id, 
       first_name=:person_fname, last_name=:person_lname
       WHERE id = $person_id;
    */
}

I minimized the example because your code was all over the place, but hopefully this gives you a rough template to work from - see the php:HTML FAQ for how this $_POST relationship works.

I've also completely glossed over the need for HTML entity protection on the values coming out of the database to prevent from attacks such as XSS. You should look into these sooner, rather than later.