Question 232344 in Mysql Question posted in
The official MySQL documentation can be found here.

Retrieve multiple key array from to mysql in key value pair

i have the data below store in mysql database, but I having problem to retrieve in desire form

here is my data

id key value
1 contact_0_type phone
2 contact_0_value 1234567890
3 contact_1_type email
4 contact_1_value [email protected]

this is query im trying to use but failed

SELECT
    t1.value AS type,
    t2.value AS value,
    SUBSTRING_INDEX(t2.key, '_', 2),
    CONCAT( SUBSTRING_INDEX(t2.key, '_', 2), '_value')
FROM
    settings AS t1
JOIN
    settings AS t2
    ON t1.key = CONCAT(SUBSTRING_INDEX(t2.key, '_', 2), '_value')
WHERE
    t1.key LIKE 'contact_%_type'

Any help is appreciated. Thank you

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally find the correct way to get the desired result.

    SELECT
    settings.`key` AS `type_key`, settings.`value` AS `type`, t3.`key` AS value_key, t3.`value`
FROM
    settings
LEFT JOIN (
    SELECT
        t2.key AS `key`, t2.value
    FROM
        settings AS t2
    WHERE
        t2.key LIKE 'contact_%_value'
    ) AS t3 ON t3.`key` = CONCAT(SUBSTRING_INDEX(settings.`key`, '_', 2), '_value')
WHERE
    settings.`key` LIKE 'contact_%_type'

    Instead of joining directly with same table, I have to use subquery

    Thank you


  2. 0

    Table redesign suggestion

    id key type value
    1 contact_0_type phone 1234567890
    2 contact_1_type email [email protected]

    Now its a simple

    SELECT * FROM Table t
WHERE  t.key LIKE 'contact_%_type'

    Could even order by key to get them in order of priority. Remember there is no underlying sort order on rows in a table so you have to ORDER the resultset yourself

    ORDER BY key
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search