Computer Science Canada

Using multiple databases with PHP
Author:  peterggmss [ Mon Oct 30, 2006 9:29 pm ]
Post subject:  Using multiple databases with PHP
Hello,
On my own, and for a self-inspired and self-directed work project not for school, I'm building a system with user management where companies can launch their PHP/MySQL applications and have us manage the users, fees, etc. This system has many uses, such as another system I'm developing as a self-inspired and self-directed work project.

Bit of background on my project:
My grandmother is a real estate brokerage and we find that the recordkeeping is a lot of work as data doesn't magically move between Word, Excel, TorontoMLS, and her Real Estate Trust Account records. I'm developing a system similar to quick!office commander http://www.les.ca/. The cost of quick!office commander inspired me to make a system using PHP and MySQL which brokers could use for a low fee (around $300 per year plus taxes and a per-user fee). I decided to extend this to a system that other companies could use to launch their solutions and that I could use to launch other solutions.

The Problem
There will be different solutions launched on the Red Forums Solution Launcher. We will need a system with one database for each user (rfusers) and different databases for each solution (realestate, financing, relaw, etc.). I need to store the users in rfusers and the information in realestate, financing or relaw - depending on which service they purchase. I need to know how to do this, right now I have no code as I'm just starting to learn PHP and SQL, but I can see how powerful they are when combined.[/url]
Author:  rdrake [ Mon Oct 30, 2006 10:04 pm ]
Post subject: 
php:
<?php
    $conn = mysql_connect("localhost", "root", "");
    $db1  = mysql_select_db("database1", $conn);
    $db2  = mysql_select_db("database2", $conn);

    $result = mysql_query("SELECT * FROM whatever", $db1);
?>
As I recall.
Author:  Tony [ Mon Oct 30, 2006 10:30 pm ]
Post subject: 
yey for phpGACL... it's like... almost no work on your part now
Author:  octopi [ Mon Oct 30, 2006 10:40 pm ]
Post subject: 
Tony is almost correct, you can also have different connections too.

code:

$local = mysql_connect("localhost", "root", "");
$remote = mysql_connect("someremotehost", "username", "");
mysql_select_db("database1", $local);
mysql_select_db("database2", $remote);

$result=mysql_query("SHOW TABLES FROM `database1`;",$local);
$result_remote=mysql_query("SHOW TABLES FROM `database2`;",$remote);


//etc....

You specify the 'link' which is the connection to the mysql server.

Also, I don't want to discourage you, but Its very hard to make such a complex program. Make sure you do tons of planning work before you start coding, make sure you know how things are going to fit together, and work, this way you will avoid problems down the line...that require a sort of patchy solution (which is no good).

Good luck!, feel free to pm if you need any help.
Author:  rdrake [ Mon Oct 30, 2006 11:12 pm ]
Post subject: 
Most interesting, Octopi. Didn't know you could to that.

My suggestion would be to use as little databases per client as possible though if you can. If need be, you can resort to table prefixes. Many hosts (still) seem to charge for extra databases, plus it leads to a more complex install.
Author:  PeterK [ Wed Aug 01, 2012 5:21 pm ]
Post subject:  Re: Using multiple databases with PHP
rdrake @ Tue Oct 31, 2006 3:04 am wrote:
php:
<?php
    $conn = mysql_connect("localhost", "root", "");
    $db1  = mysql_select_db("database1", $conn);
    $db2  = mysql_select_db("database2", $conn);

    $result = mysql_query("SELECT * FROM whatever", $db1);
?>
As I recall.


This is WRONG, mysql_select_db returns only true or false.

handler is for mysql_connect, it works like this:

php:
    $conn1 = mysql_connect("localhost", "root1", "");
    $conn2 = mysql_connect("localhost", "root2", "");

    mysql_select_db("database1", $conn1);
    $result1 = mysql_query("SELECT * FROM table1", $conn1);
    mysql_select_db("database2", $conn1);
    $result2 = mysql_query("SELECT * FROM table2", $conn1);
    mysql_select_db("database1", $conn2);
    $result3 = mysql_query("SELECT * FROM table1", $conn2);
    mysql_select_db("database2", $conn2);
    $result4 = mysql_query("SELECT * FROM table2", $conn2);
Author:  Dreadnought [ Wed Aug 01, 2012 5:25 pm ]
Post subject:  Re: Using multiple databases with PHP
Out of curiosity, is there a function to send a post 6 years into the past?

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy