Assigning a variable to the value of a cell from a reactive rendered table pulled from a MySQL query

shiny
, , , ,
2

Hi,

Welcome to the RStudio community!

Since you asked, let me start by pointing you towards some information on how to best create a good post that people on here can work with. It's formatted as a reprex. A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:

Now for your question, I'm not sure I completely get it what you want, but I assume that you want the username (provided by the user in the app), to first filter a table to display the correct user information, then use the user's id (after filtering) to get info from another table.

If this is what you like, you can actually solve that with just SQL, no extra R or Shiny code needed:

sprintf("SELECT * FROM mysqldb.OtherTable WHERE user_ID =
 (SELECT user_id from mysqldb.UserTable WHERE username = '%s')", input$username)
  • UserTable is the table that contains the usernames and ids, OtherTable is the one where the id is the foreign key (replace with actual names)
  • user_id and username are the attributes of those tables (replace with actual names)
  • The actual query is a nested query where the we first look up the user_id based on the username in one table, then use it as input for querying the next table
  • Using the sprintf() function (could also be done with paste), we paste the username given in Shiny (input$username, replace with actual textbox id) into the query.

The result should return the data you need from the database.

Hope this helps,
PJ

1 Like