Jump to content

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


slyte33

Recommended Posts

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 :P

 
Edited by slyte33
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :P

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.