DataTables + PHP/AJAX - manipulate result before display

Question

I don't usually use dataTables via AJAX call but rather run SQL query and then pop the result into a table. For the sake of learning, I would like give dataTable + AJAX a try, but am a little confused as to how I best manipulate my result before displaying it in my table.

Following scenario:

I have a table with a list of users and display the users' avatar in the first column. The avatar image is saved in assets/img/avatars/ and naming convention follows the users id number from the database (eg. avatar_<user_id>.jpg).

Before displaying the image though, I want to check if the image actually exists, and if not, use the default placeholder image (eg. avatar_00.jpg) instead.

Normally I would run my MySQL query and add a php if check to decide what images goes into the column. How do I do this with AJAX? Should I do my data manipulation directly in the javascript or in the fetch_data.php before the result array comes back?

My working sample code below:

EDIT: long story short: how do I check whether an avatar image exists or not in AJAX and ensure that the placeholder is shown in DataTables where no file exists?

HTML Table:

<table id="global_address_book" name="global_address_book" class="table table-hover table-primary align-middle">
    <thead class="primary">
        <tr class="primary">
            <th scope="col">Avatar</th>
            <th scope="col">Name</th>
            <th scope="col">Department</th>
            <th scope="col">Region</th>
            <th scope="col">Action</th>
        </tr>
    </thead>
</table>

JavaScript:

<script type="text/javascript">
    $(document).ready(function() {
        $('#global_address_book').dataTable({
            "processing": true,
            "ajax": "fetch_data.php",
            "columns": [{
                    data: 'id',
                    render: function(data, type, row) {
                        return '<div class="sidebar-comment-avatar"><a href="#"><img src="../../assets/img/misc/small/avatar/avatar-' + row.id + '.jpg"></a></div>';
                    }
                },
                {
                    data: 'first',
                    render: function(data, type, row) {
                        return '<h5>' + row.first + ' ' + row.last + ' - ' + row.position + '<br><small><u>' + row.property + ' (' + row.property_code + ')</u> - ' + row.city + ', ' + row.country + '<br>Email: <a href="#">' + row.email + '</a> - Phone: ' + row.phone + '</small></h5>';

                    }
                },
                {
                    data: 'department',
                    render: function(data, type, row) {
                        return '<a href="#" class="btn btn-info btn-xs">' + row.department + '</a>';
                    }
                },
                {
                    data: 'region',
                    render: function(data, type, row) {
                        return '<a href="#" class="btn btn-default btn-xs">' + row.region + '</a>';
                    }
                },
                {
                    data: 'region',
                    render: function(data, type, row) {
                        return '<a href="#" style="font-size: 14px;">more details</a>';
                    }
                }
            ]
        });
    });
</script>

fetch_data.php

<?php
// db settings
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'db_name';

// db connection
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));

// fetch records
$sql = "select id, first, last, phone, email, position, department, region, property_code, property, city, country from users2";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_assoc($result)) {
    $array[] = $row;
}

$dataset = array(
    "echo" => 1,
    "totalrecords" => count($array),
    "totaldisplayrecords" => count($array),
    "data" => $array
);

echo json_encode($dataset);

Answer 1

You don't need to manipulate data directly in JavaScript. You can return plain HTML in your PHP file, because DataTables will paste any content in the <td> element.

It's best to check if avatar exists before you send the response from your PHP file. I'm assuming you have a DB with all the users. So check against that, and if no avatar file exists, place your placeholder HTML/Image instead