Solved

multiple entries in one field

Posted on 2014-02-13
7
262 Views
Last Modified: 2014-02-14
Hi Experts

I created a table in mySQL. The purpose is to groups servers I want to add multiple servers to a group. How do I achieve this? See my table below:

CREATE TABLE servergroups_tbl(`servergrpid` VARCHAR(100), servergrp VARCHAR(100) NOT NULL, servername VARCHAR(100), servertype VARCHAR(50) NOT NULL, userid VARCHAR(100), PRIMARY KEY (`servergrpid`))ENGINE=InnoDB;

I want to be able to add multiple servers in the servername field, as these servers will belong to a specific group field in servergrp which contains the name of the server group.
What is the SQL syntax to achieve this.
0
Comment
Question by:jw124210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39857682
Normally, you don't.
You need to create another table, called servers_tbl or whatever works for you, with at least 3 fields:
id
servername
group_id

where group_id is the foreign key to the servergroups_tbl
You'll probably want to add servertype here too.

And delete the server details from the servergroups table.

After that, just join the tables when you need to pull data.

HTH,
Dan
0
 

Author Comment

by:jw124210
ID: 39857747
Thanks Dan

I am not quite clued up with SQL. I will try that. will let you know.
0
 

Author Comment

by:jw124210
ID: 39858855
Hi Dan

I am still new to SQL, this is what I intend to do. Insert into servergroups_tbl a list of servers from the server_tbl servername field. I have added the servers_tbl table and aded the foreign key.
How do I insert the data into the servergroup_tbl making use of foreign key for a specific group name. I want to able to retrieve servergroups and servers under that servergroup.
If possible an example would help on insert and select.

Here are my tables.
servergroups_tbl
==============
CREATE TABLE servergroups_tbl( id unasigned int(10) NOT NULL, `group_id` VARCHAR(10), servergrpname VARCHAR(100) NOT NULL, servername VARCHAR(100), userid VARCHAR(100), PRIMARY KEY (`group_id`))ENGINE=InnoDB;

servers_tbl
=========
CREATE TABLE servers_tbl(`id` VARCHAR(10) NOT NULL, servername VARCHAR(255)NOT NULL, servertype VARCHAR(255)NOT NULL, group_id INT(10), PRIMARY KEY (`id`), Foreign Key (group_id) REFERENCES servergroups_tbl(group_id));)ENGINE=InnoDB;
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 35

Accepted Solution

by:
Dan Craciun earned 65 total points
ID: 39859265
You need to normalize your data:
servergroups_tbl
`id` unsigned int(10) NOT NULL AUTO INCREMENT,
servergrpname VARCHAR(100) NOT NULL,
userid VARCHAR(100),
PRIMARY KEY (`id`)

servers_tbl
`id` unsigned int(10) NOT NULL AUTO INCREMENT,
servername VARCHAR(255) NOT NULL,
servertype VARCHAR(255) NOT NULL,
group_id INT(10),
PRIMARY KEY (`id`),
Foreign Key (group_id)

If a user is common for a whole group, then it should stay in the group table, otherwise you should put it in the servers table.

You will first need to insert servergroups data (you enumerate your server groups and your group users, if you kept the users in that table).
INSERT INTO servergroups_tbl (servergrpname, userid) VALUES ("group 1", "1")

Open in new window


Then you insert the server data in the server table:
INSERT INTO servers_tbl (servername, servertype, group_id) VALUES ("server1", "fileserver", "1")

Open in new window


If you want to retrieve all the servers inside a server group:
SELECT servergrpname, servername, servertype 
    FROM servergroups_tbl 
    JOIN servers_tbl ON servergroups_tbl.id = servers_tbl.group_id
    WHERE servergrpname = "group 1"

Open in new window


HTH,
Dan
0
 

Author Comment

by:jw124210
ID: 39859937
Hey Dan!! Thanks, seems it's working, just need to run a few more tests and I will close this one.
0
 

Author Comment

by:jw124210
ID: 39860073
Thanks Dan! much appreciated, all working fine. I will apply the same method for users and roles.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39860087
Glad I could help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question