Multiple MySQL Connections

[alexjewell]

I have an included filed called dbLogin.php, with the following function:



function dbLogin($dbDatabase){
global $dbHost, $dbUser, $dbPass, $db;

$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");

mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
}


Then, I simply call dbLogin() when I need to connect to a database. It works with multiple connections on other pages, and I use mysql_close() before each instance just to be safe.

Anyway, the problem: I'm using a foreach statement to grab $_POST variables submitted to the page, and splitting them up into two parts in order to be added to two different databases. Here's the code:



$name = isset($_POST['name']) ? trim(stripslashes($_POST['name'])) : FALSE;
$sn = isset($_POST['sn']) ? trim(stripslashes($_POST['sn'])) : FALSE;
$db2 = isset($_POST['db']) ? $_POST['db'] : FALSE;
$dbtable = isset($_POST['dbtable']) ? $_POST['dbtable'] : FALSE;
unset($_POST['name']);
unset($_POST['sn']);
unset($_POST['db']);
unset($_POST['dbtable']);
unset($_POST['submit']);
unset($_POST['submit3']);
$dbpath = $db2."-".$dbtable;
$addArr = array(
"name" => $name,
"sn" => $sn,
"dbpath" => $dbpath);
$addArrKeys = array_keys($addArr);
$standFields = array_keys($standFields);
$query_standFields_cols = "$addArrKeys[0],$addArrKeys[1],$addArrKeys[2]";
$query_standFields_vals = "'$addArr[name]','$addArr[sn]','$addArr[dbpath]'";
$query_custFields_cols = "sn";
$query_custFields_vals = "'$sn'";
include('dbLogin.php');
dbLogin($db2);
$result = mysql_query("SELECT * FROM $dbtable");
$result = mysql_fetch_assoc($result,MYSQL_ASSOC);
$result = array_keys($result);
foreach($_POST as $key => $val){
if(in_array($key,$standFields)){
$query_standFields_cols .= ",$key";
$query_standFields_vals .= ",'$val'";
}
else if(in_array($key,$result)){
$query_custFields_cols .= ",$key";
$query_custFields_vals .= ",'$val'";
}
else{
die('<h1>This is not working so well.</h1>');
}
}
$query_custFields = "INSERT INTO $dbtable ($query_custFields_cols) VALUES ($query_custFields_vals)";
$query2_custFields = mysql_query($query_custFields) or die('WEIRD! query1custFields');
mysql_close();
dbLogin('standardFields') or die('Cant login to standardFields!');
$query_standFields = "INSERT INTO standFields ($query_standFields_cols) VALUES ($query_standFields_vals)";
$query2_standFields = mysql_query($query_standFields) or die('WEIRD! query2standFields');
mysql_close();
header('Location:dEdit.php?viewSN='.$sn);


The custom fields are added to that specific database, but then the code dies at dbLogin('standardFields'). The database standardFields exists and I'm able to login to it in other places. Why won't it login to standardFields here? See anything I'm missing? Thanks.

[djr33]

I haven't looked into this issue too deeply, but here's the basic info on using two databases:
1. When you connect to mysql, you store it in a variable like $conn. I believe this may be required, though I'm not positive about that.
2. To identify the connection you want to use for a query (or any other 'mysql_' function) you use $conn or whatever other identifier.
3. If you choose to skip $conn in those functions (which is ok, just lazy), it will assume the latest mysql connection.
4. So, if you want to use two different mysql connections here are the things you need to do:
a) Store [for example] $conn1 and $conn2 as the connections.
b) Reference these explicitly in your various queries/functions. Do not leave any blank or it may become unpredictable which was the "last" connection.

Does this help with the overall picture? There may be other issues with the code above, but that's somewhere to start at least.

[alexjewell]

Well, I would pursue the $conn idea if this very setup wasn't working elsewhere. Here's where it works on a different page:



include('dbLogin.php');
dbLogin('standardFields');
$result = mysql_query("SELECT * FROM standFields WHERE sn='$editSN'");
$rowCheck = mysql_num_rows($result);
if($rowCheck > 0){
echo "\n".'<span class="errMsg indent">Edit Item, SN #: '.$editSN.'</span>'."\n";
echo '
<form method="post" action="dEdit.php" id="editSN">
<table cellpadding="2px" cellspacing="3px">'."\n";
unset($standFields['notes']);
unset($standFields['sn']);
unset($standFields['dbtype']);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
foreach($standFields as $key => $value){
if($key == 'campus'){
echo "\n".'<tr><td><b>'.$standFields['campus'].':</b></td><td>'."\n";
echo '<select name="campus">'."\n";
echo '<option value="LY">LY</option>'."\n";
echo '<option value="GM">GM</option>'."\n";
echo '<option value="UC">UC</option>'."\n";
echo '</select></td></tr>'."\n";
}
else{
echo "\n".'<tr><td><b>'.$value.':</b></td><td><input type="text" name="'.$key.'" value="'.$row[$key].'" /></td></tr>'."\n";
}
}
}
$editType = mysql_query("SELECT dbtype,notes FROM standFields WHERE sn='$editSN'");
$editType = mysql_fetch_array($editType);
$getNotes = $editType['notes'];
$editType = $editType['dbtype'];
$editType = explode('-',$editType);
$editTypeD = $editType[0];
$editTypeT = $editType[1];
mysql_free_result($result);
mysql_close();
dbLogin($editTypeD);
$result = mysql_query("SELECT * FROM $editTypeT WHERE sn='$editSN'");
$rowCheck = mysql_num_rows($result);
if($rowCheck > 0){
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
foreach($row as $key => $value){
if($key == 'type' && $editTypeT == 'computers'){
echo "\n".'<tr class="'.$key.$key.'"><td><b>'.strtoupper($key).':</b></td><td>'."\n";
echo '<select name="type">'."\n";
echo '<option value="desktop">Desktop</option>'."\n";
echo '<option value="tablet">Tablet</option>'."\n";
echo '<option value="laptop">Laptop</option>'."\n";
echo '<option value="netbook">Netbook</option>'."\n";
echo '<option value="server">Server</option>'."\n";
echo '</select></td></tr>'."\n";
}
else if($key == 'touchscreen' && $editTypeT == 'computers'){
echo "\n".'<tr class="'.$key.$key.'"><td><b>'.strtoupper($key).':</b></td><td>'."\n";
echo '<select name="touchscreen">'."\n";
echo '<option value="no">No</option>'."\n";
echo '<option value="yes">Yes</option>'."\n";
echo '</select></td></tr>'."\n";
}
else{
echo "\n".
'<tr class="'.$key.$key.'"><td><b>'.strtoupper($key).':</b></td><td><input type="text" name="'.$key.'" value="'.$value.'" /></td></tr>'
."\n";
}
}
}
}
echo "\n".'<tr><td><b>Notes:</b></td><td><textarea name="notes">'.$getNotes.'</textarea></td></tr>'."\n";
echo '<tr><td colspan="2"><input type="submit" value="Submit" name="submit" /></td></tr>';
echo '</table></form>';
}


The above code works perfectly, so it's got to be some sort of syntax or logic issue with the code in my first post. I can't seem to figure out precisely what it is, however, and it's driving me crazy because this should be a simple thing :p

[djr33]

It's not "an idea"-- it's how the function works. You can omit it, but if you do then PHP just guesses which one it should use.
The odds are that it will work most of the time because the previous connection you used is often the same one you will be using. But in situations like this where you are using two different connections at the same time, relying on that guess is not a good idea.
Also, it is not required that you specify the connection every time, only that you specify it in cases where you need to-- any time you do not specify it, it will default to the latest connection, whatever that is.

[alexjewell]

how do you suggest I rewrite the function?

[djr33]

When you connect each time, store it as a global variable. Be sure you also make this global within any functions where it is used.
Let's say you use $conn1 and $conn2.
In each function that needs these, add to the top:
global $conn1,$conn2;

Now, for at least every use of the mysql_query() function, add a parameter of $conn1 or $conn2:
mysql_query('something',$conn1);

Look on php.net if you have a question about other functions (like mysql_fetch_array()) to see if you can/should add $conn1 to that as well. In most cases I don't think you need to if you've already specified it in mysql_query() because you are referring back to that with $result, etc.