I have a function that I use to connect to a database so I can run queries.

function dbconnect(){
	$dbhost = "hostname";
	$dbuser = "username";
	$dbpass = "password";
	$dbname = "database";

$db_conn = mysql_connect($dbhost, $dbuser, $dbpass) 
	or die ("Database CONNECT Error");
mysql_select_db($dbname, $db_conn);

return $dbname;
}

$dbname = dbconnect();

$query = "select * from blah";
$getrecords = mysql_db_query($dbname, $query);

I also want to put in a statement to disconnect from the database which, if the connection were not inside the function, I would close with:

mysql_close($db_conn);

I'm assuming that I cannot close it outside the function because the variable is not returned outside the function. What is the best way to close the connection in this case?

    Hmmmm...

    mysql_db_query is deprecated as of 5.3 and will be eliminated as of 6.

    I've rarely seen this function used in any code I've reviewed and
    I never really liked it exactly because of the problems it presents.

    The reason I bring this up, is that you're returning the database name
    from the function and if you want to accomplish the 'close', you'll need
    to return the connection resource. Of course for simple applications,
    you can just issue the mysql_close() without an argument and
    the last opened connection will be closed.

    In my opinion, the better way to handle this is to abstract out all database
    functionality into a reusable class (or classes) which will make your
    life easier in the long run.

    Here's an example; albeit a bit stubby...

    $db = new Database('localhost', 'me', 'hello', 'mydb');

if ($db->isReady()) {
  $db->query("select * from blah");

  while ($db->hasRecords()) {
    $db->getNextRecord();
    // do something with the record
  }

  $db->close();
}

class Database {

  private $host = '';
  private $user = '';
  private $pwd = '';
  private $db = '';

  private $conn = 0;

  private $query = '';

  function __construct($host, $user, $pwd, $db) {
    $this->host = $host;
    $this->user = $user;
    $this->pwd = $pwd;
    $this->db = $db;
    $this->_connect();
  }

  private function _connect() {

$this->conn = mysql_connect($this->host, $this->user, $this->pwd);

if ($this->conn) {
  mysql_select_db($this->db, $this->conn);
}  
  }

  function isReady() { // Did we get a connection
    return $this->conn ? true : false;
  }

  function query($query) {
    // to implement
  }

  function hasRecords() {
    // to implement
  }

  function getNextRecord() {
    // to implement
  }

  /**
   * More functions...
   *
   *
   */

} // End: class Database {

    I wouldn't necessarily implement the class like
    the above, but it might help get you started.

    I also prefer the mysqli (MySQL Improved Extension)
    or the PHP Data Objects, but both are more complicated
    then the basic mysql_xxxx calls.

    One problem you'll have to solve is where
    the Database class gets its configuration
    information from.

    Will it be hard-coded?

    Will configuration files be used?

    Will it just be passed in?

    Etc....

    I've shown a simple implementation where that
    information is passed in when the Database class
    object is constructed.

    Hope that helps...

      Thanks! You've given much food for thought. It's already given me a host of ideas.

        Write a Reply...