CodeIgniter Forums
Been trying to find the solution! (help with database) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: Been trying to find the solution! (help with database) (/thread-31708.html)



Been trying to find the solution! (help with database) - El Forum - 06-29-2010

[eluser]Unknown[/eluser]
Lets say i have a table with three fields:

user_id , the session->userdata('id')
friend_id
are_friends , is a boolean

now i want to grab every id in the field user_id that is equal to session->userdata('id'),
because every individual row is connected with a single friend_id.

if this is confusing here is an example

i logged in as john with the id 34,
i have 4 entries that have the user_id as '34', the friend_id number of each one of them is
5,12,28,13
and are_friends boolean = 1, in all of them.

now i want to write in my model that if they are friends,

then i want to grab all the numbers in the friend_id field that are connected to the same user_id field. AND place them in a new table which i called "new"

this "new" table would have 2 fields

one with the user id and the other field stores all the numbers into a single entry (if that makes sense),

the problem is i also have no idea of how to setup a table that allows multiple numbers (or strings) in one entry.

i know this sounds confusing as i really don't know how to explain it well but any help would be great.


Been trying to find the solution! (help with database) - El Forum - 06-29-2010

[eluser]mddd[/eluser]
I don't really understand exactly what you want to do, but I have the feeling that you could achieve your goal by just adding a boolean field "new" to your existing table.


Been trying to find the solution! (help with database) - El Forum - 06-29-2010

[eluser]Unknown[/eluser]
i want to find were user_id = $this->session->userdata('id');

and grab all the friend_id numbers that they are connected to.

as in my table will look like this in phpmyadmin

|-user_id-|-friend_id-|-are_friends-|
34 14 1
12 23 1
9 15 1
34 19 1
34 45 1

as you can see i have multiple user_ids that equal 34 and i want to grab all the friend_id numbers that are connected with it , in this case 14,19 and 45.

and place these new numbers in a new table :

|-user_id-|-friends-|
34 14,19,45

but i also don't know how to create a table that allows multiple ids in a single entry.


Been trying to find the solution! (help with database) - El Forum - 06-29-2010

[eluser]n0xie[/eluser]
Why would you want multiple id's in a single entry?

Anyway:
Code:
$query = $this->db->select('friend_id')->where('user_id',$this->session->userdata('id'))->get('tablename');



Been trying to find the solution! (help with database) - El Forum - 06-29-2010

[eluser]pickupman[/eluser]
As noxie mentioned and as a database practice, you don't want to repeat data. Two options:
1.) You can use implode() to create a delimited string like , or |. implode takes an array and converts them into a delimited string of your choosing.
2.) You could keep your existing user table, and add a friends table. You user table would have details about the user like name, email, a/s/l, and etc. Then create a new table for friends. Here you would create the connections between users by creating a entry for each connection by users.user_id = friends.friend_id.
+ user_id + friend_id +
+ 34 | 1 +
+ 1 | 34 +
+ 34 | 2 +
+ 34 | 3 +


Option 1 may be faster at first because you have one table to work with, and all of the data is in one spot. However, this option will much too cumbersome as your lists of users/friends grow. As searching a plain text/varchar field will become pretty slow and may be limiting the total number of users you can save in a row. It will also become difficult to properly update users profiles. Option 2 gives you room to grow, and much better data management.