Edit database row from PHP page

Question

I have two PHP pages:

• The first one has a form that insert data in the database.
• The second diplays all the data of the database.

How could I, conceptually, adding for each row a button "update", that allow me to change some of the value of that row (in my example the value of the 2 dropdownlist) and update this info in the database as well.

This is the code I was able to do looking in internet and I have 2 problems: First, is working only the second update button (so only the second row get updated). Second, the changes does not get reflected into the dropdowlist. (means that if I change the second row value the database get updated but not the dropdownlist).

Note that I implemented the two columns

<td>" . $row['status'] . "</td>
<td>" . $row['priority'] . "</td>

Only to check that the value in the database changes.

    <form method="post" action="job-status.php">
           <?php

    include("../includes/connection.php");

        if($link->connect_errno > 0){
            die('Unable to connect to database [' . $link->connect_error . ']');
        }

        if(isset($_POST['update'])) {
           $results = $link->query("UPDATE job SET status='$_POST[status]', 
        priority='$_POST[priority]' WHERE id='$_POST[hidden]'");

        }    
           $sql = "SELECT * from job";
        if(!$result = $link->query($sql)){
            die('There was an error running the query [' . $link->error . ']');
        }
        echo "
    <table class='table'>
        <thead>
            <tr>";
        /* Get field information for all columns */
        while ($finfo = $result->fetch_field()) {
            echo "
            <th>" . $finfo->name . "</th>";
        }
        echo "
            </tr>
        </thead>
        <tbody>";
        while($row = $result->fetch_assoc()){
       echo "<tr class='info'>

                    <td>" . $row['id'] . "</td> 
                    <td>" . $row['device'] . "</td>
                    <td>" . $row['model'] . "</td> 
                    <td>" . $row['problem'] . "</td>
                    <td><select class='form-control col-sm-10' id='status' name='status'>
                         <option value='new'>New</option>
                         <option value='progress'>Progress</option>
                         <option  value='wait'>Wait</option>
                         <option value='done'>Done</option>
                         <option value='close'>Close</option>
                   </select></td>

                    <td><select class='form-control col-sm-10' id='priority' name='priority'>
                     <option value='high'>High</option>
                         <option value='medium'>Medium</option>
                     <option  value='low'>Low</option>
                     </select></td>

                       <td>" . $row['status'] . "</td>
                       <td>" . $row['priority'] . "</td>

                   <input type=hidden name=hidden value=" . $row['id'] . ">

<td><button type='submit' class='btn btn-primary btn-sm' name='update'>Update</button></td>

<td> <a class='btn btn-primary btn-sm'  data-toggle='modal' data-target='#myModal'>Info</a></td>
                </tr>"; 
        } 
        echo "
        </tbody>  
</table>"; 
?>   
</form>

Answer 1

I solve part of the problem with the code below. The only remaining problem is: the dropdownmenu is showing all the values plus the value that is stored in the database, so that value gets showed twice. (see pic)

 while($row = $result->fetch_assoc()){
                echo "<form action='' method=post>";
              echo "<tr class='info'>
           <input type=hidden name=hidden value=" . $row['id'] . ">
                        <td>" . $row['id'] . "</td> 
                        <td>" . $row['device'] . "</td>
                        <td>" . $row['model'] . "</td> 
                        <td>" . $row['problem'] . "</td>

     <td><select class='form-control col-sm-10' id='status' name='status'>

         <option value=". $row['status'] ." >" . $row['status'] . "</option>
                          <option value='new'>New</option>
                          <option value='progress'>Progress</option>
                          <option  value='wait'>Wait</option>
                          <option value='done'>Done</option>
                          <option value='close'>Close</option>
                                                                                                </select></td>

     <td><select class='form-control col-sm-10' id='priority' name='priority'>
     <option value=". $row['priority'] ." >" . $row['priority'] . "</option>
                                <option value='high'>High</option>
                                <option value='medium'>Medium</option>
                                <option  value='low'>Low</option>
    </select></td>


                           <td>" . $row['status'] . "</td>
                           <td>" . $row['priority'] . "</td>

     <td>   <button type='submit' class='btn btn-primary btn-sm' name='update'>Update</button></td>

      <td> <a class='btn btn-primary btn-sm'  data-toggle='modal' data-target='#myModal'>Info</a></td>
     </tr>";   echo"</form>";
            }     
            echo "
            </tbody>

        </table>";

Answer 2

You can use another TD for each row of your table:

"<td>" . $row['name'] . '</td><td><a href="LINK">Edit</a>' . "</td>"

The LINK will be a link to your controller, in case you are sing a MVC pattern, and you can pass the id of que row you want to delete. I mean somethig like:

"<td>" . $row['name'] . "</td><td><a href='clientes/editar?id=" . $row['id'] . "'>Edit</a> . "</td>"

This way you can edit any row just by clicking a "Edit" link. Remember to implement in the controller the logic for updating the row with the specific ID. For this purpose you can use a ORM wich can help you with the database access.