delete selected row of table from database in shiny app

Question

I want to delete the selected row from MySql Database when Click on delete button in my shinyapp. The codes of ui.r and server.r are as following: It is connected to MySQl and I can save data to mySQL but I cannot delete and Update the table. 1: connection to database

 # Define the fields we want to save from the form
     fields <- c("name", "used_shiny", "r_num_years")
    #connect to MySQL

    options(mysql = list(
      "host" = "127.0.0.1",
      "port" = 3306,
      "user" = "root",
      "password" = ""
      ))
    databaseName <- "myshinydatabase"
     table <- "responses"
     saveData <- function(data) {
       # Connect to the database
       db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
               port = options()$mysql$port, user = options()$mysql$user, 
              password = options()$mysql$password)

Function of delete Data :

deleteData <- function() {
    # Connect to the database
     db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
                         port = options()$mysql$port, user = options()$mysql$user, 
                         password = options()$mysql$password)
      # Construct the deleting query
      query <- sprintf("DELETE selctedrow FROM %s", table)
     # Submit the fetch query and disconnect
      data <- dbGetQuery(db, query)
     dbDisconnect(db)
      data
  }

When Delete button is clicked, delete the selected row of data

observeEvent(input$delete.button, {
    deleteData (formData())
  })

The code for ui.r

ui <- fluidPage(
      #use shiny js to disable the ID field
      shinyjs::useShinyjs(),
      #DT::dataTableOutput("responses"),
      shinyjs::disabled(textInput("id", "Id", "0")),

      textInput("name", "Name", ""),
      checkboxInput("used_shiny", "I've built a Shiny app in R before", FALSE),
      sliderInput("r_num_years", "Number of years using R", 0, 25, 2, ticks = FALSE),

      actionButton("submit", "Submit",icon = icon("plus-circle"),class = "btn-primary"),
      # Delete button 
      actionButton(inputId = "delete", label = "Delete", icon = icon("minus-circle"),class = "btn-primary"),
      #NEW button
      actionButton("new", "Reset",icon = icon("refresh"),class = "btn-primary")
    ))
    ,box(
      title = "KPIs", status = "primary", solidHeader = TRUE,
      collapsible = TRUE,
     DT::dataTableOutput("responses"), tags$hr()

    )),

The Error message that I get:

Listening on http://127.0.0.1:5894
Warning: Unhandled error in observer: unused argument (formData())
observeEvent(input$delete.button)

Answer 1

Simply pass the current form's id parameter into the function. Then concatenate the id into the WHERE clause of the DELETE query. By the way the DELETE statement does not take any field as it removes the entire row and not one particular column.

UI side

ui <- fluidPage(
  #use shiny js to disable the ID field
  shinyjs::useShinyjs(),
  #DT::dataTableOutput("responses"),
  shinyjs::disabled(textInput("id", "Id", "0")),

  selectInput("ID", 
              label = "Choose ID from list",
              choices = c(1:50), 
              selected = "1"),   

  textOutput("successtext"),

  textInput("name", "Name", ""),
  checkboxInput("used_shiny", "I've built a Shiny app in R before", FALSE),
  sliderInput("r_num_years", "Number of years using R", 0, 25, 2, ticks = FALSE),

  actionButton("submit", "Submit",icon = icon("plus-circle"),class = "btn-primary"),
  # Delete button 
  actionButton(inputId = "delete", label = "Delete", icon = icon("minus-circle"),class = "btn-primary"),
  #NEW button
  actionButton("new", "Reset",icon = icon("refresh"),class = "btn-primary")
))
,box(
  title = "KPIs", status = "primary", solidHeader = TRUE,
  collapsible = TRUE,
 DT::dataTableOutput("responses"), tags$hr()

)),

Server side

observeEvent(input$delete.button, {
     deleteData(input$ID)         
})

deleteData <- function(formid) {
     # Connect to the database
     db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
                         port = options()$mysql$port, user = options()$mysql$user, 
                         password = options()$mysql$password)

     # Construct the deleting query
     query <- sprintf("DELETE FROM %s WHERE id = %d", table, formid)

     # Submit the fetch query and disconnect
     rs <- try(dbSendQuery(db, query))
     if(inherits(rs, ErrorClass)){ 
               output$successtext <- renderText({paste0("Failed to remove ID ", 
                          input$ID, " from database table. Please contact administrator.")})
               } 
     } else {
               output$successtext <- renderText({paste0("Successfully removed ID ", 
                          input$ID, " from database table!")})
               } 
     }
     dbDisconnect(db)
}

Also, be sure to sanitize and validate the form data especially id field. DELETE is a very powerful function and sql injection is always an issue when an application receives user inputted data.