current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

mixed_menu

mixed_menu

SELECT mixed.unique_id, 
MENU_LABEL 
      case 
          when (--something...)
FROM mixed_menu mixed 
ORDER BY mixed.position ASC

Question: I want to write a mysql query which can retrieve unique_id, MENU_LABEL with the following condition.

  • CASE1:

    if type == 'db_category' then retrieve label from database (1.db_category) where id=unique_id

  • CASE 2:

    if type == 'category' then retrieve array value from php array (2.PHP Array) where array key=unique_id

1. db_category

db_category

2. PHP Array

$category_array = array('car'  => 'All Cars', 
                        'jeep' => 'All Jeeps'
                       );
share|improve this question

1 Answer 1

up vote 2 down vote accepted

It would be easier if you created a new table with your php array.

But using your current structure, you can create a temporary/fake table like this

SELECT * FROM (
  SELECT 'car' as `id`, 'All Cars' as `label`
  UNION
  SELECT 'jeep' as `id`, 'All Jeeps' as `label`
) php

So, you can use a query like -

SELECT mm.unique_id,

CASE
  WHEN mm.type = 'db_category' THEN dbc.label
  WHEN mm.type = 'category' THEN php.label
END as menu_label

FROM mixed_menu mm

LEFT JOIN db_category dbc ON mm.unique_id = dbc.id

LEFT JOIN (
  /*Create fake table*/
  SELECT 'car' as `id`, 'All Cars' as `label`
  UNION
  SELECT 'jeep' as `id`, 'All Jeeps' as `label`
) php ON mm.unique_id = php.id

ORDER BY mm.position ASC

here is a SQLFiddle example - http://sqlfiddle.com/#!2/5630cc/5

You would then need to build that fake table using your php array.

EDIT

Since your mixed_menu and db_category tables are COLLATION latin1_swedish_ci, and using SELECT UNION is defaulting to COLLATION utf8_general_ci you will need to declare this in your SELECT UNION

SELECT * FROM (
  SELECT 'car' COLLATE latin1_swedish_ci as `id`, 'All Cars' COLLATE latin1_swedish_ci as `label`
  UNION
  SELECT 'jeep' COLLATE latin1_swedish_ci as `id`, 'All Jeeps' COLLATE latin1_swedish_ci as `label`
) php

So now the query would be 

SELECT mm.unique_id,

CASE
  WHEN mm.type = 'db_category' THEN dbc.label
  WHEN mm.type = 'category' THEN php.label
END as menu_label

FROM mixed_menu mm

LEFT JOIN db_category dbc ON mm.unique_id = dbc.id

LEFT JOIN (
  /*Create fake table*/
  SELECT 'car' COLLATE latin1_swedish_ci as `id`, 'All Cars' COLLATE latin1_swedish_ci as `label`
  UNION
  SELECT 'jeep' COLLATE latin1_swedish_ci as `id`, 'All Jeeps' COLLATE latin1_swedish_ci as `label`
) php ON mm.unique_id = php.id

ORDER BY mm.position ASC
share|improve this answer
    
thanks for your fast reply and efforts, i am working on it. shows the error- #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'case' –  Devs Jun 19 '14 at 4:07
1  
What is the Collation of mixed_menu and db_category? –  Sean Jun 19 '14 at 4:13
    
latin1_swedish_ci –  Devs Jun 19 '14 at 4:22
1  
See the EDIT in my answer. You will need to declare the fake table's Collation implicitly -> SELECT 'car' COLLATE latin1_swedish_ci as id, to match the Collation of mixed_menu/db_category –  Sean Jun 19 '14 at 4:31
    
thats great man. So I guess the fake table, i can generate query, using php foreach traversal through the $category_array. –  Devs Jun 19 '14 at 4:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.