DB connection -> I don't think this is the best way.

spstieng

Hi guys.

I have a Data Acces Layer (dal.php) where I connect to the DB and have my SQL queries.

The files looks like this:

  function connect($sql) 
  { 
      $username     = "root"; //database username
      $pwd          = ""; //database password
      $host         = "localhost"; //database location
      $dbname       = "mydatabase"; //database name

  if (!($conn=mysql_connect($host, $username, $pwd)))  { 
     printf("error connecting to DB by user = $username and pwd=$pwd"); 
     exit; 
  }

  $db=mysql_select_db($dbname,$conn) or die("Unable to connect to database!"); 

  $result = mysql_query($sql, $conn)or die("Unable to query local database <b>". mysql_error()."</b><br>$sql"); 

  if (!$result){ 
      echo "database query failed."; 
  }else{ 
      return $result; 
  } 
  }


class storeLocator
{

  public function getNextID()
  {
    $sql = "SELECT LAST_INSERT_ID()";
    $result = connect($sql);
    $row = mysql_fetch_assoc($result);

return $row;
  }
}

To get the result, I do the following from from any php page:

  $sl = new storeLocator();
  $result = $sl->getNextID();
  echo   $result[0];

The problem with using this aproach, or using the PHP equlliant mysql_insert_id(), is that the result always returns 0 (zero).

I was told this is because I always start a new session.
Every time I access the DB, I do a $result = connect($sql);

My questions:
1. Is it possible to only connect to the DB one time, and let the connection stay open?
2. How do I solve the problem that mysql_insert_id() and SELECT LAST_INSERT_ID() always returns 0(zero)?

sneakyimp

If you are expecting a non-zero value to appear from mysql_insert_id when you have not run any queries to insert something, you are always going to be disappointed.

You should also rethink your connect function. It does more than just connect. It connects, runs a query, and then returns the result resource of that query.

Consider rewriting your dal class to have two distinct routines, connect (which connects and selects the db and NOTHING ELSE) and query (which runs the supplied sql query and either returns or stores the result). Your class should probably have a property in which to store the db link. Maybe something like this:

class DAL {
  private $username; // the username for db connect
  private $pwd; // the pwd to use when connecting
  private $host; // the host to which one connects
  private $dbname; // the db to select 

  public $conn; // reference to the db link resource
  public $db; // result of db_select
  public $query_result; // the stored result of the last query you ran

  public function __construct($username_arg, $pwd_arg, $host_arg, $dbname_arg) {
    $this->username     = $username_arg;
    $this->pwd          = $pwd_arg;
    $this->host         = $host_arg;
    $this->dbname       = $dbname_arg;
  }


  function connect() {

  if (!($this->conn=mysql_connect($this->host, $this->username, $this->pwd)))  {
     die("error connecting to DB by user = " . $this->username . " and pwd=" . $this->pwd);
  }

  $this->db = mysql_select_db($this->dbname,$this->conn)
    or die("Unable to connect to database " . $this->dbname);

  } // connect()


  public function query($sql) {
      $this->result = mysql_query($sql, $conn)or die("Unable to query local database <b>". mysql_error()."</b><br>$sql");

  if (!$this->result){
      die("database query failed.");
  } else {
      return $result;
  }
  }

  public function lastID() {
    return mysql_insert_id($this->conn);
  } // lastID()

} class DAL

then you could use it like this:

$myDB = new DAL("root", "", "localhost", "mydatabase");
$myDB->connect(); // your script will die if this fails
$sql = "INSERT INTO some_table (field1, field2, field3) VALUES ('value 1', 'value 2', 'value 3')";
$myDB->query($sql); // this will also die on failure
echo "last insert id:" . $myDB->lastID();

spstieng

Thanks.
It works better if '$this->result' is changed to '$this->query_result' :p

I also think it's wiser to have the username / password in the DAL file, rather than having it multiple times, depending on where we make the connections.

Now.....
I want all my SQL queries to be in the DAL.php file.

So I did this:

class DAL { 
  private $username; // the username for db connect 
  private $pwd; // the pwd to use when connecting 
  private $host; // the host to which one connects 
  private $dbname; // the db to select 

  public $conn; // reference to the db link resource 
  public $db; // result of db_select 
  public $query_result; // the stored result of the last query you ran 

  public function __construct() 
  { 
    $this->username     = "root"; 
    $this->pwd          = ""; 
    $this->host         = "localhost"; 
    $this->dbname       = "myDatabaseName"; 
  } 


  public function connect() 
  { 

  if (!($this->conn = mysql_connect($this->host, $this->username, $this->pwd)))  {
     die("error connecting to DB by user = " . $this->username); 
  } 

  $this->db = mysql_select_db($this->dbname,$this->conn) 
    or die("Unable to connect to database " . $this->dbname); 
  }  


  private function query($sql) 
  {
      $this->query_result = mysql_query($sql, $this->conn)or die("Unable to query local database <b>". mysql_error()."</b><br>$sql"); 

  if (!$this->query_result){ 
      die("database query failed."); 
  } else { 
      return $this->query_result; 
  } 
  } 

/* ---------------------------------------------------------------
                           GENERAL
----------------------------------------------------------------*/

  public function getNextID()
  {
    $sql = "SELECT LAST_INSERT_ID()";
    $row = mysql_fetch_array($this->query($sql));

return $row[0];
  }

}

In my whatever.php file, I have the following code:

<?php
  get_header();

  $myDB = new DAL(); 
  $myDB->connect();
?>

  <div>
    Next ID: <?php echo $myDB->getNextID(); ?>
  </div>

<?php get_footer(); ?>

I hope this is what sneakyimp meant.

BUT, the LAST_INSERT_ID still returns zero.
Even if I do insert new data into the database (through phpMyAdmin).

Some other questions:
1. Should a DB connection be established for each page that is loaded?
2. Should the DB connection be closed in the footer of each page?

spstieng

this post can be deleted. I edited the post above.

sneakyimp

Your getNextID function is only going to return a value if you have run a query to insert something since you have connected to your database. If I run a query that inserts a record and then come back next week and check getNextID, of course it isn't going to return anything.

In this code, all you do is connect -- you don't insert anything:

<?php
  get_header();

  $myDB = new DAL();
  $myDB->connect();
?>

  <div>
    Next ID: <?php echo $myDB->getNextID(); ?>
  </div>

<?php get_footer(); ?>

If you insert some record in phpMyAdmin and then run some script that you wrote and expect insert_id to return anything, you are going to be disappointed. It just doesn't work that way. As for your questions:

1) You should make a db connection when you need to connect to the database to get some data and no more. On the other hand, if you connect to the db and run no queries it's probably not the end of the world, but you are using resources on your server that might be needed elsewhere.

2) It's not critical that you close a database connection that you have opened in a given script (PHP will do it for you when the script completes) but it is good practice to close db connections that are no longer in use and to use mysql_free_result after you have finished working with the results of a query.