-none- 2007-10-16 - By afan pasalic
Back
hi,
I have "standard" organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+
| organization_id | address_id | full_name | phone | fax |
website |
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+
| 8200 | 1 | 1520 | 2122457777 | 2122457730 |
http://www.abcinc.com |
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*************************** 1. row ***************************
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*************************** 2. row ***************************
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*************************** 3. row ***************************
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual Member
*************************** 4. row ***************************
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql> select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-- ---- ---- ----+-- ---- --+-- ---- ---- -----+
| organization_id | field_id | cust_field_value |
+-- ---- ---- ----+-- ---- --+-- ---- ---- -----+
| 8200 | 12 | 2005-04-01 |
| 8200 | 14 | Small Member |
| 8200 | 16 | 1-4 |
| 8200 | 21 | Retail |
+-- ---- ---- ----+-- ---- --+-- ---- ---- -----+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+-- ---- ----+-- ---- -----+-- ---- ---- ----+
| organization_id | address_id | full_name | phone | fax |
website | start date | cancel date | membership type |
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+-- ---- ----+-- ---- -----+-- ---- ---- ----+
| 8200 | 1 | 1520 | 2122457777 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 | Smal Member |
+-- ---- ---- ----+-- ---- ----+-- ---- -----+-- ---- ----+-- ---- ----+-- ----
-- ---- ---- ---- ---+-- ---- ----+-- ---- -----+-- ---- ---- ----+
thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
