Is this a functional way of exploding userID+update mysql DB CONCAT

[slyte33]

I didn't know how to phrase the title correctly..

I'm creating a friends list for players in my game.

I could easily create a table and have fields "players_id" and "friends_id".

so my table "friends" could be

friends
players_id | friends_id
----------------------------
players_id = 1 | friends_id = 2
players_id = 1 | friends_id = 11
players_id = 1 | friends_id = 6
players_id = 1 | friends_id = 64

but with 1,000 players each having 100 friends, that's 100,000 rows.
..or i could do this:

friends
players_id | friends_id
----------------------------
players_id = 1 | friends_id = (2)(11)(6)(64)

with this code:

$get_friends= explode(')(', substr($player['friends_id'], 1, -1));
foreach($get_friends as $friend_list)
{
$list_ids_query=$db->execute("select * from players where pID='".$friend_list[0]."'");
$friends=$list_ids_query->fetchrow();
if ($friends['id']!=$_GET['friendID']){$add_friend=1;}else{$add_friend=0;}
}
if ($add_friend==1){
$update=$db->execute("update friends set friends_id= CONCAT( friends_id, '(".$_GET['friendID'].")')");
}else{
//is already friend
} 

I'm just wondering if that saves space, works more efficiently and if I'm doing it correctly to begin with.

 

Thanks

 

EDIT: Accidentally posted thread without finishing if you didn't get the rest hitman6003

 

Edited December 9, 2013 by slyte33

[hitman6003]

but with 1,000 players each having 100 friends, that's 100,000 rows.

meh... Are you hurting for space, than an extra MB or two (cause a row of 2 int columns doesn't take much room) is really gonna make a difference?

 

Use an index and don't worry about it.

 

Doing it the second way, if you wanted to query "Who's friends list is user 123 on?", you would have to query an expand everyone's list to get that information.

[slyte33]

meh... Are you hurting for space, than an extra MB or two (cause a row of 2 int columns doesn't take much room) is really gonna make a difference?

 

Use an index and don't worry about it.

 

Doing it the second way, if you wanted to query "Who's friends list is user 123 on?", you would have to query an expand everyone's list to get that information.

 

I wasn't sure how much space it'd take, thanks for the info.

You're right, but what if I wanted to use this for say, checking which users read a forum thread. If new post in thread update the list of ids to blank VS having to delete 1,000s of rows in "users_who_havent_read_thread" table; would that be efficient.

 

Heck, is the second way I listed efficient for anything

[hitman6003]

what if I wanted to use this for say, checking which users read a forum thread. If new post in thread update the list of ids to blank VS having to delete 1,000s of rows in "users_who_havent_read_thread" table; would that be efficient.

It's always easier to store who has seen something like a thread than who hasn't. I've never looked at phpBB's code, but I would imagine they associate the user to the thread when it's been read. So, when I click on it, a row in a table is inserted that says "hitman6003 has read thread xyz at 0230GMT".

[mac_gyver]

forum software generally stores unread posts, which you only need to store for active users.

 

the id of the last post at the time of the last visit is stored for each user. on the next visit, posts made since the last visit are added to the 'unread' table and the id of the last post is stored. as the posts are read, the row with their id/the user's id is removed from the 'unread' table. also, there is generally a 'mark all posts are read' button that simply removes all that visitor's records from the 'unread' table.

[slyte33]

forum software generally stores unread posts, which you only need to store for active users.

 

the id of the last post at the time of the last visit is stored for each user. on the next visit, posts made since the last visit are added to the 'unread' table and the id of the last post is stored. as the posts are read, the row with their id/the user's id is removed from the 'unread' table. also, there is generally a 'mark all posts are read' button that simply removes all that visitor's records from the 'unread' table.

 

Makes sense...logical answers here...

so storing a list isn't really a good way of doing 'anything' if theres better ways?

[mac_gyver]

by storing each data item in its own row, you can perform operations directly using queries. to add an item, just inserting a row (if you set up a unique key, you don't even need to try to select the item first to test if it already exists before trying to insert it). to delete an item, simply delete the row. you can get a count or retrieve one, any, or all matching values directly in a query. you can run one JOIN'ed query using the values to get related information (such as getting the friend's usernames based on the stored friend's ids.)

 

storing a list requires two queries and a bunch of php code to accomplish any of these operations.