Access multiple DBs using one connection and one query via codeigniter

Question

I have a question regarding mysql db and codeigniter framework

I want to use one query accessing multiple DBs using codeigniter which returns one resultset Example as follow:

SELECT A.name, B.class_name
FROM DB1.Table1 AS A, DB2.Table1 AS B
WHERE A.id = B.id

Return one result????

Following are my connection setting in codeigniter

$db['DB2']['hostname'] = 'localhost';
$db['DB2']['username'] = 'connection_name';
$db['DB2']['password'] = 'connection_password';
$db['DB2']['database'] = 'DB2';
$db['DB2']['dbdriver'] = 'mysql';
$db['DB2']['dbprefix'] = '';
$db['DB2']['pconnect'] = TRUE;
$db['DB2']['db_debug'] = TRUE;
$db['DB2']['cache_on'] = FALSE;
$db['DB2']['cachedir'] = '';
$db['DB2']['char_set'] = 'utf8';
$db['DB2']['dbcollat'] = 'utf8_general_ci';
$db['DB2']['swap_pre'] = '';
$db['DB2']['autoinit'] = TRUE;
$db['DB2']['stricton'] = FALSE;


$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'connection_name';
$db['default']['password'] = 'connection_password';
$db['default']['database'] = 'DB1';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Kindly provide me some sample code which helps to run the above query.

Thanks in advance

It's not possible to help you without your data. Posting username and password to access your database at localhost doesn't help. ;) — memowe
– memowe, Commented Sep 18, 2012 at 11:17
Hello memowe, May be I am not clear to you. I need to know what is the procedure. So why do you need data for that? Actually my question is very simple i.e. how to use a query which access two DBs using codeigniter framework — Sameer
– Sameer, Commented Sep 18, 2012 at 13:12
I'm sorry, maybe I didn't understand you correctly. I thought your question "Return one result????" was about why you get only one result. My bad. — memowe
– memowe, Commented Sep 18, 2012 at 14:30

Night2 · Accepted Answer · 2012-09-18 09:24:16Z

1

Create 1 connection to the DB1 with a MySQL user for that database, for example user x and give user x privileges to DB2 in MySQL too, this way you can use one connection and access more than 1 database in queries ...

answered Sep 18, 2012 at 9:24

Night2

1,1539 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sameer Over a year ago

Sorry if I didn't clearly specify my problem due to my poor English :( Actually I am facing problem when I use codeigniter. I have given user x privileges to DB1 and DB2 both. And I am able to access it using NAVICAT DB tool. But I don't no what is the procedure for codeigniter framework

Night2 Over a year ago

Do a test before getting to codeigniter part, use phpMyAdmin to connect to your database using user x (DO NOT CONNECT AS root OR OTHER USERS) and test your query there to make sure it works, if it's working then you should look for problem in codeigniter, else your user definition has a problem.

Sameer Over a year ago

Thanks Night2 for your valuable comment, I checked it with new user testuser and its working fine on mysql console. and NAVICAT tool. But the problem is in codeigniter because we use $this->db instead I need $this->db_group type of thing which can access multible DBs in a group. So do you have any clue?

Night2 Over a year ago

Could you update your Question with the code of using connection and sending your query? By seeing that, I may be able to help.

Sameer Over a year ago

You are right But now what I am doing a little bit trick making view of DB1 table in DB2 and then access it using DB2 Its working for now but I know that is not a solution Thank you very much for your time and help Best regards

|

Collectives™ on Stack Overflow

Access multiple DBs using one connection and one query via codeigniter

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related