Mysql: fetch value from database and php array in single query

Question

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

Sean · Accepted Answer · 2014-06-19 04:30:05Z

3

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

edited Jun 19, 2014 at 4:30

answered Jun 19, 2014 at 3:51

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

J.K Over a year ago

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'

Sean Over a year ago

What is the Collation of mixed_menu and db_category?

Sean Over a year ago

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

J.K Over a year ago

thats great man. So I guess the fake table, i can generate query, using php foreach traversal through the $category_array.

Collectives™ on Stack Overflow

Mysql: fetch value from database and php array in single query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related