I am working on R shiny app. Please note that I am trying to load data from my mysql server. So I used help of RMySQL package.
In my ui.R file, I have this:
selectInput("pitchername","Pitcher Name:", choices = unique(pitcher_names$pitcher_name), selected = "Chan Ho Park"),
I have csv file which was loaded onto 'pitcher_names' and it gives the list of names. In drop down menu, I have list of names to choose from.
In my server.R file, I have this:
selected_data <- reactive({
query <- dbSendQuery(conn = con, statement = paste('SELECT * FROM pitch_velo WHERE pitcher_name = ', "\"",input$pitchername,"\"",';',sep=''))
pitch_data <- dbFetch(query,n=-1)
minyear <- input$year[1]
maxyear <- input$year[2]
velo <- pitch_data %>% group_by(pitcher_name, pitch_type, Year) %>% summarise(velocity = mean(start_speed)) %>% arrange(velocity) %>% dplyr::filter(pitcher_name %in% input$pitcher_name) %>% dplyr::arrange(pitcher_name) %>% dplyr::filter(Year >= minyear) %>% dplyr::filter(Year <= maxyear) %>% dplyr::filter(pitch_type %in% input$pitch_type)
print(velo)
})
After working on this for half day, I realized that only problem is that name of pitcher chosen from selectInput() is not being passed onto dbSendQuery function in server.R. What can I do to make this work?
I think you have a typo in :
filter(pitcher_name %in% input$pitcher_name)
shouldn't it be:
filter(pitcher_name %in% input$pitchername)
And actually, I don't see the point of filtering your result query since you already put the condition in the query.