[SOLVED] Multiple records into one line and other things...

[tellivision]

Hi everyone,

 

I'm still getting the hang of php and there's something that's got me stumped.  I'm wondering if someone can help me.

 

I wish to create a table with columns consisting of member name, member e-mail and a column that contains multiple records associated with a single member name:

 

|  Member name  |  Member e-mail  | Records associated with member |

_____________________________________________________

| member's name |  e-mail address  | record 1, record 2, record 3 etc. |

 

Each record (those to be displayed in the column 'record associated with member' in the table has a member id linked to it, the member's name and e-mail are in another table also with the member id.  The join between the two mysql tables would be through the member id attribute.

 

Is there any possible way to put together a script for such a setup?

 

Thanks in advance

 

[anthylon]

I'm not sure I understand you well enough. I think you have two tables and want to query data from it using JOIN or...

 

If that is what you need, let's try this:

 

tbl_members:

id | fname | lname | .... | club_id |

 

 

 

tbl_club

id | club_name | address | ... |...

 

So, if you need to make query from these tables to get for example all members of club with id 7:

SELECT tbl_members.fname, tbl_members.lname, ..., tbl_club.club_name, tbl_club.address ....

FROM tbl_members LEFT JOIN tbl_club ON tbl_members.club_id = tbl_club.id WHERE club.id=7

 

I'm sorry if I understood you wrong. I have difficulties with english. Also I think you hadn't explain it well.

 

Anthylon 

[tellivision]

Sorry about not being clear enough.  Maybe this general example will help explain better:

 

2 tables; memberinfo and membersports

 

memberinfo has the following information: memberid, name, email

 

membersports has memberid and sport.  A single member can play many sports, thus resulting in membersports looking like:

 

memberid      sport

1                  tennis

1                  golf

2                  tennis

2                  football

2                  rugby

 

The join would be on memberid.

 

 

I want the query to output the information in a table in the following format:

 

    Name                      E-mail                    Sports

Member1 name      Member1 e-mail            tennis, golf

Member2 name      Member2 e-mail            tennis, football, rugby

 

 

Is that a little clearer?

 

Thanks again for taking the time to look at this.  I appreciate your help.

 

[anthylon]

Well I would make it with three tables. We don't want (for example sport "tennis") write every time when adding it to some member. For optimisation of database (less data and runing faster), we don't want repeating of some data. But you will understand I'm sure.

 

So, here is code to insert in database (I named my db_sport):

 

DROP TABLE IF EXISTS `members`;
CREATE TABLE `members` (
  `id` int(11) NOT NULL auto_increment,
  `m_name` varchar(30) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `members` VALUES (1, 'Osman', '[email protected]');
INSERT INTO `members` VALUES (2, 'Eldin', '[email protected]');

DROP TABLE IF EXISTS `membersinfo`;
CREATE TABLE `membersinfo` (
  `id` int(11) NOT NULL auto_increment,
  `member_id` int(11) NOT NULL,
  `sport_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `membersinfo` VALUES (1, 1, 2);
INSERT INTO `membersinfo` VALUES (2, 1, 3);
INSERT INTO `membersinfo` VALUES (3, 1, 5);
INSERT INTO `membersinfo` VALUES (4, 2, 1);
INSERT INTO `membersinfo` VALUES (5, 2, 2);
INSERT INTO `membersinfo` VALUES (6, 2, 4);

DROP TABLE IF EXISTS `sports`;
CREATE TABLE `sports` (
  `id` int(11) NOT NULL auto_increment,
  `m_name` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `sports` VALUES (1, 'Tennis');
INSERT INTO `sports` VALUES (2, 'Football');
INSERT INTO `sports` VALUES (3, 'Swimming');
INSERT INTO `sports` VALUES (4, 'Handball');
INSERT INTO `sports` VALUES (5, 'volleyball');
INSERT INTO `sports` VALUES (6, 'skiing');

 

1. Table members is used only for storing data about members (name, email, phone... etc.)

2. Table sports is used for storing all kind of sports (I added few)

3. Table membersinfo is used for adding sport to some members. We just need to store member's id (member_id) and sport_id in that database. That way we don't repeating name of sports or name of member. We just use their IDs ...

 

Now your SQL query could be like this:

SELECT members.m_name, members.email, sports.m_name
FROM members
LEFT JOIN membersinfo ON ( members.id = membersinfo.member_id ) 
LEFT JOIN sports ON ( sports.id = membersinfo.sport_id ) 
LIMIT 0 , 30

 

Of course you can add some WHERE condition and simillar.

 

I hope this can be useful for you. If this is what you asked for, than please use Topic Solved button (bottom - left side). Thanks,

 

Anthylon

[tellivision]

Thanks, Anthylon