CODEWITHSUNDEEP
mysqlnode.js
Mr Boolean
Mr Boolean

Reputation: 103

How to search MySQL database with an array of strings?

This is for a Discord Bot with NodeJS. So I have an array called args which contains the arguments the user passed when the command was called. What I want to do is search the database under the column 'Symptoms' and return those rows back. But I only want the ones that contain the words in the array. For example, so I send the command " Pain, Sore throat" the bot will then turn that into an array. I then want it to search the database with anything that contains those words but it doesn't have to be in that order, so like the row could say "Sore Throat, Headache, Pain" and it would still select that because it at least contains those two arguments.

I have tried the following solutions:

SELECT * FROM diagnose WHERE Symptoms IN (${args})

But that only returns a row if it only has one word in it (that word being cough and nothing else, if there was another word it wouldn't show).

SELECT * FROM diagnose WHERE Symptoms LIKE '%${args}%'

Same result as above.

My Code:

if (!message.content.startsWith(prefix) || message.author.bot) return;
    const args = message.content.slice(prefix.length).split(' ');
    const command = args.shift().toLowerCase();

if(command === `symptoms`){
        if(!args.length){
            return message.channel.send("You didn't provide me any symptoms.");
        }
        var query = connection.query(`SELECT * FROM diagnose WHERE Symptoms LIKE '${args}'`);
        console.log(args1);
        query.on('error', function(err) {
            throw err;
        });
        query.on('fields', function(fields) {
            //console.log(fields);
        });
        query.on('result', function(row) {
            //console.log(row);
            message.channel.send(row.Outcome);
        });
    }

Upvotes: 1

Views: 160

Answers (1)

olivecoder
olivecoder

Reputation: 2914

The simplest way, but not the most efficient, to achieve that is:

SELECT *
FROM diagnose
WHERE 'pain' IN Symptoms
  AND 'sore' IN Symptoms
  AND 'throath' IN Symptoms

An efficient way would be to create another table with the words in the Symptoms column pointing to diagnose. Let's say we have already created and populated another table like diagnoseWords, then you could do:

SELECT DISTINCT *  
FROM diagnose d
INNER JOIN diagnoseWords w1 ON d.id=w1.id AND w1.word='pain' 
INNER JOIN diagnoseWords w2 ON d.id=w2.id AND w2.word='sore' 
INNER JOIN diagnoseWords w3 ON d.id=w3.id AND w3.word='throat'

Of course that would require indexes and it is trading off space for time. Other databases which implement the INTERSECT operator, like postgres and oracle, would allow you to do:

SELECT * FROM diagnose d 
 INNER JOIN  diagnoseWords w ON d.id=w.id AND w.word='sore'
INTERSECT
SELECT * FROM diagnose d 
 INNER JOIN  diagnoseWords w ON d.id=w.id AND w.word='throat'

Another approach that seems like would be helpful for your case would be the mysql natural language search or simply a boolean text search. Both approaches require a FULLTEXT index to work.

You could yet provide a "more results" feature using full-text searches with query expansion

Upvotes: 1

Related Questions