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)

This depends on your connect code. If you're actually giving the connection from a pool, and one of your inner methods close the connection, the connection that one of your outer methods is pointing to may be stale — JohnP
– JohnP, Commented Apr 24, 2011 at 7:12
Dont close the connection... if you open a new connection with the same parameters it just reuses the already opened connection, so if you close that connection in your api function you close it everywhere - even if you dont pass by reference — prodigitalson
– prodigitalson, Commented Apr 24, 2011 at 7:15
i guess it is just my paranoia. i open a fresh connection inside the function because i didn't want to depend on a global db connection being open already and i close it because i am a fan of closing or deallocating resources i open or create. — Mr. Lost IT Guy
– Mr. Lost IT Guy, Commented Apr 24, 2011 at 7:21
Alright, I did some more testing with my code. It seems when you use built-in mysql_connect and mysql_close functions it doesn't matter what you name the connection variable. If you close the connection to the mysql server with mysql_close it closes all connections to that particular server. Kind of annoying that you cannot create independent connections to the same DB server and close them based on the object you created them with. Either way I am now assuming that the $db object is created. — Mr. Lost IT Guy
– Mr. Lost IT Guy, Commented Apr 24, 2011 at 7:23

outis · Accepted Answer · 2011-04-26 19:32:26Z

2

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.

edited Apr 26, 2011 at 19:32

answered Apr 24, 2011 at 7:34

outis

77.7k23 gold badges155 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr. Lost IT Guy Over a year ago

I like your view of using a class. Not a bad idea at all =).

outis Over a year ago

@Mr. Lost: private state+functions = OOP (or functional programming, but that requires PHP 5.3 or better, and the result is still OOP).

kushaldsouza · Accepted Answer · 2011-04-24 07:28:36Z

1

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.

answered Apr 24, 2011 at 7:28

kushaldsouza

7085 gold badges12 silver badges37 bronze badges

1 Comment

Mr. Lost IT Guy Over a year ago

Great Job! This is what I was looking for. I scrounged google and php.net myself but those of you that know it is really the best resource!

Jess · Accepted Answer · 2011-04-24 07:15:18Z

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.

answered Apr 24, 2011 at 7:15

Jess

8,7306 gold badges51 silver badges68 bronze badges

Collectives™ on Stack Overflow

PHP, Variable Scope Question

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related