Skip to content

Latest commit

 

History

History
87 lines (63 loc) · 2.28 KB

README.md

File metadata and controls

87 lines (63 loc) · 2.28 KB
Raw

SqlFormatter

A lightweight php class for formatting sql statements. Handles automatic indentation and syntax highlighting.

History

I found myself having to debug auto-generated SQL statements all the time and wanted some way to easily output formatted HTML without having to include a huge library or copy and paste into online formatters.

I was originally planning to extract the formatting code from PhpMyAdmin, but that was 10,000+ lines of code and used global variables.

I saw that other people had the same problem and used Stack Overflow user losif's answer as a starting point. http://stackoverflow.com/a/3924147

Usage

The SqlFormatter class has a static method 'format' which takes a SQL string
as input and returns a formatted HTML block inside a pre tag.

Sample usage:

<?php
require_once('SqlFormatter.php');

echo SqlFormatter::format("SELECT * FROM MyTable LIMIT 10");

Sample output:

Syntax Highlighting Only

There is also a static method 'highlight' that only does syntax highlighting and preserves all original whitespace.

This is useful for sql that is already well formatted and just needs to be a little easier to read.

<?php
echo SqlFormatter::highlight("SELECT * FROM MyTable LIMIT 10");

Split SQL String into Queries

Another feature, which is unrelated to formatting, is the ability to break up a SQL string into multiple queries.

For Example:

DROP TABLE IF EXISTS MyTable;
CREATE TABLE MyTable ( id int );
INSERT INTO MyTable	(id)
	VALUES
	(1),(2),(3),(4);
SELECT * FROM MyTable;
$queries = SqlFormatter::splitQuery($sql);

$queries is now an array of the 4 queries without trailing semicolons.

Why not just use explode(';',$sql) or regular expressions? The following example sql and others like it are impossible to split correctly using regular expressions.

SELECT ";"; SELECT ";\"; a;";
SELECT ";
    abc";
SELECT a,b #comment;
FROM test;

The splitQuery method will still fail in the following cases:

  • The DELIMITER command can be used to change the delimiter from the default ';' to something else.
  • The CREATE PROCEDURE command