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);

2 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

Please post any queries and comments here.