How can I delete selected rows in Shiny from MySQL

Question

I need to delete the selected rows from MySql Database when Click on delete button in my shinyapp. It must work for multiply selection the same as for single selection.

I use input$table_rows_selected, but it return the ID of row in dataframe in Shiny, not in MySQL. Thats why it doesn`t work.

My code is:

library(shiny)
library(DBI)

con <- dbConnect(RMySQL::MySQL(), dbname = "test", username = "root", password = "password", host = "host", port = 3306)

onStop(function() {
  dbDisconnect(con)
})


ui <- fluidPage(
  DT::dataTableOutput("tbl"),
  actionButton("del", "Delete user"),
)

server <- function(input, output, session) {
  sqlOutput <- reactive({
    sqlInput <- "select * from test"
    dbGetQuery(con, sqlInput)
  })

  output$tbl <- DT::renderDataTable(sqlOutput(),
    server = TRUE, rownames = FALSE 
  )


  observeEvent(input$del, {
    delFunction(input$tbl_rows_selected)
  })

  delFunction <- function(id) {
    query <- sprintf("DELETE FROM test WHERE (`idtest` = '%d');", id)
    dbSendQuery(con, query)
  }
}

shinyApp(ui, server)

relevant posts: https://stackoverflow.com/questions/55874019/delete-rows-from-sql-server-table-using-r-dbi-package/77019378#77019378, https://stackoverflow.com/questions/26373739/how-can-i-delete-records-from-an-odbc-database-based-on-a-dataframe-in-r — Aaron C, Aug 31 '23 at 22:18

score 1 · Accepted Answer · answered Feb 03 '20 at 18:11

I find a solution modifying this part of code:

  observeEvent(input$del, {
    delFunction(input$tbl_rows_selected)
  })

  delFunction <- function(id) {
    count <- id
    data <- sqlOutput()[count, 1]
    query <- sprintf("DELETE FROM test WHERE (`idtest` = '%d');", data)
    dbSendQuery(con, query)
  }

How can I delete selected rows in Shiny from MySQL

1 Answers1