Categories: javascript, php, html, css, sql

Modifying SQL entries with PHP hreferences

1 answer

So I'm having trouble understanding how to go about modifying SQL entries individually while at the same time printing out all the results to a screen. I have root_user.php print out all of the users besides root_user onto the screen and have hrefs next to each of the users asking to either approve or deny. In my mind I'm going to need approve.php and deny.php in order to write a SQL query, but I don't know how to grab an individual entry from the list and then modify that in the approve or deny.php files. Right now I just have Approve and Deny links that send you to an empty approve or deny.php. Does anybody know where I can go with this?

My Code so far:

<?php require('database.php'); $query = 'SELECT *           FROM credentials           WHERE access_level != 0           ORDER BY email'; $statement = $db->prepare($query); $statement->execute(); $credentials = $statement->fetchAll(); $statement->closeCursor();  ?>  <!DOCTYPE html> <html>  <!-- the head section --> <head>     <title>root</title>     <link rel="stylesheet" type="text/css" href="main.css" /> </head>  <!-- the body section --> <body> <main>     <section>         <table>             <tr>                 <th>Email</th>                 <th>Access Level</th>                 <th class="right">Actions</th>             </tr>              <?php foreach ($credentials as $credential) : ?>             <tr>                 <td><?php echo $credential['email']; ?></td>                 <td><?php if($credential['access_level'] == 1)                             {                                 echo "Admin";                             }                            else                            {                                 echo "Scheduler";                            }                                                         ?></td>                 <td class="right"><a href="approve.php" class="button-class">Approve</a>                 <td class="right"><a href="deny.php" class="button-class">Deny</a></td>              </tr>             <?php endforeach; ?>         </table>     </section> </main> <footer></footer> </body> </html> 
Received answer to this question:
The best answer according to the author of the question:

Generally the approach would be to include an identifier on the link. For example:

<a href="approve.php?id=<?php echo $credential['id']; ?>" class="button-class">Approve</a> 

I'm assuming id exists in these records, but any identifier would do. If $credential['email'] is how you uniquely identify a record, that works just as well. (Though you may want to URL-encode the value.)

Then in approve.php you would get the value from $_GET["id"]. The steps then would be to:

  • Validate that the current logged in user can perform this operation on that record. Never assume that because they requested this page that they must have clicked on a link you showed them and therefore must have access. These things are easily spoofed. Always validate.
  • Use the value in $_GET["id"] as a query parameter in a prepared statement to perform the operation in your data. If you're not familiar with SQL injection and how to prevent it, this is a good place to start.