My question is I am using the variable $db in my general script code and within one of my functions. It's purpose is to be the variable that is used for MySQL connections. I have a need inside a function to write some data to the database. In my script I cannot assume that an existing db connection will be open so I open a new one and close it before the function exits. Ever since doing this I am getting an error after the script runs saying the MySQL reference is bad / doesn't exist.
The only thing I can pin it to is in my core code I use the variable $db as the variable name for database connection. I also use the same variable in the function. I did not imagine this would be a problem because I do not use global in front of $db in the function. This should mean the $db I reference in my function is in the functions private scope but it seems to be closing the public $db's connection.
Any thoughts?
Fragments of my code are:
database.php
db_connect()
{
// open mysql db connection and return it;
}
db_close( &$db )
{
// close the passed by reference db connection
}
api.php
api_verify( $keyid, $userid, $key )
{
// open a new db connection
$db = db_connect();
// check for errors. if any errors are found note them in the db
// close the db
db_close($db);
}
main.php
include api.php;
include database.php;
// open a connection to the db
$db = db_connect();
// pull a list of things to process from the db and move through them one at a time
// call api_verify() on each key before working through it's data.
db_close($db)
I assume you are opening a connection to the same database with the same username/password at each of the places you call db_connect. When doing so,unless your db_connect explicitly specifies, that you are creating a new link, it will return an already opened link.If that link is then closed using db_close(), it will also close the other connection, since the link is the same. If you are using mysql_connect to connect to the database, it takes an argument called new link
new_link
If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.
Refer to http://php.net/manual/en/function.mysql-connect.php
I'm not sure if this is the issue you are facing. Hope it helps.
To manage DB connections, you can create a class rather than a pair of functions. If where you say "MySQL reference", the exact error refers to a "MySQL resource", then you are using the outdated mysql extension and should switch to a more modern extension, such as PDO.
class DBConnection {
protected static $_connections = array(),
static connect($dsn) {
if (!isset(self::$_connections[$dsn])) {
$credentials = self::getCredentials();
/* Create connection. For example: */
try {
self::$_connections[$dsn][0] = new PDO($dsn, $credentials['username'], $credentials['password']);
} catch (PDOException $exc) {
// erase the frame w/ password from call trace to prevent leak.
throw new PDOException($exc->getMessage(), $exc->getCode());
}
/* End create connection example */
self::$_connections[$dsn][0]->dsn = $dsn;
}
++self::$_connections[$dsn]['count'];
return self::$_connections[$dsn][0];
}
static close($db) {
if (isset(self::$_connections[$db->dsn])) {
if (--(self::$_connections[$db->dsn]['count']) < 1) {
unset(self::$_connections[$db->dsn]);
}
}
}
static getCredentials() {
/* credentials can be stored in configuration file or script, in this method, or some other approach of your own devising */
}
}
Note that this isn't exactly OOP (it is, but only in a technical sense). The above doesn't lend itself well to unit testing. If you want a more OO approach (which will be more amenable to unit testing), extend or wrap PDO. Using dependency injection can also help with the coupling issues of the above.
I would assume what is happening is the connect cancels out because there already is a connection, and then the close ends the current connection.
I would recommend either A) start a connection at the beginning of the file, and just know it's always there (what I do); or B) check the to see if the $db variable is set, if not then create the connection, and always end the connection at the end of the file.
