PHP/MySQL: Sorting Results a Different Way

Question

I have to table, contacts and groups.
Inside of contacts I have 3 columns: id first_name last_last
Inside groups I have 2 columns: id linked

I want to put contacts into groups. So I thought the best way would be that when you added a contact to a group it would add that contacts ID to a list of contact ID's separated by commas in the linked column.

So if you added contact 1, 4, 14, and 24 to a specific group the linked column would have this value 1,4,14,24

Now I want to display the contacts in that group. So I explode() the linked value and then use a foreach() to cycle through the exploded array and on each loop select through MySQL the first name and last name of that contact and display it.

All of that works fine. My problem is I realized I want to sort the contacts being displayed in that group. But they display in the order they were placed in the group.

I want to be able to sort by first name ascending or descending but I have no idea how because I'm selecting each contact in a foreach() and echoing it.

P.S. I thought maybe make a column on the contact called groups and store all the groups that contact is in the same way I stored all the contacts that were in a group. So if the contact was in groups 1, 2, 4, and 6 then that contacts groups column would have a value of 1,2,4,6. The only problem is, lets say I'm search for all contacts in group 6, how do I search though that list in a MySQL statement? I thought the best way would be SELECT first_name FROM contacts WHERE groups LIKE '%6%' but then what if a contact is in group 60? Wouldn't it select that contact too even if it isn't in the right group?

Terje D. · Accepted Answer · 2013-03-15 08:38:20Z

1

Don't store multiple values in the same field in a database! As you have observed, you will get problems when you want to handle each value separately.

In your case, insert one row in the groups table for each group the person belongs to. The join the two tables to get the information in the way you want.

E.g: to find which groups Santa Claus blongs to:

SELECT groups.id 
FROM contacts
INNER JOIN groups 
ON groups.linked = contacts.id
WHERE firstname = 'Santa' AND lastname = 'Claus'

Or to find who is in group 1:

SELECT firstname, lastname 
FROM contacts
INNER JOIN groups
ON groups.linked = contacts.id
WHERE groups.id = 1
ORDER BY lastname, firstname

To get the results as comma separated lists, you can use the GROUP_CONCAT function:

SELECT groups.id,
       GROUP_CONCAT(firstname, ' ', lastname
                    ORDER BY lastname SEPARATOR ', ') AS groups 
FROM contacts
INNER JOIN groups
ON groups.linked = contacts.id
GROUP BY groups.id

In order to store group names, you may benefit from using 3 tables

Contact (id, firstname, lastname)

Group (id, groupname)

Group_membership (contact_id, group_id)

answered Mar 15, 2013 at 8:38

Terje D.

6,3151 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Hughes Over a year ago

Thank you, that help tremendously. I know PHP like the back of my hand but only know the basics of MySQL.

Collectives™ on Stack Overflow

PHP/MySQL: Sorting Results a Different Way

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related