Mysql select query based on multiple user inputs

Question

Currently my table have the following fields

id,medium(json) ,material(json),subject,category

I would like to perform a filter based on user inputs that is if user input is present on particular field perform a where sql based on that input

minimal code

var sql = 'SELECT * FROM table';

    //category is present in userinput 
    if(category){
      sql+='WHERE category=category';   
    }
    //subject is present in userinput
    if(subject){
      sql+='WHERE subject=subject'
    }
    if(material){
       sql+='WHERE material=material'
     }
   if(medium){
      sql+='WHERE medium=medium'
    }

    db.query(sql,function(error,result){
         console.log(result);
    });

But i confused where can i put AND in sql query when any two of the user input is present or user input contains all the fields

"Minimal code" : well, there's no code to support the php and mysql tags, which makes this question already unclear. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 26, 2017 at 12:36
@Fred-ii- Hover over the express tag. Its a javascript framework over node — RiggsFolly
– RiggsFolly, Commented Apr 26, 2017 at 12:39
There is more to do here than you expect. I see that the material and medium columns are JSON. So there is a whole bunch of extra stuff needed just for that column to be used in a search criteria — RiggsFolly
– RiggsFolly, Commented Apr 26, 2017 at 12:41
I guess my answer won't work, I apparently skipped the json column thing... — Robin K
– Robin K, Commented Apr 26, 2017 at 12:50

Filipe Martins · Accepted Answer · 2017-04-26 12:41:16Z

1

Try this:

    var sql = 'SELECT * FROM table';
    var where = ' WHERE';

    //category is present in userinput 
    if(category){
      sql += where + ' category=category';   
      where = ' and';
    }

    //subject is present in userinput
    if(subject){
      sql += where + ' subject=subject';
      where = ' and';
    }

    if(material){
       sql += where + ' material=material';
       where = ' and';
     }

    if(medium){
      sql += where + ' medium=medium';
    }

    db.query(sql,function(error,result){
         console.log(result);
    });

answered Apr 26, 2017 at 12:41

Filipe Martins

60810 silver badges23 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:02:36Z

1

Try below logic for your query get reference from here

 if(category){
              sql[] ='category=category';   
            }

            if(subject){
              sql[] ='subject=subject'
            }
            if(material){
               sql[] ='material=material'
             }
           if(medium){
              sql[] ='medium=medium'
            }
    var sql1 = 'SELECT * FROM table';

    if (index < sql.length) {
        sql1 += ' WHERE ' + sql.join(' AND ');
    }

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 26, 2017 at 12:53

Ahmed Ginani

6,6452 gold badges17 silver badges33 bronze badges

Comments

Robin K · Accepted Answer · 2017-04-27 10:43:49Z

1

Edit #3:

Now, I got it right (I think :>).

var sql = 'SELECT * FROM table WHERE 1';

//category is present in userinput 
if(category) {
  sql+=' AND category = category';   
}
//subject is present in userinput
if(subject) {
  sql+=' AND subject = subject'
}
if(material) {
  sql+=' AND JSON_SEARCH(material, "one", material) IS NOT NULL'
}
if(medium) {
  sql+=' AND JSON_SEARCH(medium, "one", medium) IS NOT NULL'
}

Care about correct quotes, please. Also, maybe it's a good idea to change the variable names so they do NOT correspond with the table column names. Btw. subject and medium are reserved name, better put them in backticks.

Screenshot:

Edit:

sorry I misread your question. Use JSON_EXTRACT to extract data from the json column json_column, suppose you have a one-dimensional json data structure like

{
  "category": "Test Category"
}

You get the idea.

var sql = 'SELECT * FROM table WHERE 1';

//category is present in userinput 
if(category) {
  sql+=' AND category = category';   
}
//subject is present in userinput
if(subject) {
  sql+=' AND subject = subject'
}
if(material) {
  sql+=' AND JSON_EXTRACT(material, "$.json_column") = material'
}
if(medium) {
  sql+=' AND JSON_EXTRACT(medium, "$.json_column") = medium'
}

Edit #2: Added a screenshot from phpMyAdmin:

edited Apr 27, 2017 at 10:43

answered Apr 26, 2017 at 12:43

Robin K

4476 silver badges17 bronze badges

2 Comments

Jabaa Over a year ago

Thanks for the update but i am stroing json as plain not in object represenation eg:instead of {"material":"Plastic"} i am using this ["plastic","paper"]

Robin K Over a year ago

Okay, I will adapt my answer.

Nerea · Accepted Answer · 2017-04-26 12:50:32Z

0

var sql = 'SELECT * FROM table';
var $aux = [];

//category is present in userinput 
if(category){
  $aux[] ='category=category';   
}
//subject is present in userinput
if(subject){
  $aux[]='subject=subject'
}
if(material){
   $aux[]='material=material'
 }
if(medium){
  $aux[]='medium=medium'
}

$wheres = implode(' and ', $aux);

if($wheres)
    $sql+= ' WHERE '.$wheres;

db.query(sql,function(error,result){
     console.log(result);
});

answered Apr 26, 2017 at 12:50

Nerea

2,1572 gold badges16 silver badges14 bronze badges

Collectives™ on Stack Overflow

Mysql select query based on multiple user inputs

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related