php connect to multiple mySql Databases

[fomalhaut]

Hello.

How many mysql databases can I create on my x10hosting account ?

And if that is possible, with php can I connect multiple databases at the same time ? for example, can I have

$db1 = user_database11;
$db2 = user_database2;
$dbconnect1 = new PDO("mysql:host=localhost;dbname=$db1", 'user', 'password');
$dbconnect2 = new PDO("mysql:host=localhost;dbname=$db2", 'user', 'password');
$dbconnect1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbconnect2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// ... 
$count1 = "SELECT count(*) FROM table1 WHERE col1 = '" . $vari1 . "'";
$count2 = "SELECT count(*) FROM table2 WHERE col2 = '" . $vari2 . "'";
// where table1 from $db1 and table2 from $db2
$res1 = $dbconnect1->query($count1);
$res2 = $dbconnect2->query($count2); 

Is that possible ?

Thanks for your answers.

[vishal]

You can have only 3 sql database

[misson]

Some premium hosting options allow for more.

As for accessing multiple databases from the same script, you could create a connection for each database, but this is unnecessary. The database you specify when connecting is merely the default database. When a query specifies a table but not the database, the table is looked for in the default database. To access tables in different database using the same connection, simply specify the database for each table.

Also, if part of a query varies (i.e. you need to interpolate a variable), use prepared statements. Only use PDO::query for static queries.

try {
  $query1 = $db->prepare('SELECT count FROM db1.table1 WHERE col1=?');
  $query2 = $db->prepare('SELECT count FROM db2.table2 WHERE col2=?');

  $query1->execute(array($val1));
  $query1->execute(array($val2));
} catch (PDOException $exc) {
  ...
} 

[fomalhaut]

Thank you for these precisions. I'll use 2 database, with one connect to default db.

Thanks again.

All the best.

[techmo2]

Hello.

How many mysql databases can I create on my x10hosting account ?

And if that is possible, with php can I connect multiple databases at the same time ? for example, can I have

$db1 = user_database11;
$db2 = user_database2;
$dbconnect1 = new PDO("mysql:host=localhost;dbname=$db1", 'user', 'password');
$dbconnect2 = new PDO("mysql:host=localhost;dbname=$db2", 'user', 'password');
$dbconnect1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbconnect2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// ... 
$count1 = "SELECT count(*) FROM table1 WHERE col1 = '" . $vari1 . "'";
$count2 = "SELECT count(*) FROM table2 WHERE col2 = '" . $vari2 . "'";
// where table1 from $db1 and table2 from $db2
$res1 = $dbconnect1->query($count1);
$res2 = $dbconnect2->query($count2); 

Is that possible ?

Thanks for your answers.

Also make sure you close the mysql connections if you are using way too many of them. I believe that the connections get closed when your script stops executing, but it is never a good idea to have a zillion open connections lurking around your app.

Try using a single instance of every db connection you need.

[xav0989]

I usually say: one server, one connection. This is usually a good idea :P