php how to generate dynamic database connection in for loop in mysql

Question

i am new to working in php, i am work on how to alter table dynamically in database through coding in php

database like this,

database name : data_switch

table name - data

 did   dataname  host  dbuser   dbpwd   dbname
 1     abc      local  root    root     abc_db   // here dataname create new database, when register new dataname
 2     pqr      ubuntu root    passwd   pqr_db

php code below:

<?php
        $dsn = "localhost";
        $username = "root";
        $password = "passwd";


        $db = new PDO("mysql:host=$dsn;dbname=data_switch", $username, $password);
      $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        foreach ($db->query("select * from data") as $row)
        {
           $dataname = $row['dataname'];
           $host = $row['host'];
           $dbuser = $row['dbuser'];
           $dbpwd = $row['dbpwd'];
           $dbname = $row['dbname'];

          $connection_array['data1'][] = array(

              'dataname' => $dataname,
              'host' => $host,
              'dbuser' => $dbuser,
              'dbpwd' => $dbpwd,
              'dbname' => $dbname
           );

        }
        echo "<pre>";
        print_r($connection_array);

       $dbh = new PDO("mysql:host=$host;dbname=$dbname", $dbuser, $dbpwd);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   // here all dataname find but i dont know how to generate dataname's own connection. dynamically

?>

i am getting all dataname in loop but i am not able to dynamically diffrent connection,i am register new dataname in form as like abc then it create a new database dynamically, but when i am alter the table i am not getting how to connection dynamically on each dataname's host,dbname,dbpwd through each own dataname.

Any body having any idea please help to sort it out. Thanks

This is highly unorthodox, creating databases in a dynamic manner, never seen such thing... — arkascha
– arkascha, Commented Jun 5, 2018 at 6:25
About your specific question: you need to assign the database connection you get back from the new PDO() call to different variables. Otherwise you always replace the prior one just as with wech other variable. Maybe you want to store the objects in an array... But as said: what you attempts is "strange"... — arkascha
– arkascha, Commented Jun 5, 2018 at 6:26
I thought I did understand your question. That is why I posted these comments. Sorry if you have the impression that is not the case. — arkascha
– arkascha, Commented Jun 5, 2018 at 6:36
sorry @arkascha, i store object bt the connection string in for loop inside writing ?? — Paras Prajapati
– Paras Prajapati, Commented Jun 5, 2018 at 6:47

D. Clark · Accepted Answer · 2019-02-25 06:51:54Z

I know it's an old post but it came up in a search as a possible relation to a question I had.

I have a script doing very similar to what is being asked. You need to place your connections in a dynamic variable.

eg.

I have a list of branches and their respective database connection details in an array called $branchDcom. I can make calls to all my branches without closing connections and opening all the time.

I have a connection function that connects to a database with the sent values in the function conn_dcom_branch('your_server', 'your_db', 'your_username', 'your_password')

The following creates connections to all databases.

$connection = array();
foreach ($branchDcom as $key => $value) {
    $branch = $value['Name'];
    if (!is_resource($connections[$branch]['conn'])) {
        ${'connection_'.$branch} = conn_dcom_branch($value['server'], $value['db'], $value['username'], $value['password']);
        ${'connection_'.$branch.'_db'} = $value['db'];
    }
}

This gives me an array of all branch connections and their respective database names.

To use just enter in the branch name and it will use that connection:

$branch = 'select_name';
$query = "
    SELECT 
        your_field
    FROM 
         [".${'connection_'.$branch.'_db'}."].['table_name'] 
";
$rs = ${'connection_'.$branch}->execute($query);

This may help someone

Collectives™ on Stack Overflow

php how to generate dynamic database connection in for loop in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related