Format and highlight SQL statements in PHP

jdorn/sql-formatter is a lightweight PHP class used to format SQL statements.

It supports automatic indentation, adding newlines, and even supports syntax highlighting.

Used in the command line

This extension package contains a bin/sql-formatter executable file, which can be used directly for command line formatting SQL.

You can use this command after installing Composer globally:

composer global require jdorn/sql-formatter
sql-formatter "SELECT SOME QUERY;" // 直接格式化
// 或
echo "SELECT SOME QUERY;" | sql-formatter // 使用管道，更适合较大量的 SQL 语句

Use it as an extension package

The SqlFormatter class contains a static file named format. Method, which can receive a SQL statement string as a parameter and return formatted HTML code wrapped in pre tags.

For example:

<?php
require_once(&#39;SqlFormatter.php&#39;);
$query = "SELECT count(*),`Column1`,`Testing`, `Testing Three` FROM `Table1`
    WHERE Column1 = &#39;testing&#39; AND ( (`Column2` = `Column3` OR Column4 >= NOW()) )
    GROUP BY Column1 ORDER BY Column3 DESC LIMIT 5,10";
echo SqlFormatter::format($query);

Output:

##Only formatting without highlighting

If you do not need highlighting and only need to add indentation and line breaks, please set the second parameter to false.

Suitable for outputting error logs or other non-HTML data.

<?php
echo SqlFormatter::format($query, false);

Output:

Only highlight without formatting

There is a separate method named highlight It can ensure that the original format is not changed, and only syntax highlighting is added.

Applies to when the SQL has been well formatted and needs to be made more readable.

Compress query statement

The compress method can remove all SQL comments and compress unnecessary spaces.

Suitable for outputting multiple query statements and making them easy to copy and paste into the command line.

-- 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;
echo SqlFormatter::compress($query);

Output:

SELECT Id as temp, DateCreated as Created FROM MyTable;

Remove comments

If you need to keep the original format, but still need to delete the SQL comments, you can use the removeComments method to replace compress.

-- 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;
echo SqlFormatter::removeComments($query);

Output:

    SELECT
    Id 
    as temp, DateCreated as Created FROM MyTable;

Split multiple SQL statements into arrays

There is also a feature that has nothing to do with formatting, which can split multiple SQL statements into arrays. SQL statements are separated into arrays.

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);

Result:

DROP TABLE IF EXISTS MyTable;
CREATE TABLE MyTable ( id int );
INSERT INTO MyTable (id) VALUES (1),(2),(3),(4);
SELECT * FROM MyTable;

Why not use regular expressions?

Go and read the README~

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

The above is the detailed content of Format and highlight SQL statements in PHP. For more information, please follow other related articles on the PHP Chinese website!