Datatables Custom Server Side Script

I seem to spend much of my weekends these days playing with DataTables!

I had a need to search filters Contacts using their Categories within a Category table. To put it simply: a Contact can belong to multiple Categories and each of those Categories has a General value and can have a Specific value. They can also be dated.

I display the Categories the Contact belongs to on a table within their detail view but there was a requirement to display all Contacts belonging to a General and/or Specific Category. After playing with the standard server-side script which you can get access to once you've bought the Editor (something which I recommend as this tool is just so brilliant! (Thanks for the retweet Allan!)) I realised I'd have to roll my own and I was filled with some trepidation. After reading the excellent documentation though I was slightly less concerned.

I pulled my sleeves up and wrote this:

<?php
    /**
     * Created by PhpStorm.
     * User: Dominic
     * Date: 28/01/2015
     * Time: 16:39
     * UPDATED Date: 29/03/2015
     * UPDATED Time: 09:56
     */
    require_once $_SERVER["DOCUMENT_ROOT"].'vendor/autoload.php';
    require_once $_SERVER["DOCUMENT_ROOT"].'php/variables.php';
    function IsNullOrEmptyString($question){
        return (!isset($question) || trim($question)==='');
    }
    $query = array();
    $and = array();
    foreach($_REQUEST["columns"] as $column){
        if(!IsNullOrEmptyString($column["search"]["value"])){
            $and[] = $column["search"]["value"];
        }
    }
    $response["recordsTotal"] = $db->count("contact_categories");
    if(count($and) !== 0){
        if(count($and) === 1 || $and[1] === "all"){
            $query["contact_categories.general"] = $and[0];
        }else{
            $query["AND"] = array(
                "contact_categories.general" => $and[0],
                "contact_categories.specific" => ($and[1] === "blank") ? "" : $and[1],
            );
        }
    }
    $response["recordsFiltered"] = $db->count(
        "contact_categories",
        array(
            "[>]contacts" => "contact_id"
        ),
        "*",
        $query
    );
    if(intval($_REQUEST["length"]) !== -1){
        $query["LIMIT"] = array($_REQUEST["start"], $_REQUEST["length"]);
    }
    $query["ORDER"] = array();
    foreach($_REQUEST["order"] as $order){
        $query["ORDER"][] = $_REQUEST["columns"][$order["column"]]["data"]." ".$order["dir"];
    }
    $response["data"] = $db->select(
        "contact_categories",
        array(
            "[>]contacts" => "contact_id"
        ),
        "*",
        $query
    );
    echo json_encode($response);

You'll probably be able to see that I've used Medoo again and within my client-side script I have two drop down selects with each General and Specific Category. With the Specific Category changing depending upon the General Category selected, and with all and blank added to allow for all records as well as for those without a Specific Category to be shown.

I also only needed to search using the General or Specific Category, nothing else. I only needed to order by the other remaining columns (the Date of the Category or the Full Name of the Contact), so it is somewhat crippled in functionality. I also have to do a couple of extra queries to get the correct counts for total records and filtered records... but it's not too shabby for all that and works a treat! I thought it'd take me ages to write this but it took less time than it's taken to write this all in all.