How to remove a row from MySQL table data using html delete button in PHP

Question

I am working on a project, for school. I currently have a product page to display an assortment of item includes image, description and price etc...

Under each product I have a delete button, when logged in as admin, which displays fine.

     if (is_admin())
        echo '<a href ="#"><button>Delete item</button></a>'; }

I want to know how remove the row of data from MySQL table on clicking the delete button.

<?php
        // Include need php scripts
        require_once ("Includes/simplecms-config.php");
        require_once ("Includes/connectDB.php");
        include ("Includes/header.php");

        if (!empty($_GET['cat'])) {
            $category = $_GET['cat'];
            $query = mysqli_query($db, "SELECT * FROM products WHERE category = '".$category."'");
        } else {
            $query = mysqli_query($db, "SELECT * FROM products");
        }

        if (!$query) {
            die('Database query failed: ' . $query->error);
        } 

       $deleted = mysql_query($db, "DELETE FROM products");  
    ?>

    <section>
        <div id="productList">
            <?php
                $row_count = mysqli_num_rows($query);
                if ($row_count == 0) {
                    echo '<p style="color:red">There are no images uploaded for this category</p>';
                } elseif ($query) {
                    while($products = mysqli_fetch_array($query)){             
                        $file = $products['image'];
                        $product_name = $products['product'$];
                        $image_id = $products['id'];
                        $price = $products['price'];
                        $desc = $products['description'];
                        echo '<div class="image_container">';
                        echo '<a href="viewProduct.php?id=' . $image_id . '"><p><img src="Images/products/'.$file.'" alt="'.$product_name.'" height="250" /></p>';
                        echo '' . $product_name ."</a><br>$" . $price . "<br>" . $desc;


                        echo '</div>';

                            if (is_admin()){
                           echo '<a href ="#"><button>Delete item</button></a>'; 
                          }

                     } 
                 } else {
                     die('There was a problem with the query: ' .$query->error);             
                 } 
                 mysqli_free_result($query);     
            ?>
        </div>
    </section>
    <?php include ("Includes/footer.php"); ?>



<!-- end snippet -->

One observation, you should not be mixing `mysql` libraries as you are using both `mysqli_*` and `mysql_*`. Remove all instances of the `mysql_*` as in `$deleted = mysql_query(...` — Rasclatt, Nov 26 '16 at 07:04
Another side note, you should not be doing this: `"SELECT * FROM products WHERE category = '".$category."'"`, it is an SQL injection vulnerability. You will want to bind parameters on that variable. — Rasclatt, Nov 26 '16 at 07:07
If you have a form wrapping your delete, you can use hidden fields to add an action name so you can have an `if` that is looking for the action value. `` Then when you click the delete button, you will just use `if(isset($_POST['action']) && $_POST['action'] == 'delete') {//do sql to remove item }` — Rasclatt, Nov 26 '16 at 07:11
You should delete your item with `Http Post` method, not with . In addition, you should not use mysql_* or mysqli_* functions as they are gradually deprecating. I strongly recommend you to look at this tutorial for Php Crud functionality. https://www.startutorial.com/articles/view/php-crud-tutorial-part-1 — Steve.NayLinAung, Nov 26 '16 at 08:01

Julian · Answer 1 · 2016-11-26T07:55:38.147

0

One approach

Change the button to a a element and make the href look like this:

yourdomain.tld/products/delete/{id}

You have to echo the primary key from your mysql database at the id position. It will look like this:

yourdomain.tld/products/delete/5

Then you have to change your .htaccess in a way that all requests go to your index.php in your root project. At the index.php you can do the actually query then.

Update

Keep in mind that anyone visiting this URL can delete products with this approach. You have to make sure that only the admin can do that. The preferred method is a POST request.

You can also send the primary key parameter to your PHP script you are just showed. With this approach you don't need to edit your .htaccess. You may pass it as an URL parameter like this:

yourdomain.tld/your-script.php?delete-product={id}

In your script you can get the parameter like this:

<?php
    if (isset($_GET['delete-product'])) {
    // your mysql query to delete the product
} else {
   // something else
}

edited Nov 26 '16 at 07:55

answered Nov 26 '16 at 07:15

Julian

1,380
12
28

As a general rule you shouldn't modify data with a get request like that. But it's a good idea to do that but with a post – Antony Thompson Nov 26 '16 at 07:24
If you change your answer then I'll change my down vote. Here's an answer to why btw. http://stackoverflow.com/questions/786070/why-should-you-delete-using-an-http-post-or-delete-rather-than-get – Antony Thompson Nov 26 '16 at 07:27
I said that he has to make sure that only the admin can delete the product. The whole logic for it can be implemented inside the isset block. – Julian Nov 26 '16 at 07:28

score 0 · Answer 2 · edited Feb 10 '17 at 02:19

If you want to delete the entire row of an record from your db you can do like this. So that you can pass the product id and delete the row. Just bind the id with query using bind parameters concept

$knownStmt=mysqli_prepare($conn, "DELETE FROM  `YourTableName` WHERE `pdt_id` = ?;");
if( $knownStmt ) {
    mysqli_stmt_bind_param($knownStmt,"d",$pdt_id);
    mysqli_stmt_execute($knownStmt); 
    mysqli_stmt_close($knownStmt);
}

Antony Thompson · Answer 3 · 2016-11-26T07:54:40.450

You should post to a url with the id in the post data, then redirect back to where you were.

<?php
//html on productpage
if(isset($_GET['product_deleted'])){
    if($_GET['product_deleted'] === 'true'){
        echo 'The product was deleted';
    }else{
        echo 'The product could not be deleted';
    }
}
if (is_admin()){
    /**
     * It's a good idea for the page that deletes to be different from the one your on, so that when you redirect back, 
     * they can refresh the page without getting something 
     * along the lines of 'refreshing with page will re-post the data'
     */
    ?>
    <form method="POST" action="/product/delete.php">
        <button>Delete item</button>
        <input type="hidden" name="id" value="<?php echo $image_id; ?>" />
    </form>
    <?php
}

//PHP on /product/delete.php
if(is_admin() && $_SERVER['REQUEST_METHOD'] == 'POST' && !empty($_POST['id'])){
    //delete sql here
    header('Location: /productpage.php?product_deleted=true'); //redirect back
}

How to remove a row from MySQL table data using html delete button in PHP

3 Answers3

One approach

Update