将selectizeinput中的多个选择传递到mysql查询

guykilcj 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(315)

我试图将selectizeinput的多个值传递给mysql查询。
问题是，在闪亮的应用程序中，这种操作的正确语法是什么？
我所尝试的和正在使用的只有一个值

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）传递多个值的正确语法是什么？我试着用在而不是像下面这样，但它没有工作

text <- reactive({
                dbGetQuery(con, statement = 
                paste0(" SELECT author,  title, publicationDate,  FROM 
                publications WHERE publications.year IN %'",input$year,"'% ")
             )
               })

mysql r shiny shiny-reactivity

来源：https://stackoverflow.com/questions/52090142/passing-multiple-selections-from-selectizeinput-to-mysql-query

1条答案

按热度按时间

fcwjkofz1#

您需要构造一个类似以下内容的sql：

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

一款极简闪亮的应用程序：

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)

赞(0）回复(0）举报 2021-06-20

我来回答

将selectizeinput中的多个选择传递到mysql查询

1条答案

相关问题

热门标签

最新问答