Formatting And Syntax Highlighting

Usage: 
    
<?php
$formatted = SqlFormatter::format($sql);
?>
Original Formatted And Highlighted 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo 
    FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 
    AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a',
    29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') 
    AND d.title IS NOT NULL AND t_create >= 
    DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create 
    < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, 
    orc_scheme_detail b WHERE a.id = b.id
 
SELECT 
  DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, 
  b.title memo 
FROM 
  (
    SELECT 
      id 
    FROM 
      orc_scheme_detail d 
    WHERE 
      d.business = 208 
      AND d.type IN (
        29, 30, 31, 321, 33, 34, 3542, 361, 327, 
        38, 39, 40, 41, 42, 431, 4422, 415, 4546, 
        47, 48, 'a', 29, 30, 31, 321, 33, 34, 3542, 
        361, 327, 38, 39, 40, 41, 42, 431, 4422, 
        415, 4546, 47, 48, 'a'
      ) 
      AND d.title IS NOT NULL 
      AND t_create >= DATE_FORMAT(
        (
          DATE_SUB(NOW(), INTERVAL 1 DAY)
        ), 
        '%Y-%c-%d'
      ) 
      AND t_create < DATE_FORMAT(NOW(), '%Y-%c-%d') 
    ORDER BY 
      d.id 
    LIMIT 
      2, 10
  ) a, 
  orc_scheme_detail b 
WHERE 
  a.id = b.id
 
SELECT * from Table1 LEFT 
    OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT 
  * 
from 
  Table1 
  LEFT OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT 
  * 
FROM 
  MyTable 
WHERE 
  id = 46
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
SELECT 
  count(*), 
  `Column1` as count, 
  `Testing`, 
  `Testing Three` 
FROM 
  `Table1` 
WHERE 
  Column1 = 'testing' 
  AND (
    (
      `Column2` = `Column3` 
      OR Column4 >= NOW()
    )
  ) 
GROUP BY 
  Column1 
ORDER BY 
  Column3 DESC 
LIMIT 
  5, 10
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category)
    Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
select 
  * 
from 
  `Table`, 
  (
    SELECT 
      group_concat(column1) as col 
    FROM 
      Table2 
    GROUP BY 
      category
  ) Table2, 
  Table3 
where 
  Table2.col = (Table3.col2 - `Table`.id)
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
insert ignore into Table3 (column1, column2) 
VALUES 
  ('test1', 'test2'), 
  ('test3', 'test4');
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
UPDATE 
  MyTable 
SET 
  name = 'sql', 
  category = 'databases' 
WHERE 
  id > '65'
 
delete from MyTable WHERE name LIKE "test%"
 
delete from 
  MyTable 
WHERE 
  name LIKE "test%"
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
SELECT 
  * 
FROM 
  UnmatchedParens 
WHERE 
  (A = B)
)
AND (
  (
    (Test = 1) 
WARNING: unclosed parentheses or section
 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
 
-- This is a comment
SELECT 
  
  /* This is another comment
      On more than one line */
  Id #This is one final comment
  as temp, 
  DateCreated as Created 
FROM 
  MyTable;

Formatting Only

Usage: 
    
<?php
$formatted = SqlFormatter::format($sql, false);
?>
Original Formatted 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo 
    FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 
    AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a',
    29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') 
    AND d.title IS NOT NULL AND t_create >= 
    DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create 
    < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, 
    orc_scheme_detail b WHERE a.id = b.id
 
SELECT 
  DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, 
  b.title memo 
FROM 
  (
    SELECT 
      id 
    FROM 
      orc_scheme_detail d 
    WHERE 
      d.business = 208 
      AND d.type IN (
        29, 30, 31, 321, 33, 34, 3542, 361, 327, 
        38, 39, 40, 41, 42, 431, 4422, 415, 4546, 
        47, 48, 'a', 29, 30, 31, 321, 33, 34, 3542, 
        361, 327, 38, 39, 40, 41, 42, 431, 4422, 
        415, 4546, 47, 48, 'a'
      ) 
      AND d.title IS NOT NULL 
      AND t_create >= DATE_FORMAT(
        (
          DATE_SUB(NOW(), INTERVAL 1 DAY)
        ), 
        '%Y-%c-%d'
      ) 
      AND t_create < DATE_FORMAT(NOW(), '%Y-%c-%d') 
    ORDER BY 
      d.id 
    LIMIT 
      2, 10
  ) a, 
  orc_scheme_detail b 
WHERE 
  a.id = b.id
 
SELECT * from Table1 LEFT 
    OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT 
  * 
from 
  Table1 
  LEFT OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT 
  * 
FROM 
  MyTable 
WHERE 
  id = 46
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
SELECT 
  count(*), 
  `Column1` as count, 
  `Testing`, 
  `Testing Three` 
FROM 
  `Table1` 
WHERE 
  Column1 = 'testing' 
  AND (
    (
      `Column2` = `Column3` 
      OR Column4 >= NOW()
    )
  ) 
GROUP BY 
  Column1 
ORDER BY 
  Column3 DESC 
LIMIT 
  5, 10
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category)
    Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
select 
  * 
from 
  `Table`, 
  (
    SELECT 
      group_concat(column1) as col 
    FROM 
      Table2 
    GROUP BY 
      category
  ) Table2, 
  Table3 
where 
  Table2.col = (Table3.col2 - `Table`.id)
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
insert ignore into Table3 (column1, column2) 
VALUES 
  ('test1', 'test2'), 
  ('test3', 'test4');
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
UPDATE 
  MyTable 
SET 
  name = 'sql', 
  category = 'databases' 
WHERE 
  id > '65'
 
delete from MyTable WHERE name LIKE "test%"
 
delete from 
  MyTable 
WHERE 
  name LIKE "test%"
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
SELECT 
  * 
FROM 
  UnmatchedParens 
WHERE 
  (A = B)
) 
AND (
  (
    (Test = 1)
 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
 
-- This is a comment
SELECT 
  
  /* This is another comment
      On more than one line */
  Id #This is one final comment
  as temp, 
  DateCreated as Created 
FROM 
  MyTable;

Syntax Highlighting Only

Usage: 
    
<?php
$highlighted = SqlFormatter::highlight($sql);
?>
Original Highlighted 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo 
    FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 
    AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a',
    29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') 
    AND d.title IS NOT NULL AND t_create >= 
    DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create 
    < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, 
    orc_scheme_detail b WHERE a.id = b.id
 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo 
    FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 
    AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a',
    29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') 
    AND d.title IS NOT NULL AND t_create >= 
    DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create 
    < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, 
    orc_scheme_detail b WHERE a.id = b.id
 
SELECT * from Table1 LEFT 
    OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * from Table1 LEFT 
    OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category)
    Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category)
    Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
delete from MyTable WHERE name LIKE "test%"
 
delete from MyTable WHERE name LIKE "test%"
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;

Compress Query

Usage: 
    
<?php
$compressed = SqlFormatter::compress($sql);
?>
Original Compressed 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo 
    FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 
    AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a',
    29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') 
    AND d.title IS NOT NULL AND t_create >= 
    DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create 
    < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, 
    orc_scheme_detail b WHERE a.id = b.id
 
SELECT DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID, b.title memo FROM (SELECT id FROM orc_scheme_detail d WHERE d.business=208 AND d.type IN (29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a', 29,30,31,321,33,34,3542,361,327,38,39,40,41,42,431,4422,415,4546,47,48,'a') AND d.title IS NOT NULL AND t_create >= DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, orc_scheme_detail b WHERE a.id = b.id
 
SELECT * from Table1 LEFT 
    OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * from Table1 LEFT OUTER JOIN Table2 on Table1.id = Table2.id
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT * FROM MyTable WHERE id = 46
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
SELECT count(*),`Column1` as count,`Testing`, `Testing Three` FROM `Table1` WHERE Column1 = 'testing' AND ( (`Column2` = `Column3` OR Column4 >= NOW()) ) GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category)
    Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
select * from `Table`, (SELECT group_concat(column1) as col FROM Table2 GROUP BY category) Table2, Table3 where Table2.col = (Table3.col2 - `Table`.id)
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
insert ignore into Table3 (column1, column2) VALUES ('test1','test2'), ('test3','test4');
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
UPDATE MyTable SET name='sql', category='databases' WHERE id > '65'
 
delete from MyTable WHERE name LIKE "test%"
 
delete from MyTable WHERE name LIKE "test%"
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
SELECT * FROM UnmatchedParens WHERE ( A = B)) AND (((Test=1)
 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
 
SELECT Id as temp, DateCreated as Created FROM MyTable;

Splitting SQL Strings Into Individual Queries

Usage: 
    
<?php
$queries = SqlFormatter::splitQuery($sql);
?>
Original Split 
DROP TABLE IF EXISTS MyTable;
    CREATE TABLE MyTable ( id int );
    INSERT INTO MyTable    (id)
        VALUES
        (1),(2),(3),(4);
    SELECT * FROM MyTable;
 
  1. DROP TABLE IF EXISTS MyTable;
  2. CREATE TABLE MyTable ( id int );
  3. INSERT INTO MyTable    (id)
        VALUES
        (1),(2),(3),(4);
  4. SELECT * FROM MyTable;
 
SELECT ";"; SELECT ";\"; a;";
    SELECT ";
        abc";
    SELECT a,b #comment;
    FROM test;
 
  1. SELECT ";";
  2. SELECT ";\"; a;";
  3. SELECT ";
        abc";
  4. SELECT a,b #comment;
    FROM test;
 
-- Drop the table first if it exists
    DROP TABLE IF EXISTS MyTable;

    -- Create the table
    CREATE TABLE MyTable ( id int );

    -- Insert values
    INSERT INTO MyTable (id)
        VALUES
        (1),(2),(3),(4);

    -- Done
 
  1. -- Drop the table first if it exists
    DROP TABLE IF EXISTS MyTable;
  2. -- Create the table
    CREATE TABLE MyTable ( id int );
  3. -- Insert values
    INSERT INTO MyTable (id)
        VALUES
        (1),(2),(3),(4);

Removing Comments

Usage: 
    
<?php
$nocomments = SqlFormatter::removeComments($sql);
?>
Original Comments Removed 
-- This is a comment
    SELECT
    /* This is another comment
    On more than one line */
    Id #This is one final comment
    as temp, DateCreated as Created FROM MyTable;
 
SELECT 
  Id as temp, 
  DateCreated as Created 
FROM 
  MyTable;