Retrieving data from DB without a table

David@Quantum

Hello

I'm developing a system of which DataTables is essentially the core functionality. In order to avoid convolution the programming side of it, I'm planning to use the DataTables PHP lib to retrieve data from the DB. Is there a way I can leverage the existing AJAX system to pull data into a function? Or will I need to write my own utility to do that?

Thanks in advance,
David

allan

I don't quite understand what you mean by pulling the "data into a function"? If you've got a DataTable with data in it on the client-side and you want to access that data, you can use rows().data().

Allan

David@Quantum

Morning @allen ,

I wanted to use the DataTables PHP interface to get information from the database, but without using a table in the process. This was the solution that I whipped up:

$(document).ready(function() {
    
    //AJAX data request
    var xmlhttp = new XMLHttpRequest();
    xmlhttp.onreadystatechange = function() {
      if (this.readyState == 4 && this.status == 200) {
        console.log(this.responseText);
        data = JSON.parse(this.responseText);
        format_data(data);
      }
    };
    xmlhttp.open("GET", "DataTablesSource/php/table.home.personal.php", true);
    xmlhttp.send();
} );

function format_data(data){
    
    //html strings
    var username = data.data[0].users.user_username;
    var user_name = data.data[0].users.user_name;
    var user_email = data.data[0].users.user_email_address;
    var type = data.data[0].user_types.user_type_label;
    
    //output to page
    var out =
      "<table>"
        + "<tr><td><b>Username:</b> </td>"
        + "<td>" + username + "</td></tr>"
        + "<tr><td><b>Name:</b> </td>"
        + "<td>" + user_name + "</td></tr>"
        + "<tr><td><b>Email:</b> </td>"
        + "<td>" + user_email + "</td></tr>"
        + "<tr><td><b>Type:</b> </td>"
        + "<td>" + type + "</td></tr>"
        + "<tr><td><b>Assigned Sites:</b> </td>"
        + "<td>" + "" + "</td></tr>"
        //sites here leave first td blank and put name in second td
    + "</table>";
    $("#personal").html(out);
}

I was just wondering if there was anyway to use the DataTables AJAX rather than mine, since mine has no error callback.

Cheers,
David

allan

Yes, I believe you should be able to use DataTables for that. Could you show me a sample of what DataTablesSource/php/table.home.personal.php returns first though before I give you misinformation!

Allan

David@Quantum

Hey @allan ,

It reads:

<?php

session_start();

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'users', 'user_id' )
    ->fields(
        Field::inst( 'users.user_id' ),
        Field::inst( 'users.user_name' ),
        Field::inst( 'users.user_username' ),
        Field::inst( 'users.user_email_address' ),
        Field::inst( 'user_types.user_type_label' ),
    )
    ->where('users.user_id', $_SESSION['id'])
    ->leftJoin( 'user_types', 'user_types.user_type_value', '=', 'users.user_permission_level' )
    ->process( $_POST )
    ->json();

and when directing straight to it in the browser returns:

{"data":
  [
    {"DT_RowId":"row_1",
    "users":
        {"user_id":"1",
        "user_name":"Dan Cox",
        "user_username":"Dan QCL",
        "user_email_address":"d.cox@domain-Name.ext"},
      "user_types":{"user_type_label":"Admin"}
    }
  ],
  "options":[],
  "files":[]}

Many thanks,
David

allan

Ah! I see - sorry. I hadn't realise that you were actually using Editor's server-side libraries. I thought you weren't sure on how to use DataTables' ajax option to load object data, but that's not the issue here - you want to use the Editor server-side libraries to load information for the details row~

Since we've got jQuery on the page, rather than having your own XHR you could simplify your code with:

$.ajax({
  url: 'DataTablesSource/php/table.home.personal.php',
  success: function (json) {
    format_data(json);
  }
});

Allan

David@Quantum

Ah brilliant, thanks for the pointer!
The only change I made was JSON.parse(json) in the format_data(json) call:

$.ajax({
  url: 'DataTablesSource/php/table.home.personal.php',
  success: function (json) {
    format_data(JSON.parse(json));
  }
});

Many thanks,
David