• 0

[PHP][MySQL] adding items as an array to have dynamic content


Question

I'm trying to make an Addressbook, and one of my features is that you can add a group and add contacts to that group.

Now, my problem is that I'm not sure how do to this. I can make a table called `groups` and add static names like Work, Family, School, etc. But by doing so I don't know how each registered user can choose one of these groups and add it to the list of groups for their personal accounts.

Since i'm using InnoDB I'm referencing each table to the users table with a foreign key.

So I was thinking of doing something like this

contacts table (simplified example)

==========

id

user_id

group

name

address

and in the group column have the data stored like so: {Work, School, Friends}

and somehow do a query that will get each array item and output it so it looks like this kinda

Group

----------------

Work (11)

School (2)

Friends (6)

where the (x) means the number of members in that group.

Link to comment
Share on other sites

16 answers to this question

Recommended Posts

  • 0

Of the top of my head, maybe something like the following?

  • contact.id
  • contact.name

  • group.id
  • group.name
  • group.contact_id

  • group_contact.group_id
  • group_contact.contact_id

?

Link to comment
Share on other sites

  • 0

is the your idea for the database structure?

I was more wondering what logic behind the script that will write and read to the database as well as the database logic that would store the groups and associate them with the users' contacts.

I dunno why I'm having such a hard time trying to figure out a good way to make this address book with groups ;-(

Link to comment
Share on other sites

  • 0

You need to know how you're going to store the data, before you try and obtain it. ;)

I always focus on how I can store the data properly first, then I work out how I can obtain it. Once I have that as lean as possible, I move onto using that data with code.

510488270.png

SELECT
	contact.name AS 'contact_name'
 , `group`.name AS 'group_name'
FROM
	contact
LEFT JOIN
 `group` ON contact.id = `group`.contact_id

198082074.png

Link to comment
Share on other sites

  • 0

I'm trying to make an Addressbook, and one of my features is that you can add a group and add contacts to that group.

Now, my problem is that I'm not sure how do to this. I can make a table called `groups` and add static names like Work, Family, School, etc. But by doing so I don't know how each registered user can choose one of these groups and add it to the list of groups for their personal accounts.

Since i'm using InnoDB I'm referencing each table to the users table with a foreign key.

So I was thinking of doing something like this

contacts table (simplified example)

==========

id

user_id

group

name

address

and in the group column have the data stored like so: {Work, School, Friends}

and somehow do a query that will get each array item and output it so it looks like this kinda

Group

----------------

Work (11)

School (2)

Friends (6)

where the (x) means the number of members in that group.

In order to be able to count users that belong to a particular group, you're best to have another 'link' table where a user is assigned a group ID

You could have records assigning the same user to different groups (as this seems allowed in your requirements)

You'd count easily all the ones where the group ID is matching a desired one

You'd use GROUP BY to get counts for ALL the groups

You'd store the attributes of groups in the group table and only use the id for the link table

You'd use a carefully crafted limited JOIN (or performance will suffer with tens of thousands of records.. :-) ) when needing a mass of users along with their group ids

To avoid a double join, you could read the groups table into some array (will be a small one I guess) and then simply use the data from the array when outputting the user records WITH GROUP ATTRIBUTES, using the group id stored in the link table..

Hope it makes sense and helps..

Link to comment
Share on other sites

  • 0

You need to know how you're going to store the data, before you try and obtain it. ;)

I always focus on how I can store the data properly first, then I work out how I can obtain it. Once I have that as lean as possible, I move onto using that data with code.

510488270.png

SELECT
	contact.name AS 'contact_name'
 , `group`.name AS 'group_name'
FROM
	contact
LEFT JOIN
 `group` ON contact.id = `group`.contact_id

198082074.png

oh hmmm, ya I understand you. but can you explain where the group_contact comes into play?

Link to comment
Share on other sites

  • 0

The group_contact table maintains the links between groups and contacts.

So, lets say I created a group named 'Family'. This record in the group table looks like...

497860564.png

To add a contact to this group, I would add an entry to the group_contact table...

INSERT INTO group_contact (group_id, contact_id) VALUES (1, 3)

...where 3 is the contact.id for one of my Family members, and 1 is the group.id for my group.

Following?

Link to comment
Share on other sites

  • 0

Hmmm, ok just to see if I can get the logic to all this ;)

1. User adds new group

2. php-> INSERT INTO group VALUES 'group name', 'contact_id (from a session most likely)'

3. output -> SELECT `name` FROM `address_book` in a while loop

add contact to group

1. user selects what group they want to add or save to

2. php -> INSERT INTO `group_contact` VALUES ($group_id, $contact_id)

hmmm, well now i'm wondering. If i delete a group I would need to delete it from the group_contact table? Since they are links right?

Link to comment
Share on other sites

  • 0

Looks about right.

Yep, you would need to delete all the relevant 'links' from group_contact if the group was deleted. You could use the built in RDBMS triggers for this. :)

Link to comment
Share on other sites

  • 0

A relational database is just a database where you can define (either explicitly or just in the innerworkings of your mind) relationships between the tables). InnoDB is a type of table that lets you define and enforce these rules at the DB level (but comes at a significant overhead).

All that group_contact rows are saying is that a given contact belongs in a given group.

Link to comment
Share on other sites

  • 0

Hey, what Database program are you using to design you database?

I'm not familiar with the icons in your screen cap ;)

Looks a bit like MSSQL Server Management Studio to me, might be wrong...

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.