Add Edit Button to Each Row of Table

General PHP Help
#1

I have a mysql database set up with code to search it using tabs. I want to add an edit button on each row that opens up the update.php I have already made. Any help would be great.

<?php

include("config.php");

if(isset($_POST['input'])){

    $input= $_POST['input'];
    

    $query= "SELECT * FROM tracking WHERE Title LIKE '%{$input}%' OR Analyst LIKE '%{$input}%'OR Agency LIKE '%{$input}%'
    OR Date LIKE '{$input}%'OR Requester LIKE '%{$input}%'OR Firearm LIKE '{$input}%'OR Summary LIKE '%{$input}%' OR ID LIKE '{$input}%'
    ORDER BY Date DESC";

    $results = mysqli_query($con, $query);

    if(mysqli_num_rows($results) >0){?>

<p><b>Total Records - <span id="total_records"></span></b></p>
<table class="table-responsive">
        <thead>
                <th>ID</th>
                <th>Title</th>
                <th>Analyst</th>
                <th>Agency</th>
                <th>Date</th>
                <th>Requester</th>
                <th>Firearm</th>
                <th>Packets</th>
                <th>Packets1</th>
                <th>Summary</th>
        </thead>
        <tbody>
            <?php

            while($row=mysqli_fetch_assoc($results)){

                $ID=$row['ID'];
                $Title=$row['Title'];
                $Analyst=$row['Analyst'];
                $Agency=$row['Agency'];
                $Date=$row['Date'];
                $Requester=$row['Requester'];
                $Firearm=$row['Firearm'];
                $Packets=$row['Packets'];
                $Packets1=$row['Packets1'];
                $Summary=$row['Summary'];

                ?>

                <tr>
                    <td><?php echo $ID;?></td>
                    <td><?php echo $Title;?></td>
                    <td><?php echo $Analyst;?></td>
                    <td><?php echo $Agency;?></td>
                    <td><?php echo $Date;?></td>
                    <td><?php echo $Requester;?></td>
                    <td><?php echo $Firearm;?></td>
                    <td><?php echo $Packets;?></td>
                    <td><?php echo $Packets1;?></td>
                    <td><?php echo $Summary;?></td>
                    <td><?php echo "<td><a href='update.php?ID=" . $row['ID'] . "'>edit</a></td>"; ?></td> //THIS IS THE LINE I ADDED
                </tr>
            
                <?php
            }

            ?>
        </tbody>
</table>

<?php

}else{

    echo "<h6 class='text-danger text-center mt-3'>No Entry Found</h6";
    }
}
?>
#2

Hello, ill give the same advice that was given to me in this forum in the first question I made. Use prepared staments they are more secure, and sooner or later you will want to go there, so better go now as you start.
U can read more about it directly from the manual on how to make a connection, update, etc.

https://www.php.net/manual/en/book.pdo.php

#3

Hi exeq, thanks for getting back to me

I did use a prepared statement for the initial entry but had problems with the editing part of that. I am checking out the link now.

#4

The opening and closing tags for a Table Data cell are <td></td>. Any content, such as a link, would be put between those. Now, count how many of each of those tags are in the line you have.

Here’s a laundry list of points about the posted code -

  1. You should use require for things your code must have.
  2. Require/include are not functions and the () around the filename are unnecessary typing.
  3. You should use a get method form for searching, the form should be on the same page with the code it submits to, and the form should be ‘sticky’ and repopulate the form field value with any existing search term.
  4. You should trim and validate all inputs before using them.
  5. In the case of an empty input, after being trimmed, you need to decide what if anything should be searched for, i.e. is an empty search term an error, in which case you should setup a message for the user and the query shouldn’t be executed at all or is this a case where the WHERE term should be left out of the query so that it will match all rows?
  6. The if/else failure code is usually much shorter than the success code. If you invert the condition being tested and put the failure code first, your code will be clearer.
  7. This database specific code is apparently inside the html document. If you put it above the start of the html document, fetch the data from the query into an appropriately named php variable, then test/loop over that variable in the html document, it is easier to write, test, debug, and maintain your code. If you switch to use the PDO database extension, that would be a good time to rearrange the code like this.
  8. Don’t copy variables to other variables for nothing. This is just a waste of your time typing. Just use the original variables.

If you switch to use the PDO extension, here’s a boiled down list of practices that will result in the simplest/best usage -

  1. When you make the connection, set the character set to match your database tables, set the error mode to exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc.
  2. Name the variable holding the connection $pdo, so that you can search for code that has or has not been converted.
  3. The only database statement errors that are recoverable by the visitor to a web site are when inserting/updating duplicate or out of range visitor submitted data. These are the only cases where you should have exception try/catch logic in your code. In all other cases simply let php catch and handle any database exception, by NOT putting in try/catch logic.
  4. Use a prepared query when supplying any external, unknown, dynamic value to a query when it gets executed.
  5. Use simple positional ? prepared query place-holders.
  6. Use simple implicit binding, by supplying an array of the input values to the execute([…]) call.

If you want examples of typical PDO connection code or what converting an existing query to be a prepared query would look like, just ask.

#5

This was an excellent response and I have a lot to work on. I have since edited my code to use PDO but it is far from perfect. Sending any examples would be much appreciated. I am posting another question shortly about pagination but this was very helpful.

#6

So I tried to update this code using PDO but couldn’t get it to work properly. It works now but does suffer from injection, not sure how to fix it properly.

$conn= mysqli_connect($host, $username, $password, $dbname);


if(isset($_POST['update']))
{
  $Title= $_POST['Title'];
  $Analyst= $_POST['Analyst'];
  $Agency= $_POST['Agency'];
  $Date= $_POST['Date'];
  $Requester= $_POST['Requester'];
  $Firearm= filter_input(INPUT_POST,"Firearm", FILTER_VALIDATE_BOOLEAN);
  $Packets= filter_input(INPUT_POST,"Packets", FILTER_VALIDATE_BOOLEAN);
  $Packets1= $_POST['Packets1'];
  $Summary= $_POST['Summary'];

  $query= "UPDATE tracking SET Title='$_POST[Title]', Analyst='$_POST[Analyst]', Agency='$_POST[Agency]',Date='$_POST[Date]',Requester='$_POST[Requester]',
  Firearm='$Firearm',Packets='$Packets', Packets1='$_POST[Packets1]',Summary='$_POST[Summary]'  where ID='$_POST[ID]'";

$query_run= mysqli_query($conn, $query);
#7

The reason we keep suggesting the PDO extension is because it is simple and consistent, able to treat the result from both a non-prepared and a prepared query exactly the same. The mysqli extension has a completely different set of statements for a non-prepared and a prepared query, so learning how to do a prepared query with the mysqli extension is as much work as learning an additional database extension. If you are going to go to that much trouble, you might as well just learn the much better PDO extension.

The main point of a prepared query is they separate the parsing (and planning of the query execution) from the evaluation of the data values, so that any sql special characters in a value cannot break the sql query syntax, which is how sql injection is accomplished.

To convert a query that has the values in it to a prepared query -

  1. Remove (and keep for later) the php variables, any single-quotes, any {}, and any concatenation dots around the variables from the sql query and replace each with a ? place-holder.
  2. Prepare the query.
  3. Call the execute([…]) method with an array consisting of the variables you removed in step #1.

For the UPDATE query you have posted, for just the first two fields, this would look like -

  $sql= "UPDATE tracking SET Title=?, Analyst=? WHERE ID=?";
  $stmt= $pdo->prepare($sql);
  $stmt->execute( [$_POST['Title'], $_POST['Analyst'], $_POST['ID'] ]);

Your post method form processing code should -

  1. Detect if a post method form was submitted.
  2. Keep the form data as an array, then operated on elements of this array variable throughout the rest of the code, i.e. writing code copying variables to other variables is a waste of your time.
  3. Trim all the inputs at once. When you do item #2 above, you can do this with one single line of code.
  4. Validate all inputs separately, storing user/validate errors in an array, using the field name as the array index.
  5. After the end of all the validation logic, if the array holding the errors is empty, use the submitted data.
  6. After the end of all the form procesisng code, if there are no errors, execute a redirect to the exact same url of the current page to cause a get request for that page.
  7. If there are errors at step #5, the code would continue on to display the html document, display any errors, redisplay the form, populating the form fields with the submitted form values, so that the user doesn’t need to keep editing the values over and over.
#8

Thanks so much. I plan on working on this Monday. I did use the PDO method for the initial form submit, but the update php I need to change and I think I can figure it out now quite easily with your advice above. I am going to reread this a few times, thanks again.

#9

update worked very smoothly when I edited it today in just a couple lines of code. And sql injection is gone. Thanks again. Mike

Sponsor our Newsletter | Privacy Policy | Terms of Service