16817

Mysql CASE statement usage with Zend

Question:

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.

which can have values - true or false.

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

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

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

However, it throws an error saying

<blockquote>

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

</blockquote>

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

Thanks

Answer1:

Personnaly, I don't like to get HTML as an answer from the DB. It gets confusing and harder to debug and change afterwards. Furthermore, you might get some errors due to the confusion with the ' and " and all the reserved characters in MySQL (<, >, ;, ...) I would suggest that you try this:

'final' => new Zend_Db_Expr("CASE a.red_flag WHEN 'true' THEN 1 ELSE 0 END")

Then do a check on the value of a.red_flag;

if($this->final) { $output .= '<div style=\"color:red; font-weight:bold\">'; } else { $output .= '<div style=\"color:blue; font-weight:bold\">'; } $output .= $this->head.' '.$this->tail; $output .= '</div>';

If the query still doesn't work. Try

echo $select->__toString; exit();

and check the query. Try the output that you got with the __toString on your database and check if it works. It's easier to fix it that way. You could also show the query string here and it'll be easier to debug.

Answer2:

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');

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

Recommend

  • Display Out of Stock at the end of the product list and search result page
  • magento - custom table join with catalog/product
  • generate JSON for the parent child rows. parent child depth is dynamic
  • Hive join query for performance
  • Joining multiple tables in bigquery
  • How To Query Many-to-Many Table (one table's values becomes column headers)
  • WP7 - read from CSV file? Or what to do with the data?
  • How to do popup on new site for 301 redirected users?
  • $this->params returns null in cakephp model
  • Why does PHP appear to evaluate this condition incorrectly?
  • JPA/Hibernate - Entity name seems to be important. If I rename to “Bob” works fine
  • NoMethodError in Controller#action - undefined method `keynote' for #
  • How to sort a same column both in asc order and desc order
  • Is it mandatory to have a doGet or doPost method?
  • jinja2 template not found and internal server error
  • How to add regEx in angular filter
  • Formatting a text in a table cell with PHPWord e.g. bold, font, size e.t.c
  • Warning: strpos() expects parameter 1 to be string, resource
  • JQuery form validation and submit scripts conflict
  • Find longest path less than or equal to given value of an acyclic, directed graph in Python
  • Want to understand iframe breakout code
  • Why does the font in these TD elements render at different sizes?
  • SQL - Select lowest values with group by and order by?
  • Flask not finding files in my package's 'static' directory
  • google maps autocomplete bounces back already cleared text …odd…odd…odd
  • Understanding RTF and edit it with vb.net
  • Why isn't obj.style.left = “200px”; working in this code?
  • How to autopopulate a field in SugarCRM form
  • Tell Git to stop prompting me for conflicts when none really exist?
  • OOP Javascript - Is “get property” method necessary?
  • Test if a set exists before trying to drop it
  • Jquery UI tool tip close icon
  • Breeze - Deleted Items nav properties bug
  • Display issues when we change from one jquery mobile page to another in firefox
  • Master page gives error
  • javaw.exe and eclipse startup problems
  • Redux, normalised entities and lodash merge
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • How to Embed XSL into XML