CODEWITHSUNDEEP
mysql
D_R
D_R

Reputation: 4962

Searching array element in Database

I want to create a table called user_ports which will contain all the ports the user has opened in the database.

Instead of calling each colum as "port_80", "port_100"

I was wondering if its possible to enter it to a colum which will be called opened_ports as an array.

But if I would like to get all the users with port 80 opened, how can I do that?

Upvotes: 0

Views: 60

Answers (1)

Exander
Exander

Reputation: 882

As far as I can understand, you just need to have 2 columns in the user_ports table: user_id which will reference the appropriate user, and opened_port, which will describe the specific opened port. Then you should probably declare the (user_id, opened_port) pair as a primary key. With such table structure, you could select the ids of users that opened 80 port by the following query:

SELECT user_id FROM user_ports WHERE opened_port = 80;

You COULD store all of the ports opened by user as CSV in a single record, but this will:

  1. require additional code that will deal with such trickery;
  2. complicate and slow down your database queries;
  3. denormalize your database (which should avoided).

Upvotes: 1

Related Questions