Welcome Guest, Not a member yet? Register   Sign In
One select statement that queries multiple databases
#1

[eluser]tinawina[/eluser]
Hi there - I'm in the planning stage for a project I need to get done that will add some new features to an existing website I coded with CI. I'm thinking it might be a good idea to set up a separate database for this new project since I'm already up to about 30 tables just for the website, and the project will add another 15-20 tables.

I understand that I can define as many databases as I want in the database config file. Seems very straightforward. But I don't understand how to do a query that stretches over 2 or more databases. If it were a non-CI situation I would do something like:

Code:
SELECT
database1.table.field, database2.table.field
FROM
database1.table, database2.table
WHERE
database1.table.another-field = database2.table2.another-field

CI has me setting up two discrete connections:

Code:
$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

And then I'm calling the database like this, for eg.:

Code:
$DB1->query();
$DB1->result();
How would I do the SELECT statement described here given these discrete connections that you call one at a time? (Or am I just not getting this??) Any help is much appreciated. Thanks!
#2

[eluser]James Gifford[/eluser]
To my knowledge a multiple database query isn't possible in CI. The database class is designed to connect to one database at a time. You can still use built-in PHP functions of course, but I don't see a way to do it using CI.
#3

[eluser]tinawina[/eluser]
Hmmmmm I was afraid of that. I'll play around with this, see if I can't break my queries up a bit and do some array_combine-ing.... Thanks for your input -- helps me to rethink my project. Smile
#4

[eluser]ontguy[/eluser]
I'm just curious about why you want to create another database instead of adding the tables to the existing database since the tables are related?

I don't think it would cause any performance issues? A prefix could be used for the new set of tables for organization.
#5

[eluser]tinawina[/eluser]
Right - that's where I landed too. Smile I was trying to think through how separation could be helpful. Eg., for data maintenance moving forward. I am sharing some of the burden of maintaining with staff that are non-programmers and was trying to think about how I might be able to more quickly get people into maintenance via phpMyAdmin rather than me writing all types of little maintenance tools. Seeing 15 tables as opposed to, say, 50, seemed like a good first step for handing off some of this. But really - the data relationships should be driving my dbase design -- not me trying to get out of a little work!
#6

[eluser]Unknown[/eluser]
This is no problem, see this link: How to reference multiple databases on the same server
#7

[eluser]tonanbarbarian[/eluser]
provided the user that is connecting to database1 has permissions to also read data from the tables in database 2 there should be no issue.
simply connect to database1, or whichever is the primary database that you will be working with most of the time, and then run the query making sure you reference the database as well as the table correctly and it should work.
i have not tested this in CI or any other PHP code, however I have run queries across databases in phpMyAdmin many times without issues.

just remember permissions is the key here, user must have access to both database, or at least the tables in database2 if not the whole database

i do not believe you need to have the dbo as mentioned previous i.e. database1.dbo.table
database1.table has always worked for me in phpMyAdmin




Theme © iAndrew 2016 - Forum software by © MyBB