Deleting a single row with a button

Anonymous · Jan 25, 2020

Hey everyone, I would like to delete a whole row from sql with php.
For that I would like to use the Button that is on each row. But i cant really figure it how to do, i tried to create a statement ($delete) but that doesnt work either

Code:

<head>
  <title>Tables</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</head>

<?php

$server = 'localhost';
$user = 'root';
$password = '';
$db = 'bfi';

$connection = new mysqli($server, $user, $password, $db);

if ($connection->connect_error) {
    echo $connection->connect_error;
}

$sql = "SELECT * FROM employees";
$result = $connection->query($sql);
$Delete = "DELETE FROM employees WHERE LastName='Fuller'";
echo '<table class="table table-dark table-hover">';
echo '<thead><tr>';
echo '<th>ID</th>';
echo '<th>LastName</th>';
echo '<th>FirstName</th>';
echo '<th>BDay</th>';
echo '<th>Delete</th';
echo '</tr></thead>';

while ($row = $result->fetch_object()) {
    echo "<tr>";
    echo "<td>" . $row->EmployeeID . "</td>";
    echo "<td>" . $row->LastName . "</td>";
    echo "<td>" . $row->FirstName . "</td>";
    echo "<td>" . $row->BirthDate . "</td>";
    echo "<td>" . "<a class='btn btn-danger' href='#'>Delete</a>" . "</td>";
    echo '</tr>';

}

$connection->close();

Anonymous · Jan 25, 2020

My first suggestion is to try to keep your HTML separated as much as possible from PHP code.

Here's an example of my delete blog post ->

Code:

            <h1>The Miniature Journal</h1>
            <div id="gallery" class="picture-box" data-total="{$journal|count}" >
                {counter start=-1 skip=1 print=false}
                <div class="article">
                    {foreach $journal as $cms}
                        <h2>{$cms.heading} <span class="subheading">by {$cms.author} on {$cms.date_added}</span></h2>
                        <a class="myLightBox" href="{$cms.image_path}" title="Picture Gallery" data-picture="{counter}" data-exif="{if $cms.Model}{$cms.Model}   ---   {$cms.FocalLength}    {$cms.Aperture}    {$cms.ISO}    {$cms.ExposureTime}{/if}"><img class="box" src="{$cms.thumb_path}" alt="Picture for Journal Entry"></a> 
                        <hr>
                        <p>{$cms.content|nl2br}</p>
                        <a class="edit" href="edit.php?article_id={$cms.id}">Edit</a>
                        <a class="delete" href="delete_entry.php?id={$cms.id|escape:'url'}" data-confirm="Do you really want to delete this item?">Delete</a>
                        <hr>
                    {/foreach}
                </div>
            </div>

I use a Smarty Templating system to output my HTML/PHP though I do try to keep the logic of the PHP separated as much as possible. This is basically just outputting the necessary data to user.

Notice I only have one row for deletion.

Then here's my deletion of the record in a class that I wrote:

Code:

    public function deleteRecord(int $id = NULL) {
        $db = DB::getInstance();
        $pdo = $db->getConnection();
        $this->query = "DELETE FROM journal WHERE id=:id";
        $this->stmt = $pdo->prepare($this->query);
        $this->stmt->execute([':id' => $id]);
        return \TRUE;
    }

Well, I actually have another php file to utilize the class, but this is the meat and potatoes of the deletion. The method is basically a function with a few differences.

I don't try to catch errors as I have error reporting on and this is just a simple CMS that I wrote for my website.

I was thinking it would be wise to show the php file that the anchor tag element refers to, in order to better understand.

Code:

<?php

require_once '../private/initialize.php';

use Library\CMS\CMS;

confirm_user_logged_in();
is_session_valid();

$cms = new CMS();
if (isset($_GET['id']) && filter_var($_GET['id'], FILTER_VALIDATE_INT)) {
    /*
     * Delete the image and the record from the database table journal
     */
    $id = filter_var($_GET['id']);
    $image_path = $cms->readImagePath($id);
    $thumb_path = $cms->readThumbPath($id);

    unlink($image_path);
    unlink($thumb_path);
    
    $result = $cms->deleteRecord($id);
    if ($result) {
        header("Location: member_page.php");
        exit();
    } 
}

Deleting a single row with a button

Anonymous

Guest

Anonymous

Guest