multiple entries in one field

greg's picture

He has: 1,581 posts

Joined: Nov 2005

I am currently developing a private messaging system for members on my site and am including a member blocking system, where users can block other users from pm'ing them.

What would be the best way to store a list of usernames that a user has blocked from being able to pm them?

I thought about storing them all in one field using a delimiter, but the only way I know of searching them then is to pull the lot out into a variable and explode by the delimiter.
Is there a way with mysql to search a field between each delimiter without getting out the data?

Or should I approach this another way. A new row for each member a user blocks is going to make a hugely bloated table.

Cheers

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

The database relationship between members and ignored members are many to many. For example, if I was a member, I could ignore several other members, and at the same time, I could be ignored by several other members.

This type of database relationship requires another table to be added. This joining table would have two fields: member_id, and blocked_member_id. When a member wants to block another member both member ids go into their respective fields.

When querying the database, if I wanted to find all the members I blocked, I would run:

SELECT * FROM block_members WHERE member_id = $my_id

If I wanted a list of all the members that blocked me, I would run:

SELECT * FROM block_members WHERE blocked_member_id = $my_id

(Your field and table names will vary.)

You can also grab entire profiles with some JOIN statements in the query.

If you want to post your DB structure, I can give you some more exact queries and examples.

greg's picture

He has: 1,581 posts

Joined: Nov 2005

I have created a new table for recording this (and also friends lists) but was trying to avoid a new row for every memebr one user blocks

If block 20 people, that's 20 rows containing my username and the username of the blocked member.
I was trying to use only one row, so I would have a row with my username, then a field of some kind with all the members in that one field of all the members I blocked.

since posting this I thought about using a file. Each member will have their own folder on the server anyway as I allow uploads. So I could create a blocked member file and friends file.
But I truly don't know the best way, or if my preffered use of one DB row is at all possible.

I might try to see how PHPBB does it.

Another query, rather than a new post I will plonk it here.
When a member posts a new private message, I obviously check the data they typed before storing it in DB. If they did something wrong I store the username they where sending to and their message, so they don't have to type it again. How do I store the message with a max of 5,000 chars without post data? It must be impractical to use a $_SESSION to store potentially 5,00 chars?
so what is the best way of that?

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

$_SESSION wouldn't be a bad way to do it as far as I know. 5,000 chars will be a little more than 5k of memory - not a big deal. Just clear it when the form is submitted.

Yes, blocking 20 people would create 20 rows, but that's how you do it if you follow database normalization. Selecting 20 rows from a DB of only a couple rows of member ids is nothing. If you feel that there will be some performance issues, you can look into query caching, but I wouldn't store them in flat files. Databases are made to be queried, files are not.

phpBB has a table that contains the ids of both profiles and marks whether they are friends or foes. I have no idea why the table is called "Zebra" in my installation, but I attached the table layout.

AttachmentSize
1204406292512.png 1.75 KB
greg's picture

He has: 1,581 posts

Joined: Nov 2005

thank for the advice. I've made it so for each user a member blocks a single row is created. I will limit members to 50 pm's anyway, so it shouldn't be too bad.
But didn't call the table name after a wild animal Confused

Now I just have to battle through all the if/elseifs Laugh

Cheers

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

Quote: But didn't call the table name after a wild animal Confused

I was confused on that as well. Probably an inside hoke or something.

Glad to help. Smiling

JeevesBond's picture

He has: 3,955 posts

Joined: Jun 2002

pr0gr4mm3r's advice is good (just storing two id's won't use much space anyway). However, just for future reference, you can store multiple values in a single field using serialise. This is good for doing multi-page forms, it should be avoided for storing things in a database though. Data processing in PHP is slower than a database, and it makes it harder to transfer data to new software.

a Padded Cell our articles site!

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.