CODEWITHSUNDEEP
mysqlrshiny
Will
Will

Reputation: 91

I want to create a dynamic dropdown menu for my Shiny Dashboard using R?

I have a MySQL database that contains multiple tables. Now I want to create a dropdown menu in Shiny dashboard that automatically adds values based on the unique values of each column of the tables.

My current code looks like this

ui <- fluidPage(
  numericInput("nrows", "Enter the number of rows to display:", 5),
  tableOutput("tbl")
)

server <- function(input, output, session) {
    output$tbl <- renderTable({
        conn <- dbConnect(
      drv = RMySQL::MySQL(),
      dbname = "apilogs",
      host = "localhost",
      username = "root",
      password = "root")
        on.exit(dbDisconnect(conn), add = TRUE)
        dbGetQuery(conn, paste0("SELECT * FROM logs where key = 'agc' LIMIT ", input$nrows, ";"))

    })
}

Now for my shiny dashboard I want to create a dropdown menu based on the values of the columns of logs table.

  dashboardSidebar(
        selectInput("Filter", "Filter:",
                  choices = c())
  )

Now here in choices I want to get the choices dynamically depending on the table columns. How can I do this.

Upvotes: 1

Views: 1851

Answers (1)

Olga Semenkina
Olga Semenkina

Reputation: 94

I think, you should create unique list of values like this:

unique_values <- sort(unique(table_name$column_name))

Then you can use it for choices:

selectInput("filter", "Filter:", choices = unique_values)

For dynamic dropdownMenu you can use this guide where the main idea is that on UI part you create just this:

ui <- dashboardPage(
  dashboardHeader(title = "Dropdowns 2.0",
    dropdownMenuOutput("dropdownMenuDynamic")
  )
)

Also you need to do something like this:

size <- length(output$filter)
tasks <- vector("list", size)
for(i in 1:length(tasks)) { 
  tasks[[i]] <- list(
    value = 10,
    color = "yellow",
    text = output$filter[[i]]
  ) 
}

And the last part is to create dropdownMenuDynamicon server side:

output$dropdownMenuDynamic <- renderMenu({
    items <- lapply(tasks, function(el) {
      taskItem(value = el$value, color = el$color, text = el$text)
    })
    dropdownMenu(
      type = "tasks", badgeStatus = "danger",
      .list = items
    )
  })

Upvotes: 1

Related Questions