CODEWITHSUNDEEP
mysqlpdodatabase-connection
Apiwat Jarruwattanachai
Apiwat Jarruwattanachai

Reputation: 129

PDO: connect to database server (not specific database name)

Currently I connect to database server by this

@$db_connect = mysql_connect('localhost','user','xxx', true);

I have to connect to database server (not specific database name) due to query with multiple database in the same query.

Can anyone suggest me how to do the same thing with PDO?

Upvotes: 3

Views: 1169

Answers (2)

quietmint
quietmint

Reputation: 14164

You can do the same thing in PDO. The dbname attribute of the MySQL DSN string is optional.

$db = new PDO('mysql:host=localhost', $user, $pass);

In this case, however, you will need to execute use databasename or ensure all queries use the format SELECT * FROM databasename.tablename.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

You will still need to choose one of the databases in the PDO $dsn, even if you are to be querying across multiple databases. This isn't really a problem because in your queries you will use the pattern dbname.tablename.columnname. When querying against only the database actually specified in the $dsn, you don't need to use the dbname.

You will, of course, need privileges granted on all of the databases you intend to use for the user specified in the connection.

// Need more than just testdb, but specify it here anyway
// It doesn't matter which one you choose here- pick the one you'll use the most I suppose
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// Query across 2 databases, and specify aliases for the dbname.tablename
$stmt = $dbh->prepare("
  SELECT 
    tdb.col1, 
    other.col2 
  FROM 
    testdb.table1.col1 AS tdb
    JOIN otherdb.table2.col2 AS other 
      ON tdb.col1 = other.col2
");

Upvotes: 2

Related Questions