Passing multiple selections from selectizeInput to MySQL query

Question

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

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,"'% ")
             )
               })

Imran Kocabiyik · Accepted Answer · 2018-08-31 17:28:03Z

1

You need to construct a SQL similar to this:

SELECT * FROM publications WHERE year IN (2016, 2017)

This should generate that:

text <- reactive({
  year_selected <- paste(input$year, collapse = ',')
  sql = paste("SELECT * FROM publications WHERE year IN (",year_selected,")")
  dbGetQuery(con, statement = sql)
})

A minimal Shiny App:

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)

answered Aug 31, 2018 at 17:28

Imran Kocabiyik

4395 silver badges16 bronze badges

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

4 Comments

R noob Over a year ago

Thank you, Imran for your answer. The basic shiny app works fine. Unfortunately, when working with the reactive variable text <- reactive({ year_selected <- paste(input$year, collapse = ',') sql = paste("SELECT * FROM publications WHERE year IN (",year_selected,")") dbGetQuery(con, statement = sql) }) it throws an error showing it is not the correct MySQL syntax. Warning: Error in .local: could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Imran Kocabiyik Over a year ago

That's a SQL issue. In your question, you said it worked with one value. Can you share the working SQL?

R noob Over a year ago

Here is the reactive variable that worked with the LIKE syntax for MySQL, I am using MySQL connection text <- reactive({ dbGetQuery(con, statement = paste0(" SELECT author, title, publicationDate, FROM publications WHERE publications.year LIKE %'",input$year,"'% ") ) }) As I mentioned I did not manage to even pass one value to the IN query.

Imran Kocabiyik Over a year ago

Could year column be VARCHAR instead of INTEGER? Then you may need to construct something similar to WHERE year IN ('2018', '2019'). If so, try year_selected <- stringr::str_c(stringr::str_c("'",input$year,"'"), collapse = ',')

Collectives™ on Stack Overflow

Passing multiple selections from selectizeInput to MySQL query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related