CODEWITHSUNDEEP
phpmysql
Trevor Ackermann
Trevor Ackermann

Reputation: 176

MySQL connecting to multiple Databases at same time

I have multiple databases on my localhost using the same username and password. As information is stored in different Databases and I need to work cross databases I need to open both at the same time. I insert data into one DB and want to delete tables in the other and also data.

Here is my current connection I am trying.

$host = "localhost"; // Host name 
$username = "root"; // Mysql username 
$password = "@"; // Mysql password 
$db_name = "etrack"; // Database 1
$db_name2 = "gs"; // Database 2

// Create connection
$conn = new mysqli($host, $username, $password, $db_name);
$conn2 = new mysqli($host, $username, $password, $db_name2);
// Check connection
if ($conn2->connect_error) {
    die("Connection failed: " . $conn2->connect_error);

}

I then run some sql strings one example is:

$sql = "INSERT INTO unit_hx SET
                imei = '".$imei."',
                model = '".$model."',
                supp = '".$supp."',
                status = 'Scrapped',
                installer = '".$installer."',
                unit_nr = '".$unit_nr."',
                cost = '".$cost."',
                systime = '".$time."',
                msisdn = '".$msisdn."',
                sysdate = '".$date."',
                notes = '".$notes."',
                staff = '".$agent."'";

I want to Drop a table in database gs based on imei information retrieved in database etrack 

DROP TABLE 'gs_object_data_'".$imei."' 
//imei information is set on the page from a previous form using $_POST

Would this be possible using information from DB1 to use in DB2 query and delete DB2 tables?

Edited Working Code: To insert or update information in two different databases the following code is useful;

    "INSERT INTO etrack.simcard_hx SET" //etrack is the database name simcard_hx is the table name
Changing database to other database I used 
"INSERT INTO gs.simcard_hx SET" // gs being the second database

Upvotes: 0

Views: 774

Answers (1)

Nick
Nick

Reputation: 147146

Your root user should have access to both databases, so you shouldn't need the second connection. If it doesn't, create a user that does, it will simplify your life. Then, in queries you can just prefix your table names with the database name: e.g.

SELECT * 
FROM etrack.table1 t1
JOIN gs.table2 t2 ON t2.etrack_id = t1.id

Upvotes: 1

Related Questions