PHP, Variable Scope Question

Question

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)

Answer 1

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.

Answer 2

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.

Answer 3

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.