Use PHP to format an input SQL query as HTML?

Question

What I am looking for is a php function that takes an unformatted query like this:

$sql = "select name, size from things where color = 'green' order by price asc";

so that it would appear in an HTML page something like this:

SELECT
    name, size
FROM
    things
WHERE
    color = 'green'
ORDER BY
    price ASC';

There's some code inside phpMyAdmin that does this already, I could look in there I guess!

Scleritis

You could... What is the question? – Izmir

Answer 1

I had the same problem and made a light-weight PHP class to do formatting/syntax highlighting.

https://github.com/jdorn/sql-formatter

I haven't fully tested it with complex queries (sub-selects, unions, etc.), but it seems to work pretty well for common cases.

To get fully accurate results, you really need a full SQL parser like phpMyAdmin uses, but that uses 10,000+ lines of code spread out over many files and is probably overkill for simple debugging.

Filament

beautiful! using in my sql builder to display the results. works perfectly, just plug in and it works – Soileau

Great! Greetings from Colombia. – Millepede

Great ! I wish I could give more than one up-votes. I wonder this answer is not accepted though. – Dansby

lower response from Ashish Makhija has the same functionality in single file just without a color – Murder

Answer 2

function getFormattedSQL($sql_raw)
{
 if( empty($sql_raw) || !is_string($sql_raw) )
 {
  return false;
 }

 $sql_reserved_all = array (
     'ACCESSIBLE', 'ACTION', 'ADD', 'AFTER', 'AGAINST', 'AGGREGATE', 'ALGORITHM', 'ALL', 'ALTER', 'ANALYSE', 'ANALYZE', 'AND', 'AS', 'ASC',
     'AUTOCOMMIT', 'AUTO_INCREMENT', 'AVG_ROW_LENGTH', 'BACKUP', 'BEGIN', 'BETWEEN', 'BINLOG', 'BOTH', 'BY', 'CASCADE', 'CASE', 'CHANGE', 'CHANGED',
     'CHARSET', 'CHECK', 'CHECKSUM', 'COLLATE', 'COLLATION', 'COLUMN', 'COLUMNS', 'COMMENT', 'COMMIT', 'COMMITTED', 'COMPRESSED', 'CONCURRENT', 
     'CONSTRAINT', 'CONTAINS', 'CONVERT', 'CREATE', 'CROSS', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DAY', 'DAY_HOUR', 'DAY_MINUTE', 
     'DAY_SECOND', 'DEFINER', 'DELAYED', 'DELAY_KEY_WRITE', 'DELETE', 'DESC', 'DESCRIBE', 'DETERMINISTIC', 'DISTINCT', 'DISTINCTROW', 'DIV',
     'DO', 'DROP', 'DUMPFILE', 'DUPLICATE', 'DYNAMIC', 'ELSE', 'ENCLOSED', 'END', 'ENGINE', 'ENGINES', 'ESCAPE', 'ESCAPED', 'EVENTS', 'EXECUTE',
     'EXISTS', 'EXPLAIN', 'EXTENDED', 'FAST', 'FIELDS', 'FILE', 'FIRST', 'FIXED', 'FLUSH', 'FOR', 'FORCE', 'FOREIGN', 'FROM', 'FULL', 'FULLTEXT',
     'FUNCTION', 'GEMINI', 'GEMINI_SPIN_RETRIES', 'GLOBAL', 'GRANT', 'GRANTS', 'GROUP', 'HAVING', 'HEAP', 'HIGH_PRIORITY', 'HOSTS', 'HOUR', 'HOUR_MINUTE',
     'HOUR_SECOND', 'IDENTIFIED', 'IF', 'IGNORE', 'IN', 'INDEX', 'INDEXES', 'INFILE', 'INNER', 'INSERT', 'INSERT_ID', 'INSERT_METHOD', 'INTERVAL',
     'INTO', 'INVOKER', 'IS', 'ISOLATION', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LAST_INSERT_ID', 'LEADING', 'LEFT', 'LEVEL', 'LIKE', 'LIMIT', 'LINEAR',               
     'LINES', 'LOAD', 'LOCAL', 'LOCK', 'LOCKS', 'LOGS', 'LOW_PRIORITY', 'MARIA', 'MASTER', 'MASTER_CONNECT_RETRY', 'MASTER_HOST', 'MASTER_LOG_FILE',
     'MASTER_LOG_POS', 'MASTER_PASSWORD', 'MASTER_PORT', 'MASTER_USER', 'MATCH', 'MAX_CONNECTIONS_PER_HOUR', 'MAX_QUERIES_PER_HOUR',
     'MAX_ROWS', 'MAX_UPDATES_PER_HOUR', 'MAX_USER_CONNECTIONS', 'MEDIUM', 'MERGE', 'MINUTE', 'MINUTE_SECOND', 'MIN_ROWS', 'MODE', 'MODIFY',
     'MONTH', 'MRG_MYISAM', 'MYISAM', 'NAMES', 'NATURAL', 'NOT', 'NULL', 'OFFSET', 'ON', 'OPEN', 'OPTIMIZE', 'OPTION', 'OPTIONALLY', 'OR',
     'ORDER', 'OUTER', 'OUTFILE', 'PACK_KEYS', 'PAGE', 'PARTIAL', 'PARTITION', 'PARTITIONS', 'PASSWORD', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE',
     'PROCESS', 'PROCESSLIST', 'PURGE', 'QUICK', 'RAID0', 'RAID_CHUNKS', 'RAID_CHUNKSIZE', 'RAID_TYPE', 'RANGE', 'READ', 'READ_ONLY',            
     'READ_WRITE', 'REFERENCES', 'REGEXP', 'RELOAD', 'RENAME', 'REPAIR', 'REPEATABLE', 'REPLACE', 'REPLICATION', 'RESET', 'RESTORE', 'RESTRICT',
     'RETURN', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'ROLLBACK', 'ROW', 'ROWS', 'ROW_FORMAT', 'SECOND', 'SECURITY', 'SELECT', 'SEPARATOR',
     'SERIALIZABLE', 'SESSION', 'SET', 'SHARE', 'SHOW', 'SHUTDOWN', 'SLAVE', 'SONAME', 'SOUNDS', 'SQL', 'SQL_AUTO_IS_NULL', 'SQL_BIG_RESULT',
     'SQL_BIG_SELECTS', 'SQL_BIG_TABLES', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_CALC_FOUND_ROWS', 'SQL_LOG_BIN', 'SQL_LOG_OFF',
     'SQL_LOG_UPDATE', 'SQL_LOW_PRIORITY_UPDATES', 'SQL_MAX_JOIN_SIZE', 'SQL_NO_CACHE', 'SQL_QUOTE_SHOW_CREATE', 'SQL_SAFE_UPDATES',
     'SQL_SELECT_LIMIT', 'SQL_SLAVE_SKIP_COUNTER', 'SQL_SMALL_RESULT', 'SQL_WARNINGS', 'START', 'STARTING', 'STATUS', 'STOP', 'STORAGE',
     'STRAIGHT_JOIN', 'STRING', 'STRIPED', 'SUPER', 'TABLE', 'TABLES', 'TEMPORARY', 'TERMINATED', 'THEN', 'TO', 'TRAILING', 'TRANSACTIONAL',    
     'TRUNCATE', 'TYPE', 'TYPES', 'UNCOMMITTED', 'UNION', 'UNIQUE', 'UNLOCK', 'UPDATE', 'USAGE', 'USE', 'USING', 'VALUES', 'VARIABLES',
     'VIEW', 'WHEN', 'WHERE', 'WITH', 'WORK', 'WRITE', 'XOR', 'YEAR_MONTH'
 );

 $sql_skip_reserved_words = array('AS', 'ON', 'USING');
 $sql_special_reserved_words = array('(', ')');

 $sql_raw = str_replace("\n", " ", $sql_raw);

 $sql_formatted = "";

 $prev_word = "";
 $word = "";

 for( $i=0, $j = strlen($sql_raw); $i < $j; $i++ )
 {
  $word .= $sql_raw[$i];

  $word_trimmed = trim($word);

  if($sql_raw[$i] == " " || in_array($sql_raw[$i], $sql_special_reserved_words))
  {
   $word_trimmed = trim($word);

   $trimmed_special = false;

   if( in_array($sql_raw[$i], $sql_special_reserved_words) )
   {
    $word_trimmed = substr($word_trimmed, 0, -1);
    $trimmed_special = true;
   }

   $word_trimmed = strtoupper($word_trimmed);

   if( in_array($word_trimmed, $sql_reserved_all) && !in_array($word_trimmed, $sql_skip_reserved_words) )
   {
    if(in_array($prev_word, $sql_reserved_all))
    {
     $sql_formatted .= '<b>'.strtoupper(trim($word)).'</b>'.'&nbsp;';
    }
    else
    {
     $sql_formatted .= '<br/>&nbsp;';
     $sql_formatted .= '<b>'.strtoupper(trim($word)).'</b>'.'&nbsp;';
    }

    $prev_word = $word_trimmed;
    $word = "";
   }
   else
   {
    $sql_formatted .= trim($word).'&nbsp;';

    $prev_word = $word_trimmed;
    $word = "";
   }
  }
 }

 $sql_formatted .= trim($word);

 return $sql_formatted;
}

Railey

Thank you, but how is this a good formatter, each reserved word on a separate line it is in my opinion not the best formatting eg ` SELECT x FROM x JOIN ON WHERE A UNION SELECT x FROM x JOIN ON WHERE A ` – Murder

Answer 3

Using phpMyAdmin is quite straight forward:

require 'libraries/common.inc.php';

$sql= "select * from test";
$parsed_sql = PMA_SQP_parse($sql);    
echo PMA_SQP_formatHtml($parsed_sql);

Add a stylesheet to enable syntax highlighting. If you read a bit in the sqlparser documentation you'll find some other function for different formatting types.

Only thing is that phpMyAdmin is somewhat large to only use SQL parsing, so you might want to strip all other functionalities...

Hookup

I tried this, but found it far from straight forward, and gave up in the end. – Scleritis

I tried the example myself (by creating a test.php) in the phpmyadmin directory and it worked for me. – Hookup

OK, I now got the script to work by saving it in /usr/share/phpmyadmin/ and opening it in the browser from /phpmyadmin/. As you say, I'll have to dig around in the documentation to get the nice formatting. Its not quite as straight forward as I hoped, but thanks very much for the pointers. – Scleritis

Answer 4

function sql_format($query) {
  $keywords = array("select", "from", "where", "order by", "group by", "insert into", "update","SET", ",");
  foreach ($keywords as $keyword) {
      if (preg_match("/($keyword *)/i", ",", $matches)) {
        $query = str_replace($matches[1],strtoupper($matches[1]) . "<br/>&nbsp;&nbsp;  ", $query);  
      }
    else if(preg_match("/($keyword *)/i", $query, $matches)) {
      $query = str_replace($matches[1],"<br>".strtoupper($matches[1]) . "<br/>&nbsp;  ", $query);
    }
  }
  return $query;
}

Nedi

thank you, beautiful formatter, I didnt like each keyword on separate line only - as Iosif, almost like Jeremy Dorn upper library – Murder

Answer 5

I don't think there's any freely available code that does this within PECL or similar - which is a pity, as it would be quite a neat little utility. (Albeit only with fairly limited uses.)

As such, you're answered you own question - phpMyAdmin is probably a good first port of call.

Yowl

Answer 6

Something like this will work - add any other SQL keywords you want to parse:

function sql_format($query) {
  $keywords = array("select", "from", "where", "order by", "group by", "insert into", "update");
  foreach ($keywords as $keyword) {
    if (preg_match("/($keyword *)/i", $query, $matches)) {
      $query = str_replace($matches[1], "\n" . strtoupper($matches[1]) . "\n  ", $query);
    }
  }
  return $query;
}

Slipper

This is subject to some possible bugs. – Izmir

Sure it is... it's just a starting suggestion :) – Slipper

Oh, and wrap the return string in "<pre>...</pre>" to display with indentation in HTML. – Slipper

Answer 7

May be This is what you want: http://www.orczhou.com/sqlparser/ PHP SQL Format

Speedwell