Hi there,
I would like to query a mySQL DB get the results into a table and then pass one of the columns to a Shiny selectInput widget.
I have obtained the table data and I have it into a DT table, however I'm not able to display the values of that column into a selectInput. Is that something possible to do? I attach you my code:
library(shiny)
library(DBI)
library(pool)
library(magrittr)
library(DT)
ui <- fluidPage(
titlePanel("shipments"),
# Create a new row for the table.
fluidRow(
column(4,
selectInput('id','Id', names ( as.list( DT::dataTableOutput("showList") ) ) ,selected = NULL))
),
# submitButton( text = "Submit", icon = NULL),
actionButton("action", label = "Action",selected = NULL),
mainPanel(
DT::dataTableOutput("table")
)
)
server <- function(input, output) {
statement <-( reactive({
if( input$action ){
#if (is.null(input$action)) {
isolate(sprintf("SELECT * FROM samplesSent WHERE 1;"))
}else{
isolate(sprintf("SELECT * FROM samplesSent WHERE 1;"
))
}
}) )
output$table <- DT::renderDataTable(
DT::datatable({
query <- sqlInterpolate(pool, ("SELECT * FROM samplesSent WHERE 1;") )
dbGetQuery(pool, query)
})
)
output$showList <- DT::renderDataTable({ DT::datatable ( ({
showList <- sqlInterpolate (pool, "SELECT distinct plateCol FROM samplesSent WHERE 1;" )
dbGetQuery (pool, showList)
}), selection = list(target = 'plateCol')
)
})
}
pool <- dbPool (
drv = RMySQL::MySQL(),
dbname = "shipments",
host = "a.a.a.a",
username = "root",
password = "something"
)
shinyApp(ui=ui, server=server)
regards,
David