Node JS select from MySQL filter array column by array object

Question

I have a column whose contents can null, a single integer, or several integers separated by a comma. I want to search for specific rows by using my column to filter out unwanted data. Instead of a search string, I have an array that can have a single element or several, How can I work out on this? Am using Node JS + MySQL. The solution below only works when the column data is a single integer. How can I make it work when the column contains several integers too?

connection.query("SELECT * FROM `vw_tenants` WHERE units is not null AND property_code =? AND (floors_comunn IN (?);", [property_code, floors], (err, data) => {
connection.release();
if(err) {
    logger.error(err);
    result(null);
} else {
    result(data);
}

});

The "floors" variable can be [1] or have several elements ie [0,1,3,5,..n] The "floors_comunn" table column can contain null, single integer ie 3, or a group of integers separated by a comma ie 2,4,5

Kavinda Shehan Rajapakshe · Accepted Answer · 2021-08-24 22:58:29Z

3

Your question is not clear to me :( . But I can say one thing, you have to change the database design. It is not good practice to store multiple values in a single field. It should be atomic. This is the very first normal form (1NF) Normal forms

In DBMS there are many normal forms. It is always good practice to use at least upto 3rd normal form or BCNF.

edited Aug 24, 2021 at 22:58

answered Feb 1, 2021 at 7:05

Kavinda Shehan Rajapakshe

1848 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Albert Alberto Over a year ago

I was not aware of this but I had already changed the structure of the table. That was the only solution I could work with. Thanks, your information is helpful too.

user1592129 · Accepted Answer · 2021-02-02 14:30:26Z

0

MySQL also supports JSON Objects now. You can use JSON functions of mysql to search your columns for specific values.

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

answered Feb 2, 2021 at 14:30

user1592129

4952 gold badges7 silver badges19 bronze badges

Collectives™ on Stack Overflow

Node JS select from MySQL filter array column by array object

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related