mysql - 将 MySQL 准备好的语句转换为 php 代码

Question

我有以下由 @jpw 提供的 MySQL 准备语句。查询工作正常。您可以在 SQLFiddle 上查看工作查询但我无法将这个准备好的语句转换为 php 代码。我需要帮助。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(gender = ''',
      gender,
      ''' and r.name = ''',
      r.name, ''') AS "',
      if(gender='m','Male','Female'), ' ', r.name, '" '
    )
  ) INTO @sql
FROM
  tbl_user u
JOIN 
  tbl_religion r ON u.religion = r.id;

SET @sql = CONCAT(
  'select timestampdiff(year, dob, now()) age, ', @sql, ' 
  from tbl_user u
  join tbl_religion r on u.religion = r.id
  group by timestampdiff(year, dob, now());');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

如果您使用 Phpmyadmin，我能想到的最简单的方法是使用[ Create PHP Code ] 选项。

或者您可以使用 Sql 格式化服务，例如 http://sqlformat.org/或http://www.dpriver.com/pp/sqlformat.htm 。快速将 SQL 格式化为 php 或任何语言。当然可能还有其他更好的方法来实现这一目标。

这是通过 sqlformat.org 格式化的示例

<?php
$sql = " ";
$sql .= "SET @sql = NULL;";
$sql2  = " ";
$sql2 .= "
 ";
$sql2 .= "SELECT GROUP_CONCAT(DISTINCT CONCAT( 'sum(gender = ''', gender, ''' and r.name = ''', r.name, ''') AS \"', if(gender='m','Male','Female'), ' ', r.name, '\" ' ) ) INTO @sql ";
$sql2 .= "FROM tbl_user u ";
$sql2 .= "JOIN tbl_religion r ON u.religion = r.id;";
$sql3  = " ";
$sql3 .= "
  ";
$sql3 .= "SET @sql = CONCAT( 'select timestampdiff(year, dob, now()) age, ', @sql, ' 
  from tbl_user u
  join tbl_religion r on u.religion = r.id
  group by timestampdiff(year, dob, now());');";
$sql4  = " ";
$sql4 .= "
 PREPARE stmt ";
$sql4 .= "FROM @sql;";
$sql5 = " ";
$sql5 .= "
 EXECUTE stmt;";
$sql6 = " ";
$sql6 .= "
 DEALLOCATE PREPARE stmt;";
$sql7 = " ";
$sql7 .= "
 ";

echo "$sql$sql2$sql3$sql4$sql5$sql6$sql7";

?>