0

i have three variables a,b,c (Actually more than 300 variables in my case)

t<-c(a,b,d)

a<-dbGetQuery(con, "SELECT * FROM a")
b<-dbGetQuery(con, "SELECT * FROM b")
d<-dbGetQuery(con, "SELECT * FROM d")

How can I make a loop to request data from MySQL in R? The existing question does not have the explanation on how to write it into the variable names. I need a,b,c in my environment.

tw123789
  • 55
  • 11
  • possible duplicate of [How to use a variable name in a SQL statement?](http://stackoverflow.com/questions/2182337/how-to-use-a-variable-name-in-a-sql-statement) – zx8754 Jul 23 '15 at 10:12
  • @zx8754 existing question cant solve my problem. – tw123789 Jul 23 '15 at 10:21
  • @zx8754 I can't connect to the test link, but I hope you can connect. `con2 <- dbConnect(MySQL(), user="a2909885_aawin", password="test1234", dbname="a2909885_aastock", host="mysql10.000webhost.com")` the (tables) variables are H0001, H0002, H0009 respectively – tw123789 Jul 23 '15 at 12:46

1 Answers1

1

Not tested, but something as below should work.

myTables <- c("a","b","c")

res <- lapply(myTables,
              function(myTable){
                sqlStatement <- paste("select * from",myTable)
                dbGetQuery(con, sqlStatement)
              })
names(res) <- myTables
zx8754
  • 52,746
  • 12
  • 114
  • 209
  • not work even no error warning issued. In addition, I found res stored the data... but I don't want this result... – tw123789 Jul 23 '15 at 10:54
  • Unfortunately we cannot test the code, as we do not have the database. Regarding `res` list output, would you rather prefer to have 300 variables, why? It is better to keep it as a list, so then we can access it as, `res$a` for results from table `a`. – zx8754 Jul 23 '15 at 11:04
  • I know I can access a from res$a. I just found "assign" but I dont know how to make it apply in my case...http://stackoverflow.com/questions/2679193/how-to-name-variables-on-the-fly-in-r – tw123789 Jul 23 '15 at 11:09