Create View from multiple databases?

Databases
1

Hello all, this is my first experience with Views so please be gentle if what I’m asking is dumb. A view may not even be what is needed for this?

I have multiple databases (for this instance let’s say there are 20 of them, in reality there will be a couple hundred) In each of these databases there is a ‘users’ table. Each of these users tables are structured exactly the same. These databases may, or may not reside on the same server, currently they are all on the same server but in the future it will expand to other servers.

I need (I think) to create a view that includes all the users tables combined. I want to do this so I can have a ‘global’ login form for users to use.

So the view would really only need to include the email address and the db info from which that email address came from.

My goal is for a visitor to submit the global login form, the script checks the view to see if the email address exists and then checks which db to use to login as then the script queries the correct db and verifies the user (the verification script is already written and functioning).

If there’s a better way to do this I’m open to suggestions/ideas/pointers/advice/guidance. Thanks in advance!

2

one obviously better way would be ~not~ to have hunnerts of databases, but just one

3

Yup, this has been considered. I’ll look at it again and see why we decided this wasn’t a viable solution.

4

Ok, I think our reasoning behind keeping the db’s separate was mainly portability. Each Manufacturer (client) has hundreds of distributors (which may or may not be our clients). What normally happens is that after a mfg signs on with us many of their distributors follow suit. So having the db’s separate helps us to quickly and easily roll each distributor into their own full solution.

Each mfg normally goes on their own dedicated server and we have several other servers that are loaded up with distributor accounts (some full solution, some partial). With each ‘client’ basically having their own db setup we can easily shift accounts to new servers as traffic or client volume requires.

5

Maybe the solution is to create a global users table? Bring all the users into one database?

6

maybe :slight_smile:

7

Ok, that’s what we’re going to do. Gonna take some re-coding and re-formulation but I think it’s for the best long-term. So, for this question the view is answered.

But… I’m still curious, is it possible to setup a view that includes tables from many different databases?

Let’s say we have db1 db2 and db3

Each have tbl1 tbl2 and tbl2

And I want a view (or a query of some sort) that gives me all the records for tbl2 for all databases. Is this possible?

8

Ok, that’s what we’re going to do. Gonna take some re-coding and re-formulation but I think it’s for the best long-term. So, for this question the view is answered.

But… I’m still curious, is it possible to setup a view that includes tables from many different databases?

Let’s say we have db1 db2 and db3

Each have tbl1 tbl2 and tbl2

And I want a view (or a query of some sort) that gives me all the records for tbl2 for all databases. Is this possible?

9

it would take me about 10 minutes to test that, because i would have to create some different databases, then create the tables, and so on

how long will it take you? i mean, you’ve already got the databases and the tables, so…

:slight_smile:

10

Definitely not asking anyone to test anything. But I’m not even sure where to begin to put together a VIEW for multiple databases. Already spent some time on goog, guess I’ll keep on searching.

11

you should have started with da manual :slight_smile: —

A view can refer to columns of tables or views in other databases by qualifying the table or view name with the proper database name.

then you will want to have a UNION query because each table will require a separate SELECT – the view should be created on the UNION query