Combining Input from Shiny Widgets and MySQL Queries with Less Code

===========================================================

As a developer, we’ve all been there - staring at a long piece of code that’s become unwieldy due to the need for repetitive IF-ELSE statements. In this post, we’ll explore how to simplify your MySQL queries with input from shiny widgets using a more efficient and readable approach.

Background

For those unfamiliar with shiny or MySQL, let’s quickly cover the basics:

Shiny is an R package that allows you to create interactive web applications.
MySQL is a relational database management system used for storing and managing data.

In this example, we’ll focus on using MySQL queries to retrieve data from two tables: quoteauthors and quotes. We’ll use input from various shiny widgets, such as textInput, radioButtons, and selectInput, to filter our query results.

Step 1: Building the Query String

Instead of writing long IF-ELSE statements to handle different combinations of input, we can build the query string step-by-step. This approach allows us to create a single, reusable function that generates the query based on user input.

Let’s start by defining our database connection and the necessary variables:

# Load required libraries
library(shiny)
library(RMySQL)

# Database connection settings
user <- "XXXXXXXXXXX"
password <- "XXXXXXXXXXX"
host <- "XXXXXXXXXXX"
port <- 3306
dbname <- "XXXXXXXXXXX"

# Establish database connection
con <- dbConnect(MySQL(), 
                 user = user, 
                 password = password, 
                 host = host, 
                 port = port, 
                 dbname = dbname)

Next, we’ll create a function that generates the query string based on user input:

build_query_string <- function(quote_search_1, quote_search_2, author_choice) {
  # Initialize query string
  query_string <- ""

  # Add search term 1 to query string if not empty
  if (nchar(quote_search_1) > 0) {
    query_string <- paste0(query_string, "%", quote_search_1, "%")
  }

  # Add search term 2 to query string if not empty
  if (nchar(quote_search_2) > 0) {
    query_string <- paste0(query_string, " AND %", quote_search_2, "%")
  }

  # Check if author choice is selected
  if (!is.null(author_choice)) {
    query_string <- paste0("AND authorID LIKE '%", author_choice, "%'")
  }

  return(query_string)
}

Step 2: Executing the Query

With our query string built, we can now execute it against the database. We’ll use the dbSendQuery function to send our query and retrieve the results.

execute_query <- function(con, query_string) {
  # Send query to database
  dbSendQuery(con, paste0("SELECT q.quote, a.FirstName, a.LastName FROM quotes q JOIN quoteauthors a ON (q.authorID = a.authorID WHERE ", query_string)))

  # Retrieve results
  return(dbGetQuery(con))
}

Step 3: Combining Input and Query Results

Now that we have our query executed, let’s combine it with user input. We’ll use the renderUI function to create an interactive interface that allows users to select their desired search criteria.

server <- function(input, output) {
  # Initialize reactive value
  quotedetails <- eventReactive(input$build_query, build_query_string(
    quote_search_1 = input$quoteSearch1,
    quote_search_2 = input$quoteSearch2,
    author_choice = input$authorchoice
  ))

  # Execute query using reactive value
  executed_query <- quotedetails()
  results <- execute_query(con, executed_query)

  # Render UI output
  output$quotesearchdetails <- renderUI({
    if (nrow(results) > 0) {
      uiOutput("results_table")
    } else {
      "No search results found."
    }
  })

}

# Define server-side reactive value
build_query <- eventReactive(input$build_query, {
  build_query_string(
    quote_search_1 = input$quoteSearch1,
    quote_search_2 = input$quoteSearch2,
    author_choice = input$authorchoice
  )
})

Step 4: Creating the Shiny App

Finally, let’s create our shiny app using the shinyApp function:

# Define UI for application
ui <- fluidPage(
  # Create sidebar menu
  dashboardSidebar(sidebarMenu(menuItem("QUOTE Search", tabName = "Tabs"))),

  # Create main content area
  dashboardBody(dashboardTabset(tabPanel("Search Results", 
                                             fluidRow(column(width=12, 
                                                               textInput("quoteSearch1", "Search Term 1 ", "", placeholder = "Type search term"), 
                                                                 radioButtons("combi", "Logical Operator to Combine Terms:",
                                                                                      c("AND" = "AND",
                                                                                       "OR" = "OR"), inline = TRUE), 
                                                                 selectInput("authorchoice", "Select AUTHOR", selected = NULL, multiple = T, choices=c('Albert','Stephen','Isaac'))),
                                    column(width=12, textInput("quoteSearch2", "Search Term 2 ", "", placeholder = "Type search term")),
                                    submitButton("Search")))))

)

# Create server-side application
shinyApp(ui = ui, server = server)

Conclusion

In this post, we explored how to combine input from shiny widgets and MySQL queries using a more efficient and readable approach. By building the query string step-by-step and executing it against the database, we were able to simplify our code and reduce repetition.

This technique can be applied to various scenarios where you’re working with interactive web applications and databases. Remember to always prioritize readability and maintainability when writing your code!

References

Shiny Documentation: https://shiny.rstudio.com/
RMySQL Documentation: https://cran.r-project.org/package=RMySQL
Hugo Documentation: https://gohugo.io/

Last modified on 2024-03-15