CODEWITHSUNDEEP
mysqlheidisqlcmsmadesimple
Harmovitz
Harmovitz

Reputation: 43

MySQL separate values by user

i have recovered a database from a cmsms website and with Mysql i want to extract all the login information of the front-end users . i work with Heidisql

My problem user_proprerties DB is in this form:

Id UserID Type    Data
1       2 email   [email protected]
2       2 company lorme ipsum
3       2 fname   testname
4       5 email&  [email protected]
5       5 company empty
6       5 fname   dolor sir amed

i want the data to be in this form:
Userid email company fname
2 [email protected] lorem testname
5 [email protected] emptydolor sir amed

this is the current mysql query that i have

select 
    cms_module_feusers_users.id,
    cms_module_feusers_users.username,  
    cms_module_feusers_users.createdate,
    cms_module_feusers_users.expires,
    cms_module_feusers_properties.`data` email
from 
    cms_module_feusers_users
    inner join cms_module_feusers_properties on cms_module_feusers_properties.userid = cms_module_feusers_users.id
where
    cms_module_feusers_properties.title = 'email'

But now i'm stuck when i want the company name and fname.

Upvotes: 0

Views: 59

Answers (3)

Strawberry
Strawberry

Reputation: 33945

(Fractionally) slower, but cleaner...

SELECT p.userid
     , MAX(CASE WHEN type = 'email' THEN data END) email
     , MAX(CASE WHEN type = 'company' THEN data END) company
     , MAX(CASE WHEN type = 'fname' THEN data END) fname
  FROM cms_module_feusers_properties p
 GROUP
    BY p.userid;

Upvotes: 2

Robert Gabriel
Robert Gabriel

Reputation: 1190

Try this:

SELECT 
    cms_module_feusers_users.id,
    cms_module_feusers_users.username,
    cms_module_feusers_users.createdate,
    cms_module_feusers_users.expires,
    prop_data.data AS email,
    prop_data_company.data AS company,
    prop_data_fname.data AS fname
FROM
    cms_module_feusers_users
        INNER JOIN
    (SELECT 
        *
    FROM
        cms_module_feusers_properties
    WHERE
        type = 'email') prop_data ON prop_data.userid = cms_module_feusers_users.id
        INNER JOIN
    (SELECT 
        *
    FROM
        cms_module_feusers_properties
    WHERE
        type = 'company') prop_data_company ON prop_data_company.userid = cms_module_feusers_users.id
        INNER JOIN
    (SELECT 
        *
    FROM
        cms_module_feusers_properties
    WHERE
        type = 'fname') prop_data_fname ON prop_data_fname.userid = cms_module_feusers_users.id

Upvotes: 0

Vatev
Vatev

Reputation: 7590

Add a join for each field you need

SELECT 
    user.id,
    user.whatever,
    <other stuff from cms_module_feusers_users...>
    user_email.`data` as email,
    user_company.`data` as company,
    <other stuff from cms_module_feusers_properties...>
FROM cms_module_feusers_users user
LEFT JOIN cms_module_feusers_properties user_email ON user_email.userid = user.id AND user_email.title = 'email'
LEFT JOIN cms_module_feusers_properties user_company ON user_company.userid = user.id AND user_company.title = 'company'
<other joins from cms_module_feusers_properties...>

WHERE <conditions related to users...>

You need to use LEFT JOIN because some properties might be missing in cms_module_feusers_properties.

Also this assumes that each property appears at most once for each user.

Upvotes: 2

Related Questions