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
Solved

multiple entries in one field

Posted on 2014-02-13
7
258 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
  • 4
  • 3
7 Comments
 
LVL 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Creating and Managing Databases with phpMyAdmin in cPanel.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

860 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