Delete MySQL row from PHP while loop

Question

I have a while loop displaying my database columns and rows in table.

I want make a button which can delete specific MySQL row but unfortunately I can't select which of all rows have to be deleted simply because I use "while loop" to display my database content in HTML.

Providing picture for better understanding what I want to do:
So yeah, I want that when the green button is clicked - send MySQL query to delete this row (which is existing in my DB).

Providing code:

<?php
//CONECT TO MYSQL
include 'database.php';

//GET VALUES
        $sql = "SELECT * FROM amountcontainer";
        $result = $conn->query($sql);


        if ($result->num_rows > 0) 
        {
            //Table headlines - NOT A PHP
            echo "<table class='moneytable'>
                    <tr>
                        <th style='background-color: #2d323a; color: white;'>Date</th>
                        <th style='background-color: #2d323a; color: white;'>Amount</th>
                        <th style='background-color: #2d323a; color: white;'>Reason</th>
                    </tr>";

            // output data of each row
            while($row = $result->fetch_assoc()) 
            {

                //RED //Make statement of amount red if it has a " - "
                if(strpos($row['amount'],'-') !== false)
                {

                    echo 
                    "
                        <tr>
                            <th style='font-weight: normal;background-color: #ff9999;' class='row-time'>" . $row['time'] . "</th>
                            <th style='font-weight: normal;background-color: #ff9999;' class='row-amount'>" . $row['amount'] . "</th>
                            <th style='font-weight: normal;background-color: #ff9999;' class='row-reason'>" . $row['reason'] . "</th>
                        </tr>

                    ";

                }

                //NORMAL //Make statement of amount normal if it doesn't have a " - "
                else
                {
                    echo 
                    "
                        <tr>
                            <th style='font-weight: normal;' class='row-time'>" . $row['time'] . "</th>
                            <th style='font-weight: normal;' class='row-amount'>" . $row['amount'] . "</th>
                            <th style='font-weight: normal;' class='row-reason'>" . $row['reason'] . "</th>
                        </tr>

                    ";

                }


            }
            echo "</table>";
        } 

        else 
        {
            echo "0 results";
        }

Do you have a primary index or unique key on the table that you can reference to delete the row? — Ohgodwhy, Dec 03 '15 at 04:12
I don't have. I was thinking to do the same but can't figure out how. — Martinez, Dec 03 '15 at 04:16
[Have a look here, friend](http://stackoverflow.com/questions/15255304/how-add-unique-key-to-existing-table-with-non-uniques-rows) — Ohgodwhy, Dec 03 '15 at 04:16

score 1 · Answer 1 · answered Dec 03 '15 at 04:34

This sounds like a place for ajax. Heres a crude example that might give you a good starting point.

<?php
while($row = $result->fetch_assoc()) {
    $isNegative = strpos($row['amount'],'-') !== false;

    echo "<tr class='record' data-id=\"{$row["id"]}\">";
    //Use ternary operator & css classes
    echo "<td class='standard-record ".$isNegative ? "color-red" : "" ."''>{$row["record"]}</td>";
    echo "</tr>";
}
?>
<script>
    $(".record").click(function(){
        var request = {
            id: $(this).data("id")
        };
        $.post( "delete-record.php", request, function(data){
            $(this).remove();
        });
    });
</script>

score 1 · Answer 2 · answered Dec 03 '15 at 04:36

1

Change this

if(strpos($row['amount'],'-') !== false) # wrong way

to this

if($row['amount'] < 0) # detecting negative numbers

answered Dec 03 '15 at 04:36

Abdulla Nilam

36,589
17
64
85

Tom Berthold · Accepted Answer · 2015-12-03T04:52:54.417

1

First, only use <th> elements for the table headers. For the actual data cells use the <td> element.

To allow deletion of individual database rows you can include links in your table. In your loop that creates the table rows:

                    <tr>
                        <td>" . $row['time'] . "</td>
                        <td>" . $row['amount'] . "</td>
                        <td>" . $row['reason'] . "</td>
                        <td><a href='?deleteId=$row[keyID]'>Delete</a></td> 
                    </tr>

In this case, when the "Delete" link is selected it calls your same script with a $_GET variable: "deleteId" which you can test for and if found, perform the delete of the row. You can also embed the table in an html form and add a submit button to a cell in each row, with the value of the submit set to the row id to delete.

edited Dec 03 '15 at 04:52

answered Dec 03 '15 at 04:46

Tom Berthold

552
4
4

As @Ohgodwhy mentioned you will need a primary key for the table. Almost without exception every table in your database should have a primary key. – Tom Berthold Dec 03 '15 at 04:51
I think this might be the answer. How can I call function with this "GET" method though? I just tested it and I see it marks my row, which is great! – Martinez Dec 03 '15 at 05:02
In the example the primary key of the table is "keyID" in your table you will need to have column that represents the unique identifier of a row in your table. When you click the link it will call the same page but with a url parameter. You can test for it like: if($_GET['deletedId']) { $sql = "DELETE FROM MyTable WHERE keyID = '$_GET[deletedId]' "; – Tom Berthold Dec 03 '15 at 05:12
Also FYI use prepared statements or at least mysqli_real_escape_string on the $_GET variable before using it in the delete statement to prevent SQL-Injection. Good luck! – Tom Berthold Dec 03 '15 at 05:24
It says "Notice: Undefined index: deleteId" when I put this on the code. EDIT: I have mistake , sorry. Will tell you what's going on tho! – Martinez Dec 03 '15 at 05:25

sumon cse-sust · Answer 4 · 2015-12-03T05:09:08.513

1

You need to add an extra

<th style='font-weight: normal;' class='row-reason'><a class="crossBtn" href="?del_id=".$row['id'].">&nbsp;</a></th>

And the get the del_id and delete this specific records.

edited Dec 03 '15 at 05:09

answered Dec 03 '15 at 05:01

sumon cse-sust

434
4
8

Delete MySQL row from PHP while loop

4 Answers4

Linked