Connect to a database with info from another database table?

Question

Is it possible to connect to a database with tables in a database you are already connected with.

This is what I want :

I have a database with tables called regdbhost,regdbuser,regdbname and regdbtable. In the tables you can get the info to connect. For instance regdbhost=localhost, regdbuser=username, regdbname=database name.

This is my code

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

<?php 
if(!isset($_SESSION['logged_in']) || $_SESSION['logged_in'] == false) 
{ 
header('Location: index.php'); 
exit(); 
}  

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=inschrijvingen.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('id','Name', 'Email', 'age','tekens'));

// fetch the data
$table = "user";
$query = mysql_query("SELECT regdbhost,regdbuser,regdbpass,regdbname,regdbtable FROM $table");

while($result = mysql_fetch_array($query))
{   
$regdbhost = '.$result["regdbhost"].';
$regdbuser = '.$result["regdbuser"].';
$regdbpass = '.$result["regdbpass"].';
$regdbname = '.$result["regdbname"].';
$regdbtable = '.$result["regdbtable"].';
$link = mysql_connect($regdbhost, $regdbuser, $regdbpass);
mysql_set_charset('utf8',$link);
mysql_select_db($regdbname);
$rows = mysql_query('SELECT * FROM $regdbtable');
}
// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) 
fputcsv($output, $row);

?>

I tried to connect it like this but it didnt work. What am I doing wrong?

Okay guys I fixed it.

I changed this :

$rows = mysql_query('SELECT * FROM $regdbtable');

to

$rows = mysql_query("SELECT * FROM $regdbtable");

Make sure you get the $result correctly. var_dump to see it has info you wanted — Serhat Akay, Oct 14 '15 at 13:53
Do you have any errors ? Add `ini_set('display_errors',1); ini_set('display_startup_errors',1); error_reporting(-1);` in top of your code if not. Moreover, your `while($row = mysql_fetch_assoc)` should be in your `while($result = ...)` because you are reinitialising `$row` var on each loop. — ThinkTank, Oct 14 '15 at 14:02
No, I don't have any errors.. I tried your code but it did nothing. — twoam, Oct 14 '15 at 14:14
MySQL Functions are deprecated and not advisable to use. I see no issue with your current code. I would advise checking `$result` for errors before proceeding. If you're getting the results, it should work. — Twisty, Oct 14 '15 at 18:15

score 1 · Answer 1 · edited May 23 '17 at 10:27

As mentioned by others, the MySQL extension is deprecated, and you should stop using it. More info in Why shouldn't I use mysql_* functions in PHP?

As for your code, the problem why it doesn't work lies in here:

$regdbhost = '.$result["regdbhost"].';
$regdbuser = '.$result["regdbuser"].';
$regdbpass = '.$result["regdbpass"].';
$regdbname = '.$result["regdbname"].';
$regdbtable = '.$result["regdbtable"].';

And here:

$rows = mysql_query('SELECT * FROM $regdbtable');

Variables will not be expanded when inside single quotes, so the value of $regdbhost will be .$result["regdbhost"]., not what is actually contained in $result["regdbhost"].

Also the way you're trying to concatenate the value to the string is wrong. You don't concatenate inside the quotes, you do it outside the quotes:

$myVar = 'Hello, ' . $name . '!';

In your case, however, you don't need any concatenation as you can assign the value directly:

$regdbhost = $result["regdbhost"];

So if you fix the way you handle the contents of $result and switch to double quotes in your following SELECT statement, your code should essentially work.

In additional to checking out why you should switch to other DB extensions, I also recommend you take a look at How can I prevent SQL-injection in PHP? if you plan on using user input in your queries.

Twisty · Answer 2 · 2015-10-14T20:25:31.183

I would suggest you collect the content before placing it into your Text File. Also I would suggest limiting your query or adding a WHERE statement. If you get more than one set of credentials, the script could fail to login successfully.

<?php
// Check connection
if ($conn->connect_error) {
    die("First Connection failed: " . $conn->connect_error);
}  
if(!isset($_SESSION['logged_in']) || $_SESSION['logged_in'] == false){ 
    header('Location: index.php'); 
    exit(); 
}

// fetch the Login data
$table = "user";
$query = mysql_query("SELECT regdbhost,regdbuser,regdbpass,regdbname,regdbtable FROM $table LIMIT 1");

if (!$query) {
    echo 'First Could not run query: ' . mysql_error();
    exit();
}
while($result = mysql_fetch_array($query)){   
    $regdbhost = $result["regdbhost"];
    $regdbuser = $result["regdbuser"];
    $regdbpass = $result["regdbpass"];
    $regdbname = $result["regdbname"];
    $regdbtable = $result["regdbtable"];
}
mysql_free_result($result);

// Create new connectiong with DB credentials from other table
$link = mysqli_connect($regdbhost, $regdbuser, $regdbpass, $regdbname);
if (mysqli_connect_errno()) {
    printf("Second Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$fetchResults = array();
if ($result = mysqli_query($link, "SELECT * FROM $regdbtable"){
    while ($row = mysqli_fetch_assoc($result)) {
        $fetchResults[] = $row;
    }
    mysqli_free_result($result);
}
mysqli_close($link);

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=inschrijvingen.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('id','Name', 'Email', 'age','tekens'));

// loop over the rows, outputting them
foreach($fetchResults as $row){
    fputcsv($output, $row);
}
?>

Connect to a database with info from another database table?

2 Answers2