在PHP字符串中格式化MySQL代码 [英] Format MySQL code inside PHP string

问题描述

有什么程序IDE或不能在PHP字符串内格式化MySQL代码
我使用的是PHPStorm IDE，它不能这样做。

它对于PHP和MYSQL来说确实如此，但对于MySQL字符串来说却不是。我准备使用新的IDE，因为现在我必须手动格式化数百个数据库请求，这些请求是一行而不可读的。只有我选择的标准是ide可以自动完成。

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

应该变成

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

解决方案

使用正则表达式或SED / AWK来格式化一切，它给了我们即时更换地图的奖励。你可能有代码错误的机会很高，所以这是一个艰难的。

让我工作一点点，我可以看看我是否可以拿出一个好的
解决方案。是否保证将所有SQL封装在
双引号中？

编辑

试试这个

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

以下是一个例子

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

可爱吗？不，一点都没有，工作.... Yeup。

我会尝试创建一个过滤器文件，也许一个小bash程序或东西，因为我得到时间运行这个热门的混乱。

编辑

这是一些修改的代码，看起来漂亮的（sorta）

  printf'$ request1 =select * from where where = that and active = 1 order by asc ; \\\
'| 
 perl -pe's / select / SELECT / gi; s / from / \\\
 FROM / gi; s / where / \ WHERE / gi; s / and / \ n AND / gi; s / order by / \\\
 ORDER BY / gi; s / asc / ASC / gi; s / desc / DESC / gi;'| 
 awk'NR == 1 {pad = length（$ 0）/ 2;打印} NR> 1 {gsub（/ \r /，）; printf％* s％s\\\
，pad，，$ 0}'
 
 __OUTPUTS__ 
 $ request1 =SELECT * 
 FROM 
 WHERE这=该
和活动= 1 
 ORDER BY东西ASC; 
  

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

解决方案

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

这篇关于在PHP字符串中格式化MySQL代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！