使用mysql变量的Mysql Query在Zend Framework 1中不起作用 [英] Mysql Query with mysql variable not working in Zend Framework 1

问题描述

Zend配置为:resources.db.adapter ="pdo_mysql"

Zend config is : resources.db.adapter = "pdo_mysql"

获取消息的查询:SQLSTATE [HY000]:常规错误

$db = Zend_Db_Table::getDefaultAdapter();
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);       


        $qry =  $db->fetchAll(" SET @rank = 0 ;
                                SELECT
                                   @rank := @rank +1 AS rank,
                                    SUM(user_scoring_data.user_points)  AS user_points,
                                    league_team.user_id

                                FROM league_team 
                                RIGHT JOIN user_scoring_data ON league_team.league_team_id = user_scoring_data.league_team_id

                                WHERE league_id = '$league_id' GROUP BY user_scoring_data.league_team_id 
                                ORDER BY user_points DESC"
                            );

如果我删除mysql变量，它将起作用:

if I remove the mysql variable, it works:

$db->fetchAll("
                                SELECT
                                    SUM(user_scoring_data.user_points)  AS user_points,
                                    league_team.user_id

                                FROM league_team 
                                RIGHT JOIN user_scoring_data ON league_team.league_team_id = user_scoring_data.league_team_id

                                WHERE league_id = '$league_id' GROUP BY user_scoring_data.league_team_id 
                                ORDER BY user_points DESC"
                            );

*注意:查询已在phpmyadmin中直接测试，没有错误.

*Note: query was tested directly in phpmyadmin and has no errors.

推荐答案

在MySql中设置变量似乎被认为是单独的查询. (fetch *()无法处理多个呼叫)

It seems setting a variable in MySql is considered a separate query. ( fetch*() cannot handle multiple calls)

所以对于那些有这种问题的人:

so for those with this kind of issue:

$db->query("SET @rank = 0 ;");

$db->fetchAll("SELECT
                                   @rank := @rank +1 AS rank,
                                    SUM(user_scoring_data.user_points)  AS user_points,
                                    league_team.user_id

                                FROM league_team 
                                RIGHT JOIN user_scoring_data ON league_team.league_team_id = user_scoring_data.league_team_id

                                WHERE league_id = '$league_id' GROUP BY user_scoring_data.league_team_id 
                                ORDER BY user_points DESC"
                            );

这篇关于使用mysql变量的Mysql Query在Zend Framework 1中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！