Solution: debug/get final SQL query made in ssp.class.php

losttheplot

If you would like to log the complete SQL query passed each time an ajax call is made to ssp.class.php ( https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php ), with parameter values interpolated into it, this might be useful.

I have taken an idea proposed by bigwebguy ( http://stackoverflow.com/questions/210564/getting-raw-sql-query-string-from-pdo-prepared-statements ) and modified it as follows.

Add this method to ssp.class.php:

    /**
     * @param string $query The sql query with parameter placeholders
     * @param array $params The array of substitution parameters
     * @return string The interpolated query
     */
    static function interpolateQuery($query, $params) {
        $keys = array();
        $values = $params;
    
        # build a regular expression for each parameter
        foreach ($params as $key => $value) {
            
            $keys[$key] = '/:binding_'.$key.'/';
            
            if (is_array($value)) {
                $values[$key] = $value['val'];
            } else {
                $values[$key] = 'NULL';
            }
        }
        // Walk the array to see if we can add single-quotes to strings
        array_walk($values, create_function('&$v, $k', 'if (!is_numeric($v) && $v!="NULL") $v = "\'".$v."\'";'));
    
        $query = preg_replace($keys, $values, $query, 1, $count);
    
        return $query;
    }

Usage example (using Tracy, so you might need to change 'Debugger::log' to suit):

    static function simple ( $request, $conn, $table, $primaryKey, $columns )
    {
        $bindings = array();
        $db = self::db( $conn );

        // Build the SQL query string from the request
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings );

        // store query in log
        $query = "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."` FROM `$table` $where $order $limit";
        if (DEBUG) { Debugger::log(self::interpolateQuery($query, $bindings)); }


    [snip]

allan

Nice one - thanks for sharing this with us!

Allan

losttheplot

Thanks for sharing the whole plugin, which I am using for all the many CRUD listings on my latest project :)