How to get value of selected choice in Rshiny reactive dropdown box?

Question

I have a reactive drop down box in Rshiny with the values for years- 2016 to 2019. After the user chooses the year, the year has to be passed onto an sql query with the year as the parameter.

So I tried the line given below (where unique_vales2 holds the year values)

 ---
 title: "Dashboard"
 output: 
 flexdashboard::flex_dashboard:
 orientation: rows
 vertical_layout: fill
 social: [ "twitter", "facebook", "menu"]
 runtime: shiny

 ```{r}
 mydb = dbConnect(MySQL(), user='xxx', password='xxx',dbname='xxx', 
 host='xxx')
 selectInput("year", "Choose year", choices = unique_values2) 
 num <- reactive(as.integer(input$year))
 rs=dbSendQuery(mydb,paste("select * from employees where Year=",num," group 
 by job;"))
 result=fetch(rs,n=-1)

 ```

But when I tried to use the num value in an sql query like given above,

It shows:

Error: cannot coerce the type 'closure' to vector of type 'character'.

Any way around this? Thanks in advance.

May you post your whole code so we can reproduce it, please? — LocoGris, Feb 18 '19 at 10:51

score 1 · Accepted Answer · answered Feb 19 '19 at 05:31

1

Assume the below data is in your mysql table employees as i do not have actual data.

dput(employees)
structure(list(id = 1:12, names = structure(1:12, .Label = c("aa", 
"bb", "cc", "dd", "ee", "ff", "gg", "hh", "ii", "jj", "kk", "ll"
), class = "factor"), year = c(2016, 2016, 2017, 2017, 2018, 
2018, 2016, 2018, 2017, 2019, 2019, 2019)), .Names = c("id", 
"names", "year"), row.names = c(NA, -12L), class = "data.frame")

Below code will help you for your requirement

library(shiny)
library(DBI)
library(pool)
pool <- dbPool(drv = RMySQL::MySQL(),dbname = "db_name",host = "localhost",username = "user_name",password = "password", port = 3306, unix.sock = "/var/run/mysqld/mysqld.sock")

ui <- fluidPage(
  uiOutput("years"),
  tableOutput("mytable")
)

server <- function(input, output, session) {
  output$years <- renderUI({
    unique_values2 <- c(2016:2019)
    selectInput("year", "select year", choices = unique_values2)
  })

results <- reactive({
  df <- dbGetQuery(pool, paste0("SELECT * FROM employees WHERE year = ", input$year ," ;"))
  return(df)
})
output$mytable <- renderTable(results())
}

shinyApp(ui, server)

So the table output will be changing depending on the year we select from drop down.

answered Feb 19 '19 at 05:31

msr_003

1,205
2
10
25

Thanks for the reply. This works well except for the sql query portion. In my code, I need to give "group by" feature after the parameter but in the code snippet you've given, 'group by' is missing. When I added 'group by ' part to your code like "SELECT * FROM employees WHERE year = ", input$year ," group by ;") it flags an error -'could not run statement, error in sql syntax' – pashen Feb 19 '19 at 06:23
Can you post the complete error message? I just tried `GROUP BY` without any errors. – msr_003 Feb 19 '19 at 06:48
The error message was - "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 'by id' at line 4" – pashen Feb 19 '19 at 07:17
I'm assuming [this](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) would help you to overcome the error. Try the solution from that link, then restart your MySQL. – msr_003 Feb 19 '19 at 07:28
That solved the issue. Thanks a ton! I have another follow up question though. Rendertable(results()) function prints the entire table. Is there anyway I can print a particular column and its values or rather use that column to plot a graph using plot_ly? Something like this plot_ly(x = ~result_table_name$columnname1, y = ~result_table_name$columnname2) – pashen Feb 19 '19 at 07:39
In this case i will add two extra drop down options with column-names to select columns for x-axis and y-axis along with a `plotlyOutput`. [Check](https://stackoverflow.com/questions/53164693/grouped-bar-plot-in-shiny-with-select-input/53222938#53222938) my answer to one of the previous similar requirement. If that doesn't help you i will try different solution. – msr_003 Feb 19 '19 at 08:50
Hey, I was able to do it without using extra dropdown boxes and making some modifications to your previous answer. This will also work if have another filter (similar to year) such as job description in a drop down box and if I added that to the sql query as another parameter right? – pashen Feb 19 '19 at 09:17
Yeah that will work. If you want to avoid multiple database calls, try to bring the entire table at a time as dataframe then do reactive operations on the dataframe. – msr_003 Feb 19 '19 at 09:47
Okay got it. Thanks a lot! Really glad you took the time out to help me with this :) – pashen Feb 19 '19 at 10:05

How to get value of selected choice in Rshiny reactive dropdown box?

1 Answers1