Welcome Guest, Not a member yet? Register   Sign In
Joining tables in two dbs
#1

[eluser]Eric Brown[/eluser]
I have two databases, one for reporting and one for their account data. PConnect = FALSE

In my query, since I have to connect two both databases and then join on user_id, what's the right format for generating a query? I know I can hardcode the database.tablename in the SQL query, but should I need to move hosts, and the database names change this will make my code a nightmare to update.

Can I insert the db object names in the SQL itself?
#2

[eluser]frankcefalu[/eluser]
PConnect = persistent connection I am assuming?
#3

[eluser]frankcefalu[/eluser]
One Thing I Can Recommend Is not doing either. Just have the tables you need linked between to the two.

You can sym_link the tables if the db is on the same server.
#4

[eluser]Eric Brown[/eluser]
Right. It is = false because both databases are on the same server, and the persistent connection stays open with number 1 when called alone when i then initiate number two.

So the problem I have is not connecting to two different databases serially, but connecting to them in one query where I am joining tables. I could hardcode the database names in the query, but that doesn't allow me to update the names easily should I move hosts.

Thanks.
#5

[eluser]frankcefalu[/eluser]
When you say hosts.. Do you mean your hosts file? Or Hosting company?
#6

[eluser]Eric Brown[/eluser]
Hosting company. So right now my host uses cpanel which prefixes a db name with my username.


If I move hosts to a new web host, they might not have cpanel and might not do that.
#7

[eluser]frankcefalu[/eluser]
do they allow you to edit your hosts file in cpanel?
#8

[eluser]Eric Brown[/eluser]
Yes, I have a VPS so I can edit everything.
#9

[eluser]frankcefalu[/eluser]
If you change hosts you will need to change your config file anyways.

What I would do inside your config file is as follows.

Code:
$db['db1']['hostname'] = "localhost";
$db['db1']['dbdriver'] = "mysql";
$db['db1']['pconnect'] = FALSE;
$db['db1']['db_debug'] = TRUE;
$db['db1']['cache_on'] = FALSE;
$db['db1']['char_set'] = "utf8";
$db['db1']['dbcollat'] = "utf8_general_ci";
$db['db1']['username'] = "";
$db['db1']['password'] = "";
$db['db1']['database'] = "";


$db['db2']['hostname'] = "localhost";
$db['db2']['dbdriver'] = "mysql";
$db['db2']['pconnect'] = FALSE;
$db['db2']['db_debug'] = TRUE;
$db['db2']['cache_on'] = FALSE;
$db['db2']['char_set'] = "utf8";
$db['db2']['dbcollat'] = "utf8_general_ci";
$db['db2']['username'] = "";
$db['db2']['password'] = "";
$db['db2']['database'] = "";

Than, query as normal but now use the two seperate configs.

Let me know if you need any more help!
#10

[eluser]Eric Brown[/eluser]
Example Query (forgive any errors)
Code:
$sql = <<<SQL
select      a.user_id, b.user_id, b._first_name
from        site_db1.users a
left join   site_db2.user_details b
on         a.user_id = b.user_id
where       date(from_unixtime(b.reg_date)) = adddate(date(now()), -1)
and         a.active = 1;
SQL;

$query = $this->aggregation_db->query($sql);

DBs:
Code:
$db['default']['hostname'] = "10.1.1.1";
$db['default']['username'] = "db_user";
$db['default']['password'] = "password1";
$db['default']['database'] = "site_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['aggregation']['hostname'] = "10.1.1.2";
$db['aggregation']['username'] = "db_user";
$db['aggregation']['password'] = "password_1";
$db['aggregation']['database'] = "site_db2";
$db['aggregation']['dbdriver'] = "mysql";
$db['aggregation']['dbprefix'] = "";
$db['aggregation']['pconnect'] = TRUE;
$db['aggregation']['db_debug'] = TRUE;
$db['aggregation']['cache_on'] = FALSE;
$db['aggregation']['cachedir'] = "";
$db['aggregation']['char_set'] = "utf8";
$db['aggregation']['dbcollat'] = "utf8_general_ci";




Theme © iAndrew 2016 - Forum software by © MyBB