CODEWITHSUNDEEP
rshiny
Apricot
Apricot

Reputation: 3021

Fetching multiple rows from MYSQL inside a Shiny application

I have a shiny application which is connected to MySQL database. For some of the queries I am connecting to MYSQL using RMySQL library. My shiny application has control widgets that populates values from MYSQL. One of the widgets is selectizeInput which renders the unique names of all brands. Using this widgets output as input in R shiny dashboard returns error. The following are the codes:

SI <- c("HYUNDAI CRETA", "HYUNDAI VERNA"). The object SI is actually a Selectize Input. 

mydb <- dbConnect(MySQL(), user = "root", password = "1234", dbname = "db1", host = "localhost")
a <- paste0("select * from autotable where SUBBRAND like ",SI) 
dbGetQuery(mydb,a)
on.exit(dbDisconnect(mydb))

The output of a is a character vector with two lines as follows:

[1] "select * from tabletable where SUBBRAND like HYUNDAI CRETA"
[2] "select * from tabletable where SUBBRAND like HYUNDAI VERNA"

instead I want to pass this as a single query into mysql which is as follows:

[1] "select * from tabletable where SUBBRAND like (HYUNDAI CRETA, HYUNDAI VERNA")

The above code helps me to fetch all the records from MYSQL where subbrand is like Hyundai Creta or Hyundai Verna.

Upvotes: 0

Views: 110

Answers (1)

Pork Chop
Pork Chop

Reputation: 29407

Is this what you want?

library(stringr)
SI <- c("HYUNDAI CRETA", "HYUNDAI VERNA")
SI <- str_c(SI, collapse = ",")

a <- paste0("select * from autotable where SUBBRAND like (",SI,")") 
a

[1] "select * from autotable where SUBBRAND like (HYUNDAI CRETA,HYUNDAI VERNA)"

EDIT: To suit your database requirements with double quotes

library(stringr)
SI <- c("HYUNDAI CRETA", "HYUNDAI VERNA")
SI <- str_c(SI, collapse = ",")
SI <- sub(",",'","',SI)
a <- noquote(paste0('select * from autotable where SUBBRAND like ("',SI,'")') )
a

[1] select * from autotable where SUBBRAND like ("HYUNDAI CRETA","HYUNDAI VERNA")

Upvotes: 1

Related Questions