R shiny problem: How to output the correct result without space between value and symbol-CodePudding

I have a quick question. The R code is as below. When I want to run this code and enter a in textinput.

"query_db <- paste("select name, Votes,city from zomato_rest where name like '%",input$names,"%' and Votes <=", input$slider_votes,";")"

The result always only shows ' a '. It won't show 'babb', 'accc' etc.

If I correct it as below and enter Roku, it shows no this data, but it exists in the dataset. query_db <- paste("select name, Votes,city from zomato_rest where name = '",input$names,"' and Votes <=", input$slider_votes,";")

If I correct it again, it shows the data. query_db <- paste("select name, Votes,city from zomato_rest where name ='Roku' and Votes <=", input$slider_votes,";")

Does anyone know the reason?

library(shiny)
library(shinydashboard)
library(leaflet)
library(DBI)
library(odbc)
library(DT)

ui <- textInput("names", "Pattern of Name", "Please enter a name"),
      actionButton("Go", "Get results!"),
      DT::dataTableOutput("mytable")
      )
      
server <- function(input, output,session) {

    query_db <- paste("select name, Votes,city from zomato_rest where name like '%",input$names,"%' and Votes <=", input$slider_votes,";")
    print(query_db)
    data_db <- dbGetQuery(db, query_db)

output$mytable = DT::renderDataTable({ data_db })

I want to see the result of code running in R same as the result running in SQL. But, it looks like it always has space between the value and symbol => '% a %', so 'cbac' won't show up, but ' a ' shows up.

I expect it can show the result like '%a%', so 'cbac' shows up, but ' a ' won't show up. I hope that I describe the situation clear enough.

CodePudding user response：

you can use paste0 instead of paste. This line should now work.

query_db <- paste0("select name, Votes,city from zomato_rest where name like '%",input$names,"%' and Votes <= ", input$slider_votes,";")

CodePudding user response：

Use this instead:

    query_db <- paste("select name, Votes,city from zomato_rest where name like ? and Votes <= ?")
    print(query_db)
    data_db <- dbGetQuery(db, query_db,
        params = list(paste0("%", inputs$names, "%"), input$slider_votes))

You may notice that query_db never changes: this is a good thing. First, it helps you standardize your queries; second, it helps the DBMS by allowing it to optimize the query and reuse it on repeated use.