Trying to use Datatabel to delete records

Trying to use Datatabel to delete records

zer0ukzer0uk Posts: 13Questions: 2Answers: 0
edited April 2020 in Free community support

Hi I have a website that allows users to store records and then view as stats, I want to be able to give the users the ability to delete a record , I was hoping to use the datatable api to do this but can't figure out what's gone wrong please can you help ?

<?php
// database connect
include('../db_connect.php');

session_start();

// If the user is not logged in redirect to the login page...
if (!isset($_SESSION['loggedin'])) {
    header('Location: login.php');
    exit();
}
?>


<!DOCTYPE html>

<html>
<head>
<title>MyTimeKeeper - Stats</title>

        <p>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        </p>
        
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

         
         
         <!-- Compiled and minified CSS -->
                <link rel="stylesheet" 
                href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">

                <!-- Compiled and minified JavaScript -->
                <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>

                <!--Import Google Icon Font-->
                <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

                <!-- font from font awsome -->
                <script src="https://kit.fontawesome.com/981c51473f.js" crossorigin="anonymous"></script>

<header>

        <?php
        include('../menu.php');
        ?>       


</header>

</head>

<br>
<br>

<style>
        @media screen and (min-width: 320px) and (max-width: 767px) and (orientation: portrait) {
  html {
    transform: rotate(-90deg);
    transform-origin: left top;
    width: 100vh;
    height: 100vw;
    overflow-x: hidden;
    position: absolute;
    top: 100%;
    left: 0;
  }
}
</style>

<body id="container"bgcolor=#fafafa>


        <div class="row">
        <div class="col s12 m10 l10 push-l1 push-m1">
                <div class="card white lighten-1 grey-text z-depth-5">
                        <div class="card-content">
         
                                <table id="rides" class="table table-striped table-bordered">
                                                <thead>
                                                        <tr>
                                                        <th>Rider</th>
                                                        <th>Ride Date</th>
                                                        <th>Ride Time</th>
                                                        <th>Course</th>
                                                        <th>Action</th>
                                                        </tr>
                                                </thead>
                                                <tbody>
                                                        <?php  
                                                                $var_username_id = mysqli_real_escape_string($conn, $_SESSION['id']);
            
                                                                $query = mysqli_query($conn, "SELECT tbl_rides.r_date,tbl_rides.r_time, tbl_courses.course_name,tbl_rides.bike,tbl_rides.comments,tbl_accounts.username,tbl_rides.r_private FROM tbl_rides, tbl_courses, tbl_accounts WHERE `r_private` = 'No' AND tbl_rides.course_id = tbl_courses.course_id AND tbl_rides.username_id = tbl_accounts.id");

                                                        while ($result =  mysqli_fetch_array($query)) {
                                                                echo "<tr>
                                                                <td>".$result['username']."</td>
                                                                <td>".$result['r_date']."</td>
                                                                <td>".$result['r_time']."</td>
                                                                <td>".$result['course_name']."</td>
                                                                <td><button>Delete</button></td>
                                                                </tr>";
                                                        }
                                                
                                                        ?>
         
                                </table>

                        </div>
                </div>
        </div>
<br>
<br>
                                
<br>

                        </div>
                </div>
        </div>
        </div>


        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.js"></script>

<script>
                $(document).ready( function () {
                $('.sidenav').sidenav();
                $(".dropdown-trigger").dropdown();
         var table = $('#rides').DataTable({
            "columns": [
              null, 
              null,
              null,
              null,
              {
                "sortable": false
              }
            ]
          });          
      });
      $('#rides').on("click", "button", function(){
            console.log($(this).parent());
            table.row($(this).parents('tr')).remove().draw(false);
      });

</script>

</body>


<footer>
<br>
<br>
<br>
<br>
<div>
<?php
include('../footer.php');
?>
</footer>
</html>

Edited by Kevin:  Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

    See if something like this works:

          $('#rides').on("click", "button", function(){
                var row = $(this).closest('tr');
                table.row( row ).remove().draw(false);
          });
    

    Maybe this example will help:
    http://live.datatables.net/xijecupo/1/edit

    If not please provide a test case so we can take a look.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • zer0ukzer0uk Posts: 13Questions: 2Answers: 0
    edited April 2020

    Hi Kevin sorry still no joy even with this code , can I just check I want the record to be deleted from the mySQL db not just the visible table should this do this ?
    I am really new to all of this and struggling on this last part of my website (for now) , its no good if users can't delete / amend records they have created for now I am just going to get them to delete and re add.

    Cheers
    Andrew

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736
    edited April 2020 Answer ✓

    Oh, if you want to delete from the DB then you will need to use a jQuery ajax request to the server. The data option can be used to send the row ID to the server. The server script will need to get the ID and perform the removal from the DB.

    Then you will need to decide how to update the client. The server script can return a success or fail indicator and in the ajax success function you can look at the indicator; if true then use row().remove() if false show an error alert message.

    Alternatively you can use the Datatables Editor. It makes editing data in a database much easier but it does cost a small amount to purchase.

    Kevin

  • zer0ukzer0uk Posts: 13Questions: 2Answers: 0

    Thanks Kevin that was what I was thinking !, I like the flexibility of editor but with hardly any users at the minute its hard for me to fork out on it (but without it I am missing a key part of functionality) .. tricky , done want to be amending records in mySQL for people forever :-) ...
    Cheers Andrew

This discussion has been closed.