将多个选择从 selectizeInput 传递给 MySQL 查询 [英] Passing multiple selections from selectizeInput to MySQL query
本文介绍了将多个选择从 selectizeInput 传递给 MySQL 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将 selectizeInput 的多个值传递给 MySQL 查询.
I am trying to pass multiple values of a selectizeInput to a MySQL query.
问题是在一个闪亮的应用程序中,这种操作的正确语法是什么?
The question is what is the right syntax for this kind of operation in a shiny app ?
我尝试过的并且正在使用一个值
What I tried and is working with one value
library(shiny)
library(DBI)
library(RMySQL)
server <- shinyServer(function(input, output, session) {
con <- dbConnect(MySQL(), user='user', port = 3306,
password='pwd', dbname='db', host='host' )
on.exit(dbDisconnect(con), add = TRUE)
output$textview <- renderUI({
con <- dbConnect(MySQL(), user='user', port = 3306, password='pwd',
dbname='db', host='host' )
on.exit(dbDisconnect(con), add = TRUE)
text <- reactive({
dbGetQuery(con, statement =
paste0(" SELECT author, title, publicationDate, FROM publications
WHERE publications.year LIKE %'",input$year,"'% ")
)
})
text <-text()
HTML(text)
})
session$onSessionEnded(function() { dbDisconnect(con) })
})
ui_panel <-
tabPanel("Multi-Select Input Test",
sidebarLayout(
sidebarPanel(
selectizeInput('year', 'Select Year of publication:', choices =
publications.year, multiple = TRUE options = list(maxOptions = 5)
),
br(),
submitButton("Update Text View"),
br()
),
mainPanel(
tabsetPanel(tabPanel("Text",htmlOutput("textview"))
)
)
))
ui <- shinyUI(navbarPage(" ",ui_panel))
runApp(list(ui=ui,server=server))
MySQL 命令中允许我从 selectizeInput (input$year) 传递多个值的正确语法是什么?我尝试使用 IN 而不是 LIKE 如下,但它没有用
What is the correct syntax in the MySQL command that will allow me to pass more than one value from selectizeInput (input$year)? I tried using IN instead of LIKE as below but it did not work
text <- reactive({
dbGetQuery(con, statement =
paste0(" SELECT author, title, publicationDate, FROM
publications WHERE publications.year IN %'",input$year,"'% ")
)
})
推荐答案
你需要构造一个类似这样的SQL:
You need to construct a SQL similar to this:
SELECT * FROM publications WHERE year IN (2016, 2017)
这应该会产生:
text <- reactive({
year_selected <- paste(input$year, collapse = ',')
sql = paste("SELECT * FROM publications WHERE year IN (",year_selected,")")
dbGetQuery(con, statement = sql)
})
一个最小的 Shiny 应用程序:
library(shiny)
ui <- fluidPage(
sidebarLayout(
sidebarPanel(
selectizeInput('year', 'Select Year of publication:',
choices = c(2017L, 2018L),
multiple = TRUE,
options = list(maxOptions = 5)
)
),
mainPanel(
verbatimTextOutput('text')
)
)
)
server <- function(input, output) {
output$text <- renderText({
library(glue)
year_selected <- paste(input$year, collapse = ',')
glue("SELECT * FROM publications WHERE year IN ({year_selected})")
})
}
shinyApp(ui = ui, server = server)
这篇关于将多个选择从 selectizeInput 传递给 MySQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文