Monday 26 March 2012

Join Multiple Tables from Multiple Databases in PHP & in Codeigniter


// Join Multiple Tables from Multiple Databases in PHP
PS: You just you use the default database connection to execute the query or you can used either of any database connection if you have already made any other database connection instance. 

// This is for default database connection.
$sqlStr = "SELECT d1t1.id, t1.name, d2t2.no_of_sales, d3t2.customer_type
    FROM db1.Table1 as d1t1, db2.Table1 as d2t1, db3.Table2 as d3t2 
    WHERE d1t1.id='3' AND d3t2.customer_type='sales'";
$result = mysql_query($sqlStr);  
while($row = mysql_fetch_array($result))  {
  echo $id  = $row['id'];
        echo '\n';
        echo $name  = $row['name'];
        ..........
}
In the case of CodeIgniter, please find code below to pull the data from two or multiple database tables.


PS: You just you use the default ($this->db) database connection to execute the query or you can used either of any database connection if you have already made any other database connection instance.

// This is for default database connection.
$this->sqlStr= "SELECT d1t1.id, t1.name, d2t2.no_of_sales, d3t2.customer_type
                   FROM db1.Table1 as d1t1, db2.Table1 as d2t1, db3.Table2 as d3t2
     WHERE d1t1.id='3' AND d3t2.customer_type='sales'";
$query = $this->db->query($this->sqlStr);
$result = $query->row_array(); 
print_r($result);

6 comments:

  1. I have two databases in two different servers. How can I join two tables in these two tables in CodeIgniter?

    ReplyDelete
    Replies
    1. Just pass the Full Database Server URL into the second connection instead of 'dbserver_2' below;

      /** Defining Database 1 */
      $db['default']['hostname'] = 'localhost';
      $db['default']['username'] = 'dbusername_1';
      $db['default']['password'] = 'dbpassword_1';
      $db['default']['database'] = 'database_1';
      /* ----------------- */

      /** Defining Database 2 */
      $db['manage']['hostname'] = 'dbserver_2';
      $db['manage']['username'] = 'dbusername_2';
      $db['manage']['password'] = 'dbpassword_2';
      $db['manage']['database'] = "database_2";
      /* ----------------- */


      Please refer http://rcadhikari.blogspot.co.uk/2012/05/solution-to-problem-defining-multiple.html for defining two different server.

      Delete
  2. Nice tip for connecting to multiple databases in codeigniter, however developers should make sure that they have manually closed the connection once the code doesn't require it anymore. source: https://www.cloudways.com/blog/connect-multiple-databases-codeigniter/

    ReplyDelete
  3. Hi may i know how i can join the table for multiple database from different server? I've already load the database at my model but how can i used it in one query

    $this->db = $this->load->database('default', TRUE);
    $this->db2 = $this->load->database('otherdb', TRUE);

    $query= "SELECT * FROM db.user_request t1 JOIN db2.users t2 ON t2.id = t1.user_id";

    $result = mysqli_query($query);

    ReplyDelete
  4. This is a helpful tutorial for joining multiple tables, but you should also have mentioned how one can create connections for Codeigniter multiple databases. To create multiple databases, you have to create multiple database configuration arrays.

    ReplyDelete
  5. What if both databases are on a different server? then how can I get the records. When i join two tables then unable to execute the query

    ReplyDelete

Please post any queries and comments here.