Zend的MySQL CASE语句用法 [英] Mysql CASE statement usage with Zend

问题描述

我有以下查询，该查询从数据库中选择了一些记录:

I have the following query that selects some records from the database:

$select_person = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('a' => 'tableA'), 
                array(new Zend_Db_Expr('SQL_CALC_FOUND_ROWS a.id'),
                        'a.cid',  
                        'a.email',
                        'final' => new Zend_Db_Expr( "concat( '<div 
                            style=\"color:#1569C7; font-weight:bold\">',
                            a.head , ' ' ,  a.tail,  '</div>')" ),
                        'a.red_flag'
                )
        )   
        ->joinLeft(array('b' => 'tableb'), ... blah blah)
        ->where('blah blah')
        ->order('a.head ASC')

我想修改上面的查询，以便它根据

I want to modify the above query so that it selects a different value for 'final' depending on the value of

a.red_flag.

可以具有值-true或false.

which can have values - true or false.

我了解我可以使用mysql的CASE语句-例如，如下所示:

I understand I can use the CASE statement of mysql - eg something like the following:

'final' => new Zend_Db_Expr("CASE a.red_flag WHEN 'true' THEN '$concatstr1'
                        ELSE '$concatstr2' END")

$concatstr1 = "concat( '<div style=\"color:red; font-weight:bold\">', a.head , ' ' , a.tail, '</div>')" ;

$concatstr2 = "concat( '<div style=\"color:blue; font-weight:bold\">', a.head , ' ' , a.tail, '</div>')" ;

但是，它会引发错误

消息:SQLSTATE [42000]:语法错误或访问冲突:1064 您的SQL语法有误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 在'div附近 style ="color:red; font-weight:bold">'， 第1行的a.head，"，"

Message: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'div style="color:red; font-weight:bold">', a.head , ' ' , ' at line 1

如何使该查询起作用? 任何帮助将不胜感激.

How can I make this query work? Any help is greatly appreciated.

谢谢

推荐答案

最后，我在声明中发现了错误. 罪魁祸首是-我在$select_person语句中的$concatstr1和$concatstr2中使用了引号. 正确的查询应形成如下:

Finally, I found the error in my statement. The culprit was - I was using quotes in $concatstr1 and $concatstr2 inside the $select_person statement. The correct query should be formed as follows:

$select_person = $this->select()
        ->setIntegrityCheck(false)
        ->from(array('a' => 'tableA'), 
                array(new Zend_Db_Expr('SQL_CALC_FOUND_ROWS a.id'),
                        'a.cid',  
                        'a.email',
                        final' => new Zend_Db_Expr("CASE a.red_flag WHEN 'true' THEN $concatstr1 ELSE $concatstr2 END"),
                        'a.red_flag'
                )
        )   
        ->joinLeft(array('b' => 'tableb'), ... blah blah)
        ->where('blah blah')
        ->order('a.head ASC');

现在，当red_flag为true时，这将向我返回'final'-concatstr1的适当值，否则将向我返回concatstr2.

This is now returning me the appropriate value of 'final' - concatstr1 when red_flag is true otherwise it is returning me concatstr2.

这篇关于Zend的MySQL CASE语句用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！