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
– Ohgodwhy, Commented Dec 3, 2015 at 4:12
I don't have. I was thinking to do the same but can't figure out how. — Martinez
– Martinez, Commented Dec 3, 2015 at 4:16

Tom Berthold · Accepted Answer · 2015-12-03 04:52:54Z

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 3, 2015 at 4:52

answered Dec 3, 2015 at 4:46

Tom Berthold

5524 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Berthold Over a year ago

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.

Martinez Over a year ago

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!

Tom Berthold Over a year ago

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 Over a year ago

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!

Martinez Over a year ago

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!

Matt Oaxaca · Accepted Answer · 2015-12-03 04:34:19Z

1

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>

answered Dec 3, 2015 at 4:34

Matt Oaxaca

1961 silver badge10 bronze badges

Comments

Abdulla Nilam · Accepted Answer · 2015-12-03 04:36:28Z

1

Change this

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

to this

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

answered Dec 3, 2015 at 4:36

Abdulla Nilam

39.6k18 gold badges85 silver badges114 bronze badges

Comments

sumon cse-sust · Accepted Answer · 2015-12-03 05:09:08Z

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 3, 2015 at 5:09

answered Dec 3, 2015 at 5:01

sumon cse-sust

4544 silver badges8 bronze badges

Collectives™ on Stack Overflow

Delete MySQL row from PHP while loop

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related