Quickie code contribution: EXPLAIN queries in profiler.

[eluser]_asdf[/eluser]
I'm kind of lazy, or I'd roll this out to a separate file rather than hack the core, but whatever, its a two minute job, and its the kind of thing that ought to be in the profiler core anyway, really.

So yeah, when in development, I always run every controller with profiling on (which is kind of a pain, BTW.) so that I can see whats going on. As an addition, its kind of useful to see whats happening to queries behind the scenes, hence the delightful EXPLAIN SQL syntax. But I don't want to print_r or copypaste every query to run against the database.

So I'll make CI do it for me. In a hacky, monkeypatch'd fashion.

So, should anyone else feel like making upgrading from 1.6.1 harder, join me now as we corrupt Libraries/Profiler.php (nb: I may actually override the class method in a separate file, if I can be bothered)

Code:

Line 132: $val = htmlspecialchars($val, ENT_QUOTES);

// change to:
$explain = $val; // for EXPLAIN bit later. htmlspecialchars breaks queries, and decode isn't an option until PHP5 >
$val = htmlspecialchars($val, ENT_QUOTES);

Code:

Line 141: $output .= "<tr><td width='1%' valign='top' style='color:#990000;font-weight:normal;background-color:#ddd;'>".$time."&nbsp;&nbsp;</td><td style='color:#000;font-weight:normal;background-color:#ddd;'>".$val."</td></tr>\n";

// directly after that, lets paste in some more hacky code!
// explain select statements
if (strpos($explain, 'SELECT') !== false)
{
    $r = $this->CI->db->query('EXPLAIN ' . $explain);
    $r = $r->result_array();
    
    $output .= "<tr><td width='1%' style='font-weight:normal;'>&nbsp;</td><td style='color:#000;font-weight:normal;'>";
    
    foreach ($r as $explain)
    {
        $output .= "<table cellpadding='3' width='100%' style='border-top: 1px solid #ddd;margin-bottom: 10px'>";
        next($explain); // pop id off, because who cares?
        while (list($key, $val) = each($explain))
        {
            if (!empty($val)) // only display non-empty elements
            {
                $output .= "<tr><td style='color:#990000;' width='100'>" . $key . "</td><td>" . $val . "</td></tr>";
            }
        }
        $output .= "</table>\n";
    }
    
    $output .= "</td></tr>\n";
}

and that should be it. Now you can enjoy all the benefits of EXPLAINing your queries (and the hassles of upgrading CI in the future), and finding out why that query is taking so blasted long. Its no 'slow queries' log, but its a start.

[eluser]xwero[/eluser]
You can extend the profile class to keep CI updates hassleless (almost any way )

Code:

class MY_Profiler extends CI_Profiler
{
   function MY_Profiler()
   {
       parent::CI_Profiler();
   }

   function _compile_queries()
    {
        $output  = "\n\n";
        $output .= '<fieldset style="border:1px solid #0000FF;padding:6px 10px 10px 10px;margin:20px 0 20px 0;background-color:#eee">';
        $output .= "\n";
        
        if ( ! class_exists('CI_DB_driver'))
        {
            $output .= '<legend style="color:#0000FF;">&nbsp;&nbsp;'.$this->CI->lang->line('profiler_queries').'&nbsp;&nbsp;</legend>';
            $output .= "\n";        
            $output .= "\n\n<table cellpadding='4' cellspacing='1' border='0' width='100%'>\n";
            $output .="<tr><td width='100%' style='color:#0000FF;font-weight:normal;background-color:#eee;'>".$this->CI->lang->line('profiler_no_db')."</td></tr>\n";
        }
        else
        {
            $output .= '<legend style="color:#0000FF;">&nbsp;&nbsp;'.$this->CI->lang->line('profiler_queries').' ('.count($this->CI->db->queries).')&nbsp;&nbsp;</legend>';
            $output .= "\n";        
            $output .= "\n\n<table cellpadding='4' cellspacing='1' border='0' width='100%'>\n";
            
            if (count($this->CI->db->queries) == 0)
            {
                $output .= "<tr><td width='100%' style='color:#0000FF;font-weight:normal;background-color:#eee;'>".$this->CI->lang->line('profiler_no_queries')."</td></tr>\n";
            }
            else
            {
                $highlight = array('SELECT', 'FROM', 'WHERE', 'AND', 'LEFT JOIN', 'ORDER BY', 'LIMIT', 'INSERT', 'INTO', 'VALUES', 'UPDATE', 'OR');
                
                foreach ($this->CI->db->queries as $key => $val)
                {
                    $explain = $val; // for EXPLAIN bit later. htmlspecialchars breaks queries, and decode isn't an option until PHP5 >
                                        $val = htmlspecialchars($val, ENT_QUOTES);
                    $time = number_format($this->CI->db->query_times[$key], 4);
                    
                    foreach ($highlight as $bold)
                    {
                        $val = str_replace($bold, '<strong>'.$bold.'</strong>', $val);    
                    }
                    
                    $output .= "<tr><td width='1%' valign='top' style='color:#990000;font-weight:normal;background-color:#ddd;'>".$time."&nbsp;&nbsp;</td><td style='color:#000;font-weight:normal;background-color:#ddd;'>".$val."</td></tr>\n";
                                        // explain select statements
if (strpos($explain, 'SELECT') !== false)
{
    $r = $this->CI->db->query('EXPLAIN ' . $explain);
    $r = $r->result_array();
    
    $output .= "<tr><td width='1%' style='font-weight:normal;'>&nbsp;</td><td style='color:#000;font-weight:normal;'>";
    
    foreach ($r as $explain)
    {
        $output .= "<table cellpadding='3' width='100%' style='border-top: 1px solid #ddd;margin-bottom: 10px'>";
        next($explain); // pop id off, because who cares?
        while (list($key, $val) = each($explain))
        {
            if (!empty($val)) // only display non-empty elements
            {
                $output .= "<tr><td style='color:#990000;' width='100'>" . $key . "</td><td>" . $val . "</td></tr>";
            }
        }
        $output .= "</table>\n";
    }
    
    $output .= "</td></tr>\n";
}
                }
            }
        }
        
        $output .= "</table>\n";
        $output .= "</fieldset>";
        
        return $output;
    }
}

I apologize for the bad indentation.

[eluser]_asdf[/eluser]
Well there you go, thats how lazy I am. Hurray for overriding the class. Shame its still kind of a monkeypatch of quoted HTML, but what can you do.

S'worth pointing out too that I've only tested this on MySQL variants; while I know SQLite & Postgre both support EXPLAIN syntax, I'm pretty sure MS and oracle have their own annoying method, in which case, hey, this'll break. I should probably poll it for a known SQL-92 adherent, but like I said, lazy.