0

I'm trying to create one standard view which will dynamically select different database tables based on the MySQL user which is selecting the view. In a master database

In database_clients I have a table users_list as follows:

user_mysql company sample_user1 company1 sample_user2 company2

I then have seperate IDENTICAL client databases with IDENTICAL client specific tables for example:

database_company1.table1 database_company2.table1

Currently, I duplicate IDENTICAL views in each client database which is cumbersome and requires me to update every database view individually when I make changes. My goal is to have one "master" view which is used to query tables in each client database, with the database determined by the MySQL user login.

I've figured out that you could identify the client database using something like this:

SELECT company 
FROM database_clients.users_list
WHERE user_mysql = (SELECT SUBSTRING_INDEX(USER(), '@', 1));

What I need help with is trying to build this into a VIEW in the master database_clients.

When I try build a "dynamic database" VIEW based on user specific crieteria as follows in database_clients it throws errors:

SELECT * 
FROM concat(
    'database_',
    (
        SELECT company 
        FROM database_clients.users_list 
        WHERE user_mysql = (
            SELECT SUBSTRING_INDEX(USER(), '@', 1))
    ),
    '.table1'
);

Any guidance greatly appreciated!

Kevin

Slava Rozhnev
  • 8,085
  • 6
  • 21
  • 35
kevmanct
  • 1
  • 1

1 Answers1

-1

Thanks...I'm closer but not quite there....if you look at my stored procedure below I'm not figuring out how to use the variable "company" properly where it carries through to the second prepare statement section.....ideas?

SET @usernames = (SELECT SUBSTRING_INDEX(USER(), '@', 1));
SET @firstQuery = (CONCAT("SELECT company FROM database_client.users_list where user_mysql = ","'", @usernames,"'")); 

PREPARE company FROM @firstQuery;
EXECUTE company;

SET @finalQuery = (CONCAT("SELECT * FROM database_",company,".sample_table1"));

PREPARE stmt FROM @finalQuery;
EXECUTE stmt;

DEALLOCATE PREPARE company;
DEALLOCATE PREPARE stmt;
kevmanct
  • 1
  • 1