[SOLVED] Combine User lists
13 years 11 months ago #135472
by Kingsley
[SOLVED] Combine User lists was created by Kingsley
Hi
I am relatively new to Joomla and absolutely new to CB,
is there a way to combine user lists in CB?
ie take 2 user lists, merge the data togther and then sort it ?
I am building a cb database with each CB entry as a "family" (say 2 adults and 2 children) rather than a cb entry only having 1 person. Yes, i know not perfect database design but if I split them all as individuals would then have 4 (or more) individual records and duplication of postal addresses, phone numbers, contact info for each child of parents etc. So either way it is messy.
The problem is that I would then like to be able to create a list with all children (names) say between ages 10 and 15 all sorted by age.
So am potentially searching through more than 1 field (several date fields) and then harvesting only the relevant childs name (depending on date of birth).
Easy to search and sort for one child in the record, but need a seperate list to search for additional children. If I could then combine them and sort them, perfect.
Any suggestions would be appreciated.
If I need to jump into coding and SQL ... any suggestions on where to head for info ? I have done similar with ASP & Access in years gone by, but not played much with php.
Post edited by: krileon, at: 2010/06/21 16:51
I am relatively new to Joomla and absolutely new to CB,
is there a way to combine user lists in CB?
ie take 2 user lists, merge the data togther and then sort it ?
I am building a cb database with each CB entry as a "family" (say 2 adults and 2 children) rather than a cb entry only having 1 person. Yes, i know not perfect database design but if I split them all as individuals would then have 4 (or more) individual records and duplication of postal addresses, phone numbers, contact info for each child of parents etc. So either way it is messy.
The problem is that I would then like to be able to create a list with all children (names) say between ages 10 and 15 all sorted by age.
So am potentially searching through more than 1 field (several date fields) and then harvesting only the relevant childs name (depending on date of birth).
Easy to search and sort for one child in the record, but need a seperate list to search for additional children. If I could then combine them and sort them, perfect.
Any suggestions would be appreciated.
If I need to jump into coding and SQL ... any suggestions on where to head for info ? I have done similar with ASP & Access in years gone by, but not played much with php.
Post edited by: krileon, at: 2010/06/21 16:51
Please Log in to join the conversation.
krileon
13 years 9 months ago #138280
by Kingsley
Replied by Kingsley on topic Re:Combine User lists
Looking forward to using 1.2.3 but also found a very flexible solution to problem.
I used sql 2 Excel ... a free (or paid) joomla module etc,
This gives full SQL flexibility in searching.
eg
My database has each record with several "family" members eg 2xadults and 4xchildren, each with relevant firstName, LastName, Group & Type (and lots more).
I used SELECT (& UNION SELECT) to search each family member (adult or child), for relevant group and type value, if matched ... then put into same column of spreadsheet (list), finally sort by column in list.
The CONCAT function joins text fields together ... in my case: FirstName, LastName & (familyName). I used this as sometimes the last name of children is different to family name of adult parent.
+++++++++++++++++++++++
SELECT jos_comprofiler.cb_adult1group1 AS Groups, jos_comprofiler.cb_adult1boattype1 AS Type,
CONCAT(jos_comprofiler.cb_adult1firstname, ' ',jos_comprofiler.cb_adult1lastname, ' (', jos_users.name, ')') AS Name
FROM jos_users
INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.id
WHERE jos_comprofiler.cb_adult1group1 = "Blue" OR jos_comprofiler.cb_adult1group1 = "Red"
UNION SELECT jos_comprofiler.cb_adult1group2 AS Groups, jos_comprofiler.cb_adult1boattype2 AS Type,
CONCAT(jos_comprofiler.cb_adult1firstname, ' ',jos_comprofiler.cb_adult1lastname, ' (', jos_users.name, ')') AS Name
FROM jos_users
INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.id
WHERE jos_comprofiler.cb_adult1group2 = "Blue" OR jos_comprofiler.cb_adult1group2 = "Red"
ORDER BY Groups, Type ASC, Name DESC
+++++++++++++++++++++++++++++++++
The inner join was to enable info to be pulled out of the standard Joomla fields, matched with CB fields.
Finally SQL2Excel also offers a datagrid plugin so results can be displayed in HTML or displayed as spreadsheet or downloaded as spreadsheet.
Little bit tricky to install as there are 3 or 4 "bits", but all worked really well.
This module saved me.
Kingsley
I used sql 2 Excel ... a free (or paid) joomla module etc,
This gives full SQL flexibility in searching.
eg
My database has each record with several "family" members eg 2xadults and 4xchildren, each with relevant firstName, LastName, Group & Type (and lots more).
I used SELECT (& UNION SELECT) to search each family member (adult or child), for relevant group and type value, if matched ... then put into same column of spreadsheet (list), finally sort by column in list.
The CONCAT function joins text fields together ... in my case: FirstName, LastName & (familyName). I used this as sometimes the last name of children is different to family name of adult parent.
+++++++++++++++++++++++
SELECT jos_comprofiler.cb_adult1group1 AS Groups, jos_comprofiler.cb_adult1boattype1 AS Type,
CONCAT(jos_comprofiler.cb_adult1firstname, ' ',jos_comprofiler.cb_adult1lastname, ' (', jos_users.name, ')') AS Name
FROM jos_users
INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.id
WHERE jos_comprofiler.cb_adult1group1 = "Blue" OR jos_comprofiler.cb_adult1group1 = "Red"
UNION SELECT jos_comprofiler.cb_adult1group2 AS Groups, jos_comprofiler.cb_adult1boattype2 AS Type,
CONCAT(jos_comprofiler.cb_adult1firstname, ' ',jos_comprofiler.cb_adult1lastname, ' (', jos_users.name, ')') AS Name
FROM jos_users
INNER JOIN jos_comprofiler ON jos_users.id = jos_comprofiler.id
WHERE jos_comprofiler.cb_adult1group2 = "Blue" OR jos_comprofiler.cb_adult1group2 = "Red"
ORDER BY Groups, Type ASC, Name DESC
+++++++++++++++++++++++++++++++++
The inner join was to enable info to be pulled out of the standard Joomla fields, matched with CB fields.
Finally SQL2Excel also offers a datagrid plugin so results can be displayed in HTML or displayed as spreadsheet or downloaded as spreadsheet.
Little bit tricky to install as there are 3 or 4 "bits", but all worked really well.
This module saved me.
Kingsley
Please Log in to join the conversation.
Moderators: beat, nant, krileon
Time to create page: 0.218 seconds
