Passing values from table to selectInput Widget

shiny
dt

#1

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


#2

I found a solution. Just getting the data from the database in a data famework. like this:

cSS <- dbConnect(RMySQL::MySQL(), user="root", password="something", host="a.a.a.a", dbname="shipments")
qSS <- "SELECT * FROM samples WHERE 1;"
lSS <- dbSendQuery(cSS, qSS)
DF <- fetch(lSS, n=-1)