PHP static $link from db function to db object for transactional queries

Question

I have multiple functions, each running their own SQL query that needs to be inside a transaction... I'm using a static $link to save having to pass around links between functions... for example:

function db() {
 $user = "username";
 $pass = "password";
 $db = "database";
 $server = "localhost";
 static $link;

 if(is_null($link)){
  $link = @mysql_connect( $server, $user, $pass );
 }

mysql_select_db( $db, $link );
return $link;
}

function transactionWrapper($id){
    $link = db();
    # Start transaction
    mysql_query("SET autocommit=0",$link);
    # Get name from other function, but keep this function within the ACID transaction by using the same link
    $name = getName($id);
    mysql_query("UPDATE tbl2 SET name = '{$name}' WHERE id = '2'",$link);
    # Commit transaction
    mysql_query("COMMIT",$link);
}


function getName($id){
    $link = db();
    $result = mysql_query("SELECT name FROM user WHERE id = '{$id}'",$link);
    return mysql_result($result,0,0);
}

This works brilliantly at the moment... I can have multiple function calls within different files and not have to pass around the $link.

The problem is now I want to do everything in an object for exception handling and I don't know how to replicate the same static variable upon multiple object instances...

I thought it would be something like:

class db{
    static $link;

    function db(){
        # if link is null, create it with mysql_connect, otherwise return the link
    }

}

The problem is... a static variable within a function stays alive for an entire page load, but the static link within an object only exists within the object right?

pconnect isn't an option either :P messy stuff

So how can I get around this? using Objects. I couldn't really find anything after googling for so long so I kind of get the impression I'm doing things a little differently to other people. Inside my transaction I have loads of function calls for various things.

I have alot of code as well... about a year of 60 hrs a week so I didn't have an entire application recode in mind! :P

Thanks, I hope someone can help me and I hope someone else stumbles upon this question in the future if it's answered!

Answer 1

If you declare your object at the start of your script (i.e, as a global), it should be alive for as long as your script runs. This is what my basic SQL class looks like:

class SQL_Connection {

    var $connection;


    function __construct() {

        $this->connection = mysql_pconnect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());

        mysql_select_db(DB_TABLE, $this->connection) or die(mysql_error());

    }

    function query($query){

        return mysql_query($query, $this->connection);   

    }



}

Then somewhere in you script you would do:

$db = new SQL_Connection;