0

I have 2 databases db_1 and db_2.
db_1 contains table users (contains 'uid','name' columns)
db_2 contains table blog_posts (contains 'pid','uid','post_title' columns)
I want to extract 20 posts from blog_posts along with user's name corresponding to uid mentioned in blog_posts. I tried to use 'join' with codeigniter prepared mysql statements, but it didn't work.
What is the best way to get this data. I don't want to get name one by one for each uid as it will create database connection many times i.e. 21 (1 for getting all posts and 20 to get each post user name) which slow down the speed. Is there any more effective method.

Aman Dhanda
  • 438
  • 4
  • 15

1 Answers1

0

Well, I have got an answer for the question. We can do the following. It will create only 2 database connections.

  1. Get all the posts with 'pid','uid','post_title' you want to show, say 20 in your case. Like, $post_data= array( 0=>array( 'pid'=>1, 'uid'=>2, 'post_title'=>'Hello there!' ), 1=>array( 'pid'=>2, 'uid'=>5, 'post_title'=>'What's up!' ) );

  2. Now, get list of names from users corresponding to all uid you get in Step 1 in an array.

    $user_map= array( '2'=>'John', '5'=>'Robert', '11'=>'David' );

  3. Now, you can print user's name who belongs to the post as follows:

    foreach($post_data as $value) { echo $value['post_title']."-".$user_map[$value['uid']]; }

Aman Dhanda
  • 438
  • 4
  • 15
  • If the databases db_1 and db_2 are on the same MySQL server you can get this in one query. Please look at the answer at [this link](http://stackoverflow.com/a/5698396/393865). – Bojan Dević Feb 17 '16 at 09:54
  • But CodeIgniter creates database connection first before executing any query. So we can access only one database in 1 query. – Aman Dhanda Feb 17 '16 at 09:58
  • If your MySQL user has access to both databases you can query both databases using prefixes like db_1.table_name, no matter which connection CodeIgniter is using. – Bojan Dević Feb 17 '16 at 10:03
  • If we are using codeigniter prepared statements, is it the right way to call different database table like this. I mean codeigniter have proper way to create connection with database like this, `$this->load->database('db_1', TRUE);`. But here we are overriding this. – Aman Dhanda Feb 17 '16 at 10:26