MySQL基于多个用户输入的选择查询 [英] Mysql select query based on multiple user inputs

问题描述

当前我的表格具有以下字段

Currently my table have the following fields

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

我想基于用户输入执行过滤器，即如果用户输入存在于特定字段中，请基于该输入执行where sql

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

最小代码

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);
    });

但是当存在用户输入中的任何两个或用户输入包含所有字段时，我感到困惑在哪里可以在SQL查询中放置AND

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

推荐答案

编辑#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'
}

请注意正确的报价.另外，更改变量名称也许是个好主意，以使它们不与表列名称相对应.顺便提一句. subject 和 medium 是保留名称，最好放在反引号中.

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.

截屏:

对不起，我听错了你的问题.使用JSON_EXTRACT从json列 json_column 中提取数据，假设您具有一维json数据结构，例如

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"
}

您明白了.

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'
}

编辑#2:添加了phpMyAdmin的屏幕截图:

Edit #2: Added a screenshot from phpMyAdmin:

这篇关于MySQL基于多个用户输入的选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！