Result into array?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Result into array?

Post by psurrena »

The code below works except I don't need people_fname twice. Is there a way to store membership_name into an array through the query? If I do this through PHP it still shows people_fname twice.

Thanks!

Code: Select all

mysql> SELECT membership_name, people_fname
    -> FROM membership,people_membership,people
    -> WHERE membership.membership_id=people_membership.membership_id
    -> AND
    -> people.people_id=people_membership.people_id
    -> AND
    -> people_membership.people_id=3;
+-----------------+--------------+
| membership_name | people_fname |
+-----------------+--------------+
| AICP            | John         |
| PP              | John         |
+-----------------+--------------+
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Sounds like an opportunity for a GROUP BY clause.
User avatar
seppo0010
Forum Commoner
Posts: 47
Joined: Wed Oct 24, 2007 4:13 pm
Location: Buenos Aires, Argentina

Post by seppo0010 »

You can use GROUP_CONCAT function, to retrieve all membership names, separated by commas, and then explode it in PHP

Code: Select all

mysql> SELECT GROUP_CONCAT(membership_name) AS membership_name, people_fname
    -> FROM membership,people_membership,people
    -> WHERE membership.membership_id=people_membership.membership_id
    -> AND
    -> people.people_id=people_membership.people_id
    -> AND
    -> people_membership.people_id=3; 
Also I think is faster to join your tables instead of selecting all...
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

That works great! Just had to add the final GROUP BY. It saves a bit of PHP such as implode().

Code: Select all

mysql> SELECT GROUP_CONCAT(membership_name) AS membership_name, people_fname
    -> FROM membership,people_membership,people
    -> WHERE membership.membership_id=people_membership.membership_id
    -> AND
    -> people.people_id=people_membership.people_id
    -> AND
    -> people_membership.people_id=3
    -> GROUP BY
    -> people.people_id;
+-----------------+--------------+
| membership_name | people_fname |
+-----------------+--------------+
| AICP,PP         | John         |
+-----------------+--------------+
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

This saved me so much code:

Code: Select all

SELECT GROUP_CONCAT(membership_name ORDER BY membership.membership_id SEPARATOR ', ') AS membership_name, people.people_id, etc.....
Thanks again!
Post Reply