Format MySQL code inside PHP string

Question

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"; ... ?> 

Answer 1

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