How to sort messages?

Question

I'm a novice programmer in PHP.

I have a simple messaging site with users in one table and another table with messages:

(date, from user, to users, message, primary key, subject).

First of all, I'm not sure this is the right way to have messages stored, so please tell me.

What I'm confused about is the display of the messages.

I want the page script to:

• connect to the database
• authenticate user
• go through all messages in messages table (good up to here)
• go through the to users field, see if user is one of them (splits it up into array, they are all already separated by spaces)
• if it is, then check to see if any other messages have the same users in their to users field
  • if it is, then group it with the others with the same users in their to users field (possibly in a second dimension of a multidimensional array)
  • else put it into a new "group" (with the next first dimension of a multidimensional array)

Then, I'll display only the ones of the first dimension of the multidimensional array, and when clicked will display the rest of the messages (all the second dimensions of a multidimensional array) in another place.

If somebody could help me with these steps with some bits of code or links to a website that could help, please do so.

Answer 1

To expand on Halfer's answer, the issue is that you're trying to store multiple things in the "to users" column. So you'll end up with something like toUsers = 5,24,242. That is really hard to parse from a database.

Instead, take that column out of that table and create a new table called "Message Recipients" which has "message id, user_receiving". Then you can have multiple rows, and this table will look like:

1, 5

1, 24

1, 242

...