Page 1 of 1

Result into array?

Posted: Wed Nov 14, 2007 9:46 am
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         |
+-----------------+--------------+

Posted: Wed Nov 14, 2007 9:52 am
by feyd
Sounds like an opportunity for a GROUP BY clause.

Posted: Wed Nov 14, 2007 9:59 am
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...

Posted: Wed Nov 14, 2007 10:13 am
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         |
+-----------------+--------------+

Posted: Wed Nov 14, 2007 11:26 am
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!