Remove a table row in MYSQL using PHP

Question

I've looked through the existing questions and i don't believe they are answering my question. I have created a table that gets populated using PHP and MySQL. I have got my add function working so the user can ADD a new row, however I would like the user to remove specific rows. Each row has a remove icon that I would like when clicked to remove ONLY that row.

Home.php (where table is created)

<table class="table table-bordered table-striped table-responsive">
    <tr class="header">
        <td>id</td>
        <td>Rep</td>
        <td>Date</td>
        <td>Name</td>
        <td>P_O</td>
        <td>Due Date</td>
        <td>Terms</td>
        <td>Aging</td>
        <td>Open Balance</td>
        <td>remove</td>
    </tr>
    <?php 
        while($row = mysql_fetch_array($query))
        {
            $className ="";
            if ($row['Aging'] >= 45) {
                $className="danger";
            }
            else if($row['Aging'] >= 25 && $row['Aging'] <= 44) {
                $className="warning";
            }

            echo "<tr class='$className'>";
            echo "<td>".$row['id']."</td>";
            echo "<td>".$row['Rep']."</td>";
            echo "<td>".$row['Date']."</td>";
            echo "<td>".$row['Name']."</td>";
            echo "<td>".$row['P_O']."</td>";
            echo "<td>".$row['Due_Date']."</td>";
            echo "<td>".$row['Terms']."</td>";
            echo "<td>".$row['Aging']."</td>";
            echo "<td>".$row['Open_Balance']."</td>";
            echo "<td><button type='button' class='btn btn-link'><i class='iconhover fa fa-check-circle fa-2x'></i></button></td>";
        }
    ?>
</table>

This is the remove button:

<button type='button' class='btn btn-link'><i class='iconhover fa fa-check-circle fa-2x'></i></button>

I would like it to remove the row that its currently part of when clicked. Any help?

Here is my new code, however it still doesn't seem to be deleting the row home.php:

while($row = mysql_fetch_array($query))
{
    $className ="";
    if ($row['Aging'] >= 45) {
        $className="danger";
    }
    else if($row['Aging'] >= 25 && $row['Aging'] <= 44) {
        $className="warning";
    }

    echo "<tr class='$className'>";
    echo "<td>".$row['id']."</td>";
    echo "<td>".$row['Rep']."</td>";
    echo "<td>".$row['Date']."</td>";
    echo "<td>".$row['Name']."</td>";
    echo "<td>".$row['P_O']."</td>";
    echo "<td>".$row['Due_Date']."</td>";
    echo "<td>".$row['Terms']."</td>";
    echo "<td>".$row['Aging']."</td>";
    echo "<td>".$row['Open_Balance']."</td>";
   echo "<td><button action='deletepage.php' method='POST' value='" .$row['id']. "' class='btn btn-danger'> Delete</button></td>";
}

deletepage.php:

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

if(isset($_GET['id'])){
    $userID = (int) $_GET['id'];
    if(!empty($_GET['id'])) {
        $delete = mysql_query("DELETE FROM Book1 WHERE id='$userID'");
    }
    if($delete) {
        echo "Record deleted successfully";
    }
    else {
        echo "Sorry, record could not be deleted";
    }
  }

Do you mean just remove from the page display or remove from the database? And if the latter, are you wanting to do it with ajax or a normal form submit? — Taplar, Oct 22 '15 at 17:26
This Q&A may be of help http://stackoverflow.com/q/14475096/ — Funk Forty Niner, Oct 22 '15 at 17:29
i would like to remove it from the database, i have it set up to fill the table with mysql, so in theory if it removes it from the database it will also remvoe it from the table. I want to do it with a normal form submit. — Charles L., Oct 22 '15 at 17:29
the link I gave you above will do exactly what you want. You just need to modify it to your needs. I've used that before with success. — Funk Forty Niner, Oct 22 '15 at 17:32
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 22 '15 at 17:45
this question is turning out to be a "how to", seeing the comments under some of the answers. I've practically given you everything you needed to do this and on a silver platter. — Funk Forty Niner, Oct 22 '15 at 17:46
@Fred-ii- your comment doesnt help me, the example you have given is using checkboxes. Not what i am looking for. — Charles L., Oct 22 '15 at 17:59
ok well now you're mixing APIs and a POST array syntax error. You also could easily have replaced that routine with buttons. — Funk Forty Niner, Oct 22 '15 at 18:00
@Fred-ii- can you take a look at the edited code and maybe see why im getting that error code? it makes no sense to me — Charles L., Oct 22 '15 at 18:17
@Charles - the method you're using for delete is horrible. You should never pass $_GET parameters to your database. Use $_POST, and use prepared statements. Example [here](http://stackoverflow.com/questions/13714443/how-to-delete-a-row-in-mysql-with-prepared-statements-on-php) — devlin carnate, Oct 22 '15 at 20:45

score 5 · Accepted Answer · edited Oct 31 '15 at 22:40

5

You can do by directing it to the delete page like this:

<a href="deletepage.php?<?php echo $_POST['userid']; ?>" class="btn btn-danger"> Delete</a>

Or with javascript AJAX call:

 <a href="#" class="btn btn-danger" onclick="confirmDeletion(<?php echo $_POST['userid']; ?>);"> Delete</a>

and use $.post to the deletion page

UPDATE: the deletion page deletepage.php may contain the following:

<?php
require('dbconn.php');
if(isset($_POST['id'])){
    $userID = (int) $_POST['id'];
    if(!empty($_POST['id'])){
        $delete = mysql_query("DELETE FROM users WHERE id='$userID'");
    }
    if($delete){
        echo "Record deleted successfully";
    }else{
        echo "Sorry, record could not be deleted";
    }
}
?>

users.php

<?php
require('dbconn.php');
$get = mysql_query("SELECT * FROM users");
while($row = mysql_fetch_array($get)) {
    echo '<p>';
    echo $row['id'] . ' - ' . $row['user'];
?>
    <a href="#" style="color:red;" onclick="confirmDeletion(<?php echo $row['id']; ?>);"> Delete</a>
    </p>
<?php } ?>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script>
    function confirmDeletion(id){
        $.post('deletepage.php', { id:id }, function(data){
            alert(data);
            location.reload();
        }); 
    }
</script>

P.S: It's very recommended to quit using mysql_query() in this way and instead use PDO to avoid SQL injection

edited Oct 31 '15 at 22:40

potashin

44,205
11
83
107

answered Oct 22 '15 at 17:32

Mi-Creativity

9,554
10
38
47

what would i put in the deletion page? – Charles L. Oct 22 '15 at 17:42
your code is giving me an error at line 5? says unexpected "{" – Charles L. Oct 22 '15 at 18:28
sorry it had one closing `)` at `(!empty($_GET['id'])`.. fixed it now, also you better tweak the code to suit you more if you can i just wrote it as how it could be – Mi-Creativity Oct 22 '15 at 18:32
It will get to the deletepage.php however its blank and this is in the url deletepage.php? – Charles L. Oct 22 '15 at 18:35
i edited my origional post to include all the new changes. its still now working. – Charles L. Oct 22 '15 at 18:42
your handling of the delete is awful. you shouldn't obtain the id from $_GET, which is easily changed by a user, and you should be using prepared statements to prevent sql injection. what if i changed the url to something like: ?id=15;DELETE * FROM yourtable – devlin carnate Oct 22 '15 at 18:48
@Charles, Check the last updated code, it is using AJAX POST and it is working 100% – Mi-Creativity Oct 22 '15 at 19:28
@devlincarnate i definitely agree about the POST against GET i just wrote an example to clarify how it works and i am sorry. but concerning the prepared statements i didn't use it because of his code this why, i have been using PDO prepared statement since 2012.. – Mi-Creativity Oct 22 '15 at 19:33
@devlincarnate still your `?id-;DELETE * FROM mytable` wouldn't work anyway because of the `(int)` it will only accept the number not anything else – Mi-Creativity Oct 22 '15 at 19:35
yeah, you added that later. and it [still doesn't mean you shouldn't use prepared statements](http://security.stackexchange.com/questions/13776/if-we-cast-a-value-is-it-still-necessary-to-escape-it-with-mysqli-real-escape-st). i'm clueless as to why you would give an example that uses mysql_query and then put a note under it saying not to use it. – devlin carnate Oct 22 '15 at 20:52
I added what later??? if you mean the `(int)` then no it was there from the beginning,, and again i did mysql_query() because in his code he is using it, I've already recommended him to use PDO in the P.S part, and this one was also in the original answer but still, thank you. – Mi-Creativity Oct 22 '15 at 20:56

devlin carnate · Answer 2 · 2015-10-22T18:24:48.003

1

I would change the button to include the value of the row id, by adding a value attribute to the button html:

value='" .$row['id']. "'

You can then capture the click event using jquery / javascript, and use an ajax call to remove the record from your database. It would look something like:

$('.btn-lnk').on('click',function() {
      var id = $(this).val();
      $.ajax({
          type: "POST",
          url: "yourPageThatDeletesRow.php",
          data: { id: id },
          success: function(response) {
              if(response === 'success') {
                 //delete row showing on the page
                 $(this).closest('tr').remove();
              } else {
                  //handle error
              }
          } //consider handling ajax error case
      });
});

The ajax call will execute the code on yourPageThatDeletesRow.php. You can get the row id using $_POST['id'] and delete the data using that id. If the delete is successful, echo the string 'success'. Consider returning the error from the database if it's not successful, and handling that case in the return of the ajax

Here is a simplified JS Fiddle showing how the passing of the id and row deletion work.

edited Oct 22 '15 at 18:24

answered Oct 22 '15 at 17:30

devlin carnate

8,309
7
48
82

this makes sense but what would i add to "yourpagedeletesrow.php" to actually delete the row? – Charles L. Oct 22 '15 at 17:41
similar to your code to insert the rows... you would connect to the database, issue a delete query to delete the item with the id contained in $_POST['id'] , and echo 'success' on success. There is a similar example [here](http://stackoverflow.com/questions/13714443/how-to-delete-a-row-in-mysql-with-prepared-statements-on-php) – devlin carnate Oct 22 '15 at 17:53
can you look over the code i just added, its giving me an error. – Charles L. Oct 22 '15 at 17:58
i had inverted brackets at the end of the ajax function. i've corrected that. – devlin carnate Oct 22 '15 at 18:25

score 0 · Answer 3 · answered Oct 22 '15 at 17:21

0

You will need a delete button if your going to do it the way ive done it.

if (isset($_POST['delete'])){
$userid = $_POST['userid'];
$sql = "DELETE FROM users WHERE userid = '$userid';";
if ($conn->query($sql) === true){
//echo '<a href="dbtablelist.php">Click here</a> to view modified    patients';

}
}

answered Oct 22 '15 at 17:21

DeltaRage

119
1
3
16

there's a few steps missing here. This answer alone won't solve OP's issue – charlietfl Oct 22 '15 at 17:22
yeah this doesnt really help me – Charles L. Oct 22 '15 at 17:24
1

@charlietfl actually, it's the only one that makes the most sense. OP's asking how to implement a delete method. I've even provided the OP a link on how to basically stitch everything together, but have since thrown out that "silver platter" like a Frisbee. *WhoOOooshhhh* – Funk Forty Niner Oct 22 '15 at 17:52

score -1 · Answer 4 · edited Oct 22 '15 at 17:46

-1

Use this code:

<?php $temp_pre_ID = $row['id'];?>
<INPUT TYPE="button" onClick="window.location='home.php?Action=del&CusID=<?php echo $temp_pre_ID;?>'" value="Delete">

edited Oct 22 '15 at 17:46

Jay Blanchard

34,243
16
77
119

answered Oct 22 '15 at 17:33

Dnyan

191
2
7
19

would this be in place of my current button? – Charles L. Oct 22 '15 at 17:34
yes u can use this as per your requirement.. – Dnyan Oct 22 '15 at 17:37
Why should the OP try this? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Oct 22 '15 at 17:46

Remove a table row in MYSQL using PHP

4 Answers4