How to create a button to delete a record via PHP?

HollowPantera

Dear friends, sorry for the long question, but I wanted to ask and know how to create a button to delete a record with a request to the database via PHP.
I tried and trying many options and none served me (well actually yes, but I could not refresh the table on the page that was).
I'm giving up, so I turn to you, not to lose and stop using this great plugin.

Here's the code I'm using.

PHP Code (rescued the records show via ajax: "listRecords.php"):

$cn = mysql_connect("localhost","root");
$db = mysql_select_db("dbsys",$cn);
$sql = mysql_query("SELECT * FROM users",$cn);

function deleteRow($parameter)
{
    $delete = "<a href='javascript:;'";
    $delete .= " OnClick=";
    $delete .= "DeleteThis('".urlencode($parameter)."')>Click</a>";
    return $delete;
}

$first = 0; 
$json = '{"data":[';

 while ($row=mysql_fetch_array($sql))
 {

    if ($first++) $json .=',';
    $json .= '{"DT_RowId":"row_'.$row['id'].'" ,';
    $json .= '"cod":"'.$row['id'].'", ';
    $json .= '"nom":"'.utf8_encode($row['name']).'",';
    $json .= '"val":"'.$row['age'].'",';
    $json .= '"acc":"'.deleteRow($row['id']).'"}';

}


$json .= ']}';
print $json; 

HTML Code (Table):

<div class="container">
  <table class="table table-condensed table-hover table-striped table-bordered text-center" id="dataMysql">
    <thead>
        <tr>
            <th class="text-center">ID</th>
            <th class="text-center">Name</th>
            <th class="text-center">Age</th>
            <th class="text-center">Action (Delete or Edit)</th>
        </tr>
    </thead>
  </table>
</div>

JQ/JS Code:

<script src="js/jquery.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/bootstrap.js"></script>
<script src="js/dataTables.bootstrap.js"></script>
<script type="text/javascript" charset="utf-8">

    var table = $("#example");
    var oTable = table.dataTable({
            "ajax" : "listRecords.php",
            columns: [
                { data: "cod" },
                { data: "nom" },
                { data: "val" },
                { data: "acc" }
            ],
            "aLengthMenu": [[10, 20, 50, 100, -1], [10, 20, 50, 100, "Todos"]],
            "language": {
                  "url": "Spanish.json"
              }
      });

  $(document).ready(function() {
      var oTable;
  });

    function DeleteThis( id )
    {
        var confirmmssg = confirm("¿Delete This?");
        if (confirmmssg ){
          $.ajax({
             type: "get",
             url: "deleteRecord.php",
             data: "id=" + id,
             success: function(){
                     oTable.fnDeleteRow( $('#example tbody tr:eq(0)')[0] );
                  }
         });
        }
    }


</script>

PHP Code Delete Row (DeleteRecord.php)

$id = $_GET['id'];
$cn = mysql_connect("localhost","root");
$db = mysql_select_db("dbsys",$cn);
$sql = mysql_query("DELETE FROM users WHERE id = ".$id."",$cn );

The plugin works for me great, but I can not do what I want, I cordially grateful if I could guide to create this button and be able to do what I want.
Greetings friends.

allan

I don't see any code that will trigger the delete? What activates it?

Editor has delete row abilities and PHP libraries for the server-side if you are interested.

Allan

HollowPantera

Dear Allan:

I have a code that removes fine, and the table refreshes me, and sends me to the first page, but the code works better than I had.

Now I want to do is add an id to each TR making a ajax call to the file where I have the records, because currently I do everything in the same file.

The first question I asked was about that, but I could not make it work.

This the code that I have so far (sorry for posting it here, but I can not edit the topic):

What I want to achieve:

<tbody>
<tr id="delete-1">
<td>1</td>
<td>Milk</td>
<td>1.000</td>
</tr>
<tr id="delete-2">
<td>2</td>
<td>Cheese</td>
<td>2.000</td>
</tr>
</tbody>

JS Code:

<script type="text/javascript" charset="utf-8">
  $(document).ready(function() {

    var table = $("#example");
    var oTable = table.dataTable({
      "language": {
                  "url": "lenguaje.json"
                  }

    });

    $(document).on("click", ".delete", function() {
        var confirmmssg  = confirm("¿Delete This?");
        if(confirmmssg ){
            var celeb_id = $(this).attr("id").replace("delete-", "");
            var parent = $("#"+celeb_id);
            var row = $(this).closest("tr").get(0);
            $.ajax({
              type: "get",
              url: "deleteRecord.php?id="+celeb_id,
              data: "",
              success: function() {
                  $(parent).fadeOut(200,function() {
                    $(parent).remove();
                      oTable.fnDeleteRow(oTable.fnGetPosition(row));
                    });
              }
            });
          }
    });
  });

</script>

HTML Code:

          <table id="example">
              <thead>
                  <tr>
                      <th class="text-center">ID</th>
                      <th class="text-center">Name</th>
                      <th class="text-center">Age</th>
                      <th class="text-center">Action (Delete or Edit)</th>
                  </tr>
              </thead>
              <tbody>
                <?php

                    $cn = mysql_connect("localhost","root");
                    $db = mysql_select_db("dbsys",$cn);
                    $sql = mysql_query("SELECT * FROM users",$cn);

                    while($row = mysql_fetch_array($sql)){?>
                      <tr id="<?php echo $row[0]?>">
                        <td><?php echo $row[0] ?></td>
                        <td><?php echo $row[1] ?></td>
                        <td><?php echo $row[2] ?></td>
                        <td><a href="javascript:;" id="delete-<?php echo urlencode($row[0]);?>" class="delete">Delete</a></td>
                      </tr>

                  <?php  }

                ?>
              </tbody>
          </table>

PHP code (This code do not use it, because I do not call this file with ajax, and still failed to make every TR have your ID):

$cn = mysql_connect("localhost","root");
$db = mysql_select_db("dbsys",$cn);
$sql = mysql_query("SELECT * FROM users",$cn);
@mysql_query("SET-UTF8");

$first = 0; 
$json = '{"aaData":[';

function deleteProducto($parametro){

    $eliminar = "<a href='javascript:;' id='delete-".$parametro."' class='delete'>Borrar</a>";
    return $eliminar;

}

 while ($row = mysql_fetch_array($sql))
 {

    if ($first++) $json .=',';
    $json .= '{"DT_RowId":"row_'.$row[0].'" ,';
    $json .= '"cod":"'.$row[0].'", ';
    $json .= '"nom":"'.utf8_encode($row[2]).'",';
    $json .= '"val":"'.$row[4].'",';
    $json .= '"acc":"'.deleteProducto($row[0]).'"}';

}

$json .= ']}';
print $json; 

Waiting for help. Greetings friends.

allan

If you are going to Ajax load the data after a delete, I would suggest to ajax load it in the first instance - see this example.

Allan

HollowPantera

Dear Allan, I make the call records via Ajax.

To assign an ID to each TR using this code:

       "fnCreatedRow": function (nRow, aData, iDataIndex) {
           $ (nRow) attr ('id', aData [0]).;
       }

And I worked excellent. At last I can do what I want.
Too bad the refresh takes me to the first page.

Very grateful for all your help Allan, now only remains to refresh the page where I am.

Greetings from Chile.