Joining tables in two dbs - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Joining tables in two dbs (/showthread.php?tid=30563) |
Joining tables in two dbs - El Forum - 05-18-2010 [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? Joining tables in two dbs - El Forum - 05-18-2010 [eluser]frankcefalu[/eluser] PConnect = persistent connection I am assuming? Joining tables in two dbs - El Forum - 05-18-2010 [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. Joining tables in two dbs - El Forum - 05-18-2010 [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. Joining tables in two dbs - El Forum - 05-18-2010 [eluser]frankcefalu[/eluser] When you say hosts.. Do you mean your hosts file? Or Hosting company? Joining tables in two dbs - El Forum - 05-18-2010 [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. Joining tables in two dbs - El Forum - 05-18-2010 [eluser]frankcefalu[/eluser] do they allow you to edit your hosts file in cpanel? Joining tables in two dbs - El Forum - 05-18-2010 [eluser]Eric Brown[/eluser] Yes, I have a VPS so I can edit everything. Joining tables in two dbs - El Forum - 05-18-2010 [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"; Than, query as normal but now use the two seperate configs. Let me know if you need any more help! Joining tables in two dbs - El Forum - 05-19-2010 [eluser]Eric Brown[/eluser] Example Query (forgive any errors) Code: $sql = <<<SQL DBs: Code: $db['default']['hostname'] = "10.1.1.1"; |