RE: how ti put several records of one mysql table in one row of html table?

2007-10-16 Thread Rajesh Mehrotra 
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 | 212245 | 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 | 212245 | 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]

Re: how ti put several records of one mysql table in one row of html table?

2007-10-16 Thread afan pasalic 
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 | 212245 | 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 | 212245 | 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]