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!

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.