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:afan@afan.net]
> 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 .
>
> -afan
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=raj@hccs.com
>
>

Hi,

I think "GROUP_CONCAT" will do it.

-Raj.



-----Original Message-----
From: afan pasalic [mailto:afan@afan.net]
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 .

-afan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=raj@hccs.com