DataTables Server-Side processing, generate a link in a column with two variables

Question

I'm working in a web with a big database and I need to use the server-side processing of datatables. The script generates a table with the users's name, surname, job and in the last column there is a drop-down boostrap's button with diferents options to aplicate to the user. (delete, edit, view profile...) The problem is that I don't know how to generate that button link, because some of these options have a link with two variables like, for example, delete.php?id=$id&user=$user

HTML

<table id="tabla_valoraciones" class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>User ID</th>
                        <th>User</th>
                        <th>Surname</th>
                        <th>Job</th>
                        <th>Company</th>
                        <th>Company Phone</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>    
                </tbody>
            </table>

JavaScript

$('#tabla_valoraciones').dataTable({
        "order": [[ 0, "desc" ]],
        "columnDefs": [
         {
                "render": function ( data, type, row ) {
                    return row[3]+', '+data;
                },
                "targets": 2
            },
            {
                "render": function ( data, type, row ) {
                    return data+' en '+row[5].toUpperCase();
                },
                "targets": 4
            },
            {
                "targets": [ 0 ],
                "visible": false,
                "searchable": false
            },
            {
                "targets": [ 1 ],
                "visible": false,
                "searchable": false
            },
            {
                "targets": [ 3 ],
                "visible": false,
                "searchable": false
            },
            {
                "targets": [ 5 ],
                "visible": false,
                "searchable": false
            }
        ],
        processing: true,
        serverSide: true,
        ajax: {
            url: 'php/procesado_valoraciones.php',
            dataType: 'json'
        }   
    });

PHP (pricesado_valoraciones.php)

session_start();

// DB table to use
$table = 'valoraciones';

//where conditions
$where="estado=2";


// 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' => 'id_user', 'dt' => 1 ),
    array( 'db' => 'name', 'dt' => 2 ),
    array( 'db' => 'surname', 'dt' => 3 ),
    array( 'db' => 'job_name',  'dt' => 4 ),
    array( 'db' => 'company_name',  'dt' => 5 ),
    array( 'db' => 'company_phone',   'dt' => 6 ),
    array( 
            'db' => 'id', 
            'dt' => 7,
            'formatter' => function( $d, $row ) {
                $buttons='<div class="btn-group">
                                                        <button class="btn btn-default" type="button">Acciones</button>
                                                        <button class="btn btn-default dropdown-toggle" data-toggle="dropdown" type="button">
                                                        <span class="caret"></span>
                                                        <span class="sr-only">Toggle Dropdown</span>
                                                        </button>
                                                        <ul class="dropdown-menu" role="menu">
                                                            <li>
                                                                <a href="assess-experience.php?id='.$d.'&c="><i class="fa fa-check-circle fa-lg" title="Assess" alt="assess"></i> Assess</a>
                                                            </li>';

                                                           if($_SESSION["privilege"]==1):
                                                            $buttons.='<li>
                                                                <a href="delete-experience.php?id='.$d.'&c=" onclick="return confirmDelete;">
                                                <i class="fa fa-trash" alt="Delete" title="Delete Experience" ></i> Borrar</a>
                                                            </li>';
                                                            endif;
                                                        $buttons.='</ul>
                                                    </div>';
                return $buttons;
            }
       )

);

// SQL server connection information


$sql_details = array(
    'user' => 'root',
    'pass' => '****',
    'db'   => '****',
    'host' => 'local_host'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

/*echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);*/

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $where )
);

Everything works fine except that I don't know how tu put the user id in the last column of the table, in the url:

<a href="assess-experience.php?id='.$d.'&c='**$user_id**'"><i class="fa fa-check-circle fa-lg" title="Assess" alt="assess"></i> Assess</a>

If somebody helps me I will really apreciate it!

Thanks in advance and sorry if my english is not correct!

Answer 1

I SOLVED IT!

The solution is use the $row variable.

PHP(Procesado_valoraciones.php)

$columns = array(
    array( 'db' => 'id', 'dt' => 0 ),
    array( 'db' => 'id_user', 'dt' => 1 ),
    array( 'db' => 'name', 'dt' => 2 ),
    array( 'db' => 'surname', 'dt' => 3 ),
    array( 'db' => 'job_name',  'dt' => 4 ),
    array( 'db' => 'company_name',  'dt' => 5 ),
    array( 'db' => 'company_phone',   'dt' => 6 ),
    array( 
            'db' => 'id', 
            'dt' => 7,
            'formatter' => function( $d, $row ) {
                $buttons='<div class="btn-group">
                                                        <button class="btn btn-default" type="button">Acciones</button>
                                                        <button class="btn btn-default dropdown-toggle" data-toggle="dropdown" type="button">
                                                        <span class="caret"></span>
                                                        <span class="sr-only">Toggle Dropdown</span>
                                                        </button>
                                                        <ul class="dropdown-menu" role="menu">
                                                            <li>
                                                                <a href="assess-experience.php?id='.$row[0].'&c='.$row[1].'"><i class="fa fa-check-circle fa-lg" title="Assess" alt="assess"></i> Assess</a>
                                                            </li>';

                                                           if($_SESSION["privilege"]==1):
                                                            $buttons.='<li>
                                                                <a href="delete-experience.php?id='.$row[0].'&c='.$row[1].'" onclick="return confirmDelete;">
                                                <i class="fa fa-trash" alt="Delete" title="Delete Experience" ></i> Delete</a>
                                                            </li>';
                                                            endif;
                                                        $buttons.='</ul>
                                                    </div>';
                return $buttons;
            }
       )

);

Answer 2

You probably want to fetch "user_id" from the "$row".. something like this:

<a href="assess-experience.php?id='.$d.'&c='.$row->user_id.'"><i class="fa fa-check-circle fa-lg" title="Assess" alt="assess"></i> Assess</a> 

or

<a href="assess-experience.php?id='.$d.'&c='.$row["user_id"].'"><i class="fa fa-check-circle fa-lg" title="Assess" alt="assess"></i> Assess</a> 

Answer 3

Personally, I wouldn't build the html in the serverside code. I would just pass the raw data and do the formatting within mRender. Somthing like this:

"render": function ( data, type, row ) {
        return '<a href=\'assess-experience.php?id=\' + row[0] + '&c=' + row[7] +'\'><i class=\'fa fa-check-circle fa-lg\' title=\'Assess\' alt=\'assess\'></i> Assess</a>';
 },