title: Add MySQL query results to R dataframe
body: <p>I want to convert a MySQL query from a python script to an analogous query in R. The python uses a loop structure to search for specific values using genomic coordinates:</p>
  
  <pre><code>SQL = """SELECT value FROM %s FORCE INDEX (chrs) FORCE INDEX (sites)
                          WHERE `chrom` = %d AND `site` = %d""" % (Table, Chr, Start)
  cur.execute(SQL)
  </code></pre>
  
  <p>In R the chromosomes and sites are in a dataframe and for every row in the dataframe I would like to extract a single value and add it to a new column in the dataframe</p>
  
  <p>So my current dataframe has a similar structure to the following:</p>
  
  <pre><code>df &lt;- data.frame("Chr"=c(1,1,3,5,5), "Site"=c(100, 200, 400, 100, 300))
  </code></pre>
  
  <p>The amended dataframe should have an additional column with values from the database (at corresponding genomic coordinates. The structure should be similar to:</p>
  
  <pre><code>df &lt;- data.frame("Chr"=c(1,1,3,5,5), "Site"=c(100, 200, 400, 100, 300), "Value"=c(1.5, 0, 5, 60, 100)
  </code></pre>
  
  <p>So far I connected to the database using:</p>
  
  <pre><code>con &lt;- dbConnect(MySQL(),
                   user="root", password="",
                   dbname="MyDataBase")
  </code></pre>
  
  <p>Rather than loop over each row in my dataframe, I would like to use something that would add the corresponding value to a new column in the existing dataframe. </p>
  
  <p><strong>Update with working solution based on answer below:</strong></p>
  
  <pre><code>library(RMySQL)
  con &lt;- dbConnect(MySQL(),
                   user="root", password="",
                   dbname="MyDataBase")
  
  GetValue &lt;- function(DataFrame, Table){
    queries &lt;- sprintf("SELECT value as value 
                       FROM %s FORCE INDEX (chrs) FORCE INDEX (sites) 
                       WHERE chrom = %d AND site = %d UNION ALL SELECT 'NA' LIMIT 1", Table, DataFrame$Chr, DataFrame$start)
    res &lt;- ldply(queries, function(query) { dbGetQuery(con, query)})
    DataFrame[, Table] &lt;- res$value
    return(DataFrame)
  }
  df &lt;- GetValue(df, "TableName")
  </code></pre>