Mask cell data using MySQL?

jmyrtle

I currently have a datatable set up in my site, but it is grabbing the member's ID rather than the member's name in the Member Name column.

Is it possible to replace the cell value with the member's name using an SQL statement? If so, how do I do it?

colin

Your query that collects the JSON data would just need to request that column and return that to the client.

Colin

jmyrtle

Well, there's a problem with that... The member's name is in a different table. This is showing the member's ID which is also in the member's table, it just goes by a different column.

If I can remove this column data and replace it with the member's actual name (or even if I need to use an SQL statement), then that would be great.

jmyrtle

I need to add this SQL statement to my datatable:

select name from members, attendance where members.id = attendance.member_id

colin

You'll need a join for that - the best bet is to search the web, there's a few threads on this forum and elsewhere - see here.

Colin

jmyrtle

But what file do I have to edit in order to make this change? Would it be my ajax data file or would it be my javascript file that calls the table?

Here is my current ajax file:

<?php

// DB table to use
$table = 'attendance';
 
// Table's primary key
$primaryKey = 'id';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'id', 'dt' => 0 ),
    array( 'db' => 'member_id',  'dt' => 1 ),
    array( 'db' => 'member_email', 'dt' => 2 ),
    array( 'db' => 'member_phone', 'dt' => 3 ),
    array( 'db' => 'present', 'dt' => 4),
    array( 'db' => 'alternate', 'dt' => 5 ),
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'ccrp_db',
    'host' => 'localhost'
);
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

?>

And here is my javascript file:

$(document).ready(function() {
    $('#attnTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "order": [],
        "pageLength": 25,
        "ajax": "api/attn_server.php",  
    })
});

allan

Ah - we didn't know you were using either server-side processing or the demo SSP class before. There is currently no option to do a join in the SSP class I'm afraid.

There are a couple of threads about how it might be modified to add that ability, or you could use the Editor PHP libraries to do the server-side processing for you which does support left joins. Or, my preference in many cases, is to create a VIEW in your database which does the SELECT ... FROM ... JOIN ... and then you just read from the VIEW like you would do with any table.

Allan

jmyrtle

If I created a view in my database, how do I tell DataTables to read from the view rather than directly from the table?

colin

You would just reference the view instead of the table, the SQL would be the same.

Colin

jmyrtle

And by doing that, which file do you edit?

colin

where you've got

$table = 'attendance';

you would put the name of the view

Colin