SQL pretty print

sql_pretty_print.php

<?php

/*
 * Lightweight concept for pretty printing SQL
 * @author T. Almroth <[email protected]>
 * https://www.tim-international.net/
 */

  function sql_pretty_print($query, $indentation="\t") {

    $depth = 0;

    $newline_commands = [
      'SELECT', 'UPDATE TABLE', 'INSERT INTO',
      'FROM', 'LEFT OUTER JOIN', 'RIGHT OUTER JOIN', 'LEFT JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'INNER JOIN', 'JOIN',
      'GROUP BY', 'ORDER BY', 'LIMIT', 'WHERE', 'AND', 'OR', 'XOR', 'VALUES'
    ];

    $inline_commands = [
      'IN', 'AS', 'ASC', 'DESC'
    ];

   // Step through each character in the query
    for ($i = 0; $i < strlen($query); $i++) {

    // Skip over a value clause
      if (preg_match("#[`']s#", $query[$i]) && $query[$i - 1] != "\\") {
        $value_wrapper = $query[$i];
        for ($n = $i + 1; $n < strlen($query); $n++) {
          if ($query[$n] == $value_wrapper && $query[$n - 1] != "\\") break;
        }
        $i = $n;
      }

    // Reformat newline commands
      foreach ($newline_commands as $find) {
        $find_length = strlen($find);

      // Match $find and lookaround 1 chars to be certain
        if (preg_match("#^". preg_quote($find, '#') ."$#i", substr($query, $i, $find_length)) && preg_match("#(^|\s)". preg_quote($find, '#') ."(\s|$)#i", substr($query, ($i == 0) ? $i : $i - 1, ($i == 0) ? $find_length + 1 : $find_length + 2))) {

        // Consume preceeding whitespace characters
          while ($i > 0 && preg_match('#\s#', $query[$i - 1])) {
            $i--;
          }

        // Consume trailing whitespace characters
          $n = $i + $find_length;
          while ($n < strlen($query) && isset($query[$n + 1]) && preg_match('#\s#', $query[$n + 1])) {
            $n++;
          }

        // Process paranthesized subquery
          if ($find == 'SELECT' && $query[$i-1] == '(') {

          // Find ending paranthesis
            $e = $i + $find_length;
            $paranthesis_depth = 1;
            while ($e < strlen($query) && isset($query[$e + 1]) && ($query[$e+1] != ')' || $query[$e] == '\\' || $paranthesis_depth != 1)) {
              $e++;
            }

          // Format subquery
            $formatted_subquery = sql_pretty_print(substr($query, $i, $e-$i+1), $indentation);

          // Commit replacement
            $replacement = "\n". preg_replace('#^([\t| ]*)#m', $indentation.'$1', $formatted_subquery) ."\n";
            $query = substr($query, 0, $i) . $replacement . substr($query, $e + 1);

          // Set internal cursor
            $i += strlen($replacement) -1;
            continue 2;
          }

        // Commit replacement
          $replacement = "\n" . str_repeat($indentation, $depth) . $find . " ";

          $query = substr($query, 0, $i) . $replacement . substr($query, $n + 1);

        // Set internal cursor
          $i += strlen($replacement) -1;
          continue 2;
        }
      }

    // Reformat inline commands
      foreach ($inline_commands as $find) {
        $find_length = strlen($find);

      // Match $find and lookaround 1 chars to be certain
        if (preg_match("#^$find$#i", substr($query, $i, $find_length)) && preg_match("#(^|\s)$find(\s|\(|$)#i", substr($query, ($i == 0) ? $i : $i - 1, ($i == 0) ? $find_length + 1 : $find_length + 2))) {

        // Consume preceeding whitespace characters
          while ($i > 0 && preg_match('#\s#', $query[$i - 1])) {
            $i--;
          }

        // Consume trailing whitespace characters
          $n = $i + $find_length;
          while ($n < strlen($query) && isset($query[$n + 1]) && preg_match('#\s#', $query[$n + 1])) {
            $n++;
          }

        // Commit replacement
          $replacement = " " . $find . " ";

          $query = substr($query, 0, $i) . $replacement . substr($query, $n + 1);

        // Set internal cursor
          $i += strlen($replacement) -1;
          continue 2;
        }
      }
    }

    return trim($query);
  }

  $query = "select t1.column1, t1.column2, t1.column3, t2.column4, t2.column5, t2.column6, t1.date_created from Table1 t1 left join ( select column4, column5, column6 from Table2 group by column4 order by date_created desc ) t2 on (t2.row_id = t1.id) where t1.column1 = 'this' and t1.column2 like '%query%' order by t1.date_created desc;";

  echo sql_pretty_print($query, '  ');