Can someone help out with a mySQL query and the themes table?

[rcane]

I'd really appreciate if someone could give a little guidance on making themes table queries with regard to custom profile fields.

I understand the structure of the table--given how the custom profile fields reside in the 'variable' column.

I'm getting stumped when trying to get more than just a single variable + it's value.

For example I have firstname & lastname:  'cust_firstn' & 'cust_lastna'.

Among many others I'd like to run a report showing all the Id_member and the approrpriate variable/values that come with them.

Since there are technically "2" values coming back does that mean the SELECT statement needs something like:   SELECT id_member, `value`, `value`?

I know I'm close, but I figured you all would be more intimate with the table structure.

Anything will help.

[@rjen]

I created below sql query in the past to create a list of member details with the addition of three custom profile fields (cust_lidnum, cust_voorna and cust_achter).

Of course your fields will have different name, but the logic, using left outer joins on multiple queries will work if you adapt it to your needs...

Code Select Expand

SELECT *
FROM (
SELECT smf_members.ID_MEMBER Id, smf_members.member_Name Inlogaam, smf_members.ID_GROUP Groep, ledengroep.group_name Groepnaam, smf_members.real_Name Forumnaam, smf_members.email_address Email, smf_members.usertitle ExtraTitel, lid.cust_lidnum Lidnummer, voornaam.cust_voorna Voornaam, achternaam.cust_achter Achternaam, smf_members.posts Posts, FROM_UNIXTIME(smf_members.last_login) LaatstIngelogd, FROM_UNIXTIME(smf_members.date_registered) GeregistreerdOp
FROM smf_members

LEFT OUTER JOIN (
SELECT *
FROM smf_membergroups
) AS ledengroep ON smf_members.ID_GROUP = ledengroep.id_group

LEFT OUTER JOIN (
SELECT smf_themes.ID_MEMBER, smf_themes.value cust_lidnum, smf_themes.variable
FROM smf_themes
WHERE smf_themes.variable =  'cust_lidnum'
) AS lid ON smf_members.ID_MEMBER = lid.ID_MEMBER

LEFT OUTER JOIN (
SELECT smf_themes.ID_MEMBER, smf_themes.value cust_voorna, smf_themes.variable
FROM smf_themes
WHERE smf_themes.variable =  'cust_voorna'
) AS voornaam ON smf_members.ID_MEMBER = voornaam.ID_MEMBER

LEFT OUTER JOIN (
SELECT smf_themes.ID_MEMBER, smf_themes.value cust_achter, smf_themes.variable
FROM smf_themes
WHERE smf_themes.variable =  'cust_achter'
) AS achternaam ON smf_members.ID_MEMBER = achternaam.ID_MEMBER

) AS temp

ORDER BY ID

[shawnb61]

Some similar examples were provided a while back:
https://www.simplemachines.org/community/index.php?msg=4105162

This one is similar to @@rjen 's  example above, with discreet columns for custom fields, though it doesn't provide membergroup names, so it's a bit simpler:
https://www.simplemachines.org/community/index.php?msg=4105215