MySQL 运行直接关系但 CodeIgniter 中的语法错误

发布时间: 2015/6/11 23:53:08
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

编辑: 我看问题是与多个查询在 PHP 中,所以对于 codeigntier 还有什么锻炼?不必使用 mysqli 函数他们自我还是可以使用内建在 Codeigniter DB 类吗?

我有一个查询,运行正常,当我将它粘贴到 PHPMyAdmin,但当我在 codeigniter 中运行它时它会导致一个语法错误,任何人都可以提供援助?

这里是 SQL

SET @sql = NULL;

SELECT 
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(IF(user_fields_to_users.map_field_id=''',field_id,''',user_fields_to_users.map_data,NULL)) AS ', field_name)) INTO @sql

FROM user_custom_fields;

SET @sql = CONCAT('SELECT *
                    ,', @sql, '
                    FROM user_accounts  LEFT JOIN user_fields_to_users ON
                    user_accounts.user_id = user_fields_to_users.map_user_id
          GROUP BY user_accounts.user_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

我使用的 mysqli 与 codeigniters$this->db->query();

我得到的错误是

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(user_fields_t' at line 3

解决方法 1:

问题是多个查询,在这里是我使用的代码

$sql = "
SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(IF(user_fields_to_users.map_field_id=''',field_id,''',user_fields_to_users.map_data,NULL)) AS ', field_name)) INTO @sql
FROM user_custom_fields;
SET @sql = CONCAT('SELECT *
                    ,', @sql, '
                    FROM user_accounts  LEFT JOIN user_fields_to_users ON
                    user_accounts.user_id = user_fields_to_users.map_user_id
          GROUP BY user_accounts.user_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
";
if ($this->db->conn_id->multi_query($sql)) {
    do {
        if ($result = $this->db->conn_id->store_result()) {
            $this->selection_total_results = $result->num_rows;
            while ($row = $result->fetch_array()) {
                $result_data[] = $row;
            }
            $result->free();
        }
    } while ($this->db->conn_id->next_result());
}
官方微信
官方QQ群
31647020