ericbae ericbae - 7 months ago 44
PHP Question

Laravel 4 - logging SQL queries

There are already several questions in regards to logging the SQL query in Laravel 4. But I've tried almost all of them and it's still not working the way I want.

Here's my situation


  1. in my php view file, I make AJAX request to the server

  2. The AJAX request is received and runs a RAW parameterized Postgres SQL query (e.g.

    DB::select('select * from my_table where id=?', array(1))



If I use

Event::listen('illuminate.query', function($sql)
{
Log::error($sql);
});


I just get "select * from my_table where id=?" as the log message without the ID value actually populated.

If I use

$queries = DB::getQueryLog();
$last_query = end($queries);
Log::error(print_r($last_query, true));


I still don't have the final SQL query with the ID populated.

Finally, if I use a logging tool like https://github.com/loic-sharma/profiler - it doesn't display anything since I'm making an AJAX request.

Have I exhausted my options? Is there still another better way?

Answer

Here is what I am currently using for logging of sql queries. You should be able to drop this into your main routes file then add 'log' => true into your database config.

if (Config::get('database.log', false))
{           
    Event::listen('illuminate.query', function($query, $bindings, $time, $name)
    {
        $data = compact('bindings', 'time', 'name');

        // Format binding data for sql insertion
        foreach ($bindings as $i => $binding)
        {   
            if ($binding instanceof \DateTime)
            {   
                $bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
            }
            else if (is_string($binding))
            {   
                $bindings[$i] = "'$binding'";
            }   
        }       

        // Insert bindings into query
        $query = str_replace(array('%', '?'), array('%%', '%s'), $query);
        $query = vsprintf($query, $bindings); 

        Log::info($query, $data);
    });
}

Thanks to Jeemusu answer for the bit about inserting the bindings into the prepared statement.

Comments