how can I display database data in datatables dynamically

how can I display database data in datatables dynamically

Alpha.ArsAlpha.Ars Posts: 5Questions: 1Answers: 0

atm I have my code working fine, but I'm getting at a point where I need to have dynamic data values.

I'm searching for a good way to handle this, and I'll keep searching, but I'm posting this hoping someone might help me

my code:

index.php

<div class="card shadow mb-4">
            <div class="card-header py-3">
              <h6 class="m-0 font-weight-bold text-primary">Server DataTable</h6>
            </div>
            <div class="card-body">
              <div class="table-responsive">
                <table class="table table-bordered" id="sample_data" width="100%" cellspacing="0">
                <thead>
    <tr>
      <th scope="col">ID</th>
      <th scope="col">Server name</th>
      <th scope="col">Game / Platform</th>
      <th scope="col">Version</th>
      <th scope="col">Type</th>
      <th scope="col">Auto start</th>
      <th scope="col">Status</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
                </table>
                <div class="error-text alert alert-danger">NOT OPTIMISED ON SMARTPHONE - PLEASE USE A COMPUTER!</div><br>


              </div>
            </div>
          </div>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){

 var dataTable = $('#sample_data').DataTable({
  "processing" : true,
  "serverSide" : true,
  "order" : [],
  "ajax" : {
   url:"fetch.php",
   type:"POST"
  }
 });

 $('#sample_data').on('draw.dt', function(){
  $('#sample_data').Tabledit({
   url:'action.php',
   dataType:'json',
   columns:{
    identifier : [0, 'id'],
    editable:[[5, 'auto_start', '{"0":"No","1":"Yes"}']]
   },
   restoreButton:false,
   onSuccess:function(data, textStatus, jqXHR)
   {
    if(data.action == 'delete')
    {
     $('#' + data.id).remove();
     $('#sample_data').DataTable().ajax.reload();
    }
   }
  });
 });
  
}); 
</script>

fetch.php


<?php //fetch.php include('database_connection.php'); $column = array("id", "server_name", "status", "auto_start", "type"); $query = "SELECT * FROM list "; if(isset($_POST["search"]["value"])) { $query .= ' WHERE server_name LIKE "%'.$_POST["search"]["value"].'%" OR status LIKE "%'.$_POST["search"]["value"].'%" OR id LIKE "%'.$_POST["search"]["value"].'%" OR auto_start LIKE "%'.$_POST["search"]["value"].'%" OR exec LIKE "%'.$_POST["search"]["value"].'%" OR type LIKE "%'.$_POST["search"]["value"].'%" '; } if(isset($_POST["order"])) { $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' '; } else { $query .= 'ORDER BY id ASC '; } $query1 = ''; if($_POST["length"] != -1) { $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length']; } $statement = $connect->prepare($query); $statement->execute(); $number_filter_row = $statement->rowCount(); $statement = $connect->prepare($query . $query1); $statement->execute(); $result = $statement->fetchAll(); $data = array(); foreach($result as $row) { $sub_array = array(); $sub_array[] = $row['id']; $sub_array[] = $row['server_name']; $sub_array[] = $row['type']; $sub_array[] = $row['version']; $sub_array[] = $row['exec']; if ( $row['auto_start'] == 0 ) { $checkauto_start = "No"; } else { if ( $row['auto_start'] == 1 ) { $checkauto_start = "Yes"; } } $sub_array[] = $checkauto_start; if ( $row['status'] == 0 ) { $checkstatus = "Down"; } else { if ( $row['status'] == 1 ) { $checkstatus = "Running"; } } $sub_array[] = $checkstatus; $data[] = $sub_array; } function count_all_data($connect) { $query = "SELECT * FROM list"; $statement = $connect->prepare($query); $statement->execute(); return $statement->rowCount(); } $output = array( 'draw' => intval($_POST['draw']), 'recordsTotal' => count_all_data($connect), 'recordsFiltered' => $number_filter_row, 'data' => $data ); echo json_encode($output); ?>

database_connection.php


<?php //database_connection.php $connect = new PDO("mysql:host=localhost; dbname=servers", "xxxx", "xxxxx"); ?>

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    atm I have my code working fine, but I'm getting at a point where I need to have dynamic data values.

    I'm not clear what the problem is with your code, or what you're trying to achieve? Please can you elaborate and give more information.

    Colin

  • Alpha.ArsAlpha.Ars Posts: 5Questions: 1Answers: 0

    Yes for sure, What I tried to explain was. I'm trying to get a datatable that shows all my
    db rows ( ex. if I'm adding a new value in my db, I'm expecting it to show my new value) Right now it's only showing the value's prensent during the fetch.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    You should just be able to call ajax.reload() to get the updated data from the db,

    Colin

  • Alpha.ArsAlpha.Ars Posts: 5Questions: 1Answers: 0

    Ok perfect and where should I write this ?
    It's the first time I'm using it and I really not sure :smile:

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

    if I'm adding a new value in my db, I'm expecting it to show my new value)

    Guessing you are using jQuery ajax to save the data to the database. In the success function of the ajax request is where you would use ajax.reload().

    Basically it will go into the success callback of whatever method you are using to save the data.

    Kevin

  • Alpha.ArsAlpha.Ars Posts: 5Questions: 1Answers: 0
    edited November 2020

    I tried adding the function, and now
    my is looking like that: (still not working)

    <script type="text/javascript" language="javascript" >
    $(document).ready(function(){
    
     var dataTable = $('#sample_data').DataTable({
      "processing" : true,
      "serverSide" : true,
      "order" : [],
      "ajax" : {
       url:"fetch.php",
       type:"POST"
      }
     });
    
     setInterval( function () {
      dataTable.ajax.reload();
    }, 3000 );
    
     $('#sample_data').on('draw.dt', function(){
      $('#sample_data').Tabledit({
       url:'action.php',
       dataType:'json',
       columns:{
        identifier : [0, 'id'],
        editable:[[5, 'auto_start', '{"0":"No","1":"Yes"}']]
       },
       restoreButton:false,
       onSuccess:function(data, textStatus, jqXHR)
       {
        if(data.action == 'delete')
        {
         $('#' + data.id).remove();
         $('#sample_data').DataTable().ajax.reload();
        }
       }
      });
     });
      
    }); 
    </script>
    

    and you can find my fetch linked above

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

    Did you add the setInterval function to try fixing this issue? Unless you want to refresh the table every 3 seconds you should remove it.

    You will probably want to use $('#sample_data').DataTable().ajax.reload( null, false ); to stay on the same page. You have this in line 32 of the above code but it looks like it is only executed when deleting a row.

    The Tabledit() library is not a Datatalbes library and I'm not familiar with how it works. Seems like you will want to execute $('#sample_data').DataTable().ajax.reload( null, false ); in the onSuccess function all the time not just for deletes. Move it outside the if statement or remove the if and just have $('#sample_data').DataTable().ajax.reload( null, false ); . You will need to refer to the Tabledit() docs for more specifics about the onSuccess function and if there is a different callback to use.

    When using ajax.reload() the server code will return the page of data requested based on the page number and sorting. The edited data may or may not be on this page.

    Kevin

  • Alpha.ArsAlpha.Ars Posts: 5Questions: 1Answers: 0

    Hi Kevin

    thanks for trying to help me, I'm still not getting the extpected result.
    Just to be sure, I just want to have the table showing the exact same values as in the sql, so if a row is deleted it will get awat from the table.

    kinda like this

    data.php

    <?php 
    $conn = new mysqli('localhost', 'username', 'password', 'table');
    if ($conn->connect_error) {
        die("Connection error: " . $conn->connect_error);
    }
    $result = $conn->query("SELECT * FROM users");
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo $row['last_name'] . '<br>';
        }
    }
    ?>
    
    

    index.php:

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <title></title>
        
    </head>
    <body>
        <div id="show"></div>
    
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
        <script type="text/javascript">
            $(document).ready(function() {
                setInterval(function () {
                    $('#show').load('data.php')
                }, 1000);
            });
        </script>
    </body>
    </html>
    
This discussion has been closed.