Format MySQL code inside PHP string

Asked 2012-06-12T21:32:39.710

Is there any program IDE or not that can format MySQL code inside PHP string e.g. I use PHPStorm IDE and it cannot do it.

It does that for PHP and MYSQL but not for MYSQL inside php string. I am ready to use new IDE because now i have to manually format hundreds of database requests that are one line and not readable. Only criteria for my choice is that ide can do that automatically.

<?php
...
$request1 = "select * from tbl_admin where admin_id= {$_SESSION['admin_id']} and active= 1 order By admin_id Asc";
...
?>

should become

<?php
...
$request1 = "SELECT * 
               FROM tbl_admin 
                  WHERE admin_id = {$_SESSION['admin_id']}
                  AND active = 1
                      ORDER BY admin_id ASC";
...
?>

php mysql format

Edit Report

4 Answers

The best way to do this in my opinion is to use Regular Expressions or SED/AWK to format everything, it gives us the bonus of replacement maps on the fly. The chance that you might have code errors though is high, so it's kind of tough.

let me work on it a bit and I can see if I can come up with a good solution. Is it guaranteed that you are encapsulating all SQL in double quotes?

EDIT

Try this

cd {{directory}} && find . -type f -print0 |
  xargs -0 perl -i.bak -pe 's/select/SELECT/g ; s/from/\n\tFROM/g ; s/where/\n\t\tWHERE/g ; s/and/\n\t\tAND/g ; s/order by/\n\t\tORDER BY/g ; s/asc/ASC/g ; s/desc/DESC/g ;'

Here's an example

$ printf '"select * from whatever where this = that and active = 1 order by something asc";\n' |
> perl -pe 's/select/SELECT/g ; s/from/\n\tFROM/g ; s/where/\n\t\tWHERE/g ; s/and/\n\t\tAND/g ; s/order by/\n\t\tORDER BY/g ; s/asc/ASC/g ; s/desc/DESC/g ;'

"SELECT * 
    FROM whatever 
        WHERE this = that 
        AND active = 1 
        ORDER BY something ASC";

Is it pretty? no, not at all, does it work.... Yeup.

I'll try creating a filter file and maybe a little bash program or something as i get time to run this hot mess.

EDIT

Here's some revised code, looks prettier (sorta)

printf '$request1 = "select * from whatever where this = that and active = 1 order by something asc";\n' | 
perl -pe 's/select/SELECT/gi ; s/from/\n  FROM/gi ; s/where/\n    WHERE/gi ; s/and/\n    AND/gi ; s/order by/\n      ORDER BY/gi ; s/asc/ASC/gi ; s/desc/DESC/gi ;' | 
awk 'NR == 1 {pad = length($0)/2; print} NR > 1 {gsub(/\r/,""); printf "%*s%s\n", pad, " ", $0}'

__OUTPUTS__
$request1 = "SELECT * 
             FROM whatever 
               WHERE this = that 
               AND active = 1 
                 ORDER BY something ASC";

answered 2012-06-20T16:33:51.080

Log, profile your SQL statements.
Use an external service such as this one - SQLFormat API - in order to format your SQL statements
You can also run it on your machine dowloading the Python sources.

answered 2012-06-21T21:12:15.990

I built a tool to do this for MS SQL Server and C# code. I'd be happy to alter the tool for this purpose for a small fee. http://www.softfrontiers.com/Downloads/ReIndenter.shtml

An hour or two of my time will save you many hours of frustration.

answered 2012-06-24T08:26:40.517

I have used PHPDesigner. i think this will be fulfill what you expecting.

answered 2012-10-09T09:36:13.220

Format MySQL code inside PHP string

4 Answers

Your Answer