Yup! That's THE ONE! Thanks Rajesh.
-afan Rajesh Mehrotra wrote: > Hi, > > I think "GROUP_CONCAT" will do it. > > -Raj. > > > > -----Original Message----- > From: afan pasalic [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 16, 2007 11:49 AM > To: mysql@lists.mysql.com > Subject: how ti put several records of one mysql table in one row of > html table? > > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
