CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
Albert Alberto
Albert Alberto

Reputation: 950

Node JS select from MySQL filter array column by array object

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

Upvotes: 0

Views: 712

Answers (2)

Kavinda Shehan Rajapakshe
Kavinda Shehan Rajapakshe

Reputation: 174

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.

Upvotes: 3

user1592129
user1592129

Reputation: 493

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

Upvotes: 0

Related Questions