Help with my db class

[emehrkay]

i store an instance of the class in a session var. that works fine. for some reason the connection seems to get lost when setting it as a property of the class

 

here is the basic construction of it

 

<?php

class DB{

 

private $_host = 'localhost';

private $_user = 'root';

private $_pass = '';

private $_data = '8trk';

public $_link;

public  $_db;

private $_ct;

 

public function __construct(){

$this->_link = $this->connect();

}

 

public function connect(){

$this->_link = mysql_connect($this->_host, $this->_user, $this->_pass) or die("<h1>Connection Error:</h1>". mysql_error());

$this->_db  = mysql_select_db($this->_data, $link) or die("<h1>Could not Select Database:</h1>". mysql_error());

$this->_mark = 'mark';

}

 

public function runQuery($query = '', $unbuff = ''){

$method = (!$unbuff) ? 'mysql_query' : 'mysql_unbuffered_query';

$result = $method($query );// or die ("<h2>Problem with query</h2>".$query ."<br />".mysql_error().$this->_link);

return $result;

}

}

?>

 

when is store the class in the session, $_SESSION['db'] = new DB();, i have access to all of its properties and methods. If i print_r($_SESSION['db]) it shows all of the properties, but _link has no value and _db has a value of one.

 

when i go to run a query using

 

$query = "SELECT ...";

$_SESSION['db']->runQuery($query);

 

i get the errors

 

 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'ODBC'@'localhost'

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established

 

it basically means that my link to the db is not being carried throughout the class. if i were to add $this->connect() to the query method, t would work fine.

 

What am i doing wrong?

[trq]

You don't need the connect method. Use this as your construct.

 

function __construct(){
    $this->_link = mysql_connect($this->_host, $this->_user, $this->_pass) or die("<h1>Connection Error:</h1>". mysql_error());
    $this->_db  = mysql_select_db($this->_data, $this->_link) or die("<h1>Could not Select Database:</h1>". mysql_error());
}

 

Also, you really out to avaoid outputting anything from within a class. That should be left to your calling code.

[emehrkay]

Thorpe, thanks for yoru reply. I've originally had the connection in my constructor, I moved it out so that i could do

 

if(!$this->_link){

$this->connect();

}

 

inside of the runQuery method. Even with the connection calls in the constructor, i get the same errors.

 

when i print_r($_SESSION['db']) the only time _link equals a Resource#id is if i were to recreate the connection before running the query and that shouldnt be the case because there should already be a connection

 

i only set the session info once so its not like it is getting rewritten. this is confusing me and i am about to give up on it because figuring it out is taking up so much of my productivity

 

i store this instance in a session var so that the user doesnt have to keep recreating a connection to the db evertime a query is run, if i add that check, i am defeating the purpose of what i am trying to do

[utexas_pjm]

I might be wrong here, but I think that in order to store an object in the session it has to be serilializable.  Resources are not serializable, so this may account for the $_link attribute coming back null after being pulled from the $_SESSION array.  It seems like you are trying to implement a persistent connection setup, you might want to have a look at mysql_pconnect: http://www.php.net/mysql_pconnect.  This might expedite your implementation. 

 

Someone please correct if I'm wrong about the serialization thing, it's one of those things that I think I've run into before but can't remember for sure.

 

Best,

 

Patrick

[emehrkay]

utexax, you are absolutely correct. I did a search and it says that you cannot serialize resources.

 

so this turns into a 'how should i handle this?' from 'i need help'

 

using mysql_pconnect(), how could i create one connection and keep it persistent across the session? or is that even possible? if it isnt possible, should i use the singleton design pattern along with the pconnect?

 

thanks

[trq]

The bigger question is why do you need the connection persistsent across a users session? I see no real need for it.

[emehrkay]

The bigger question is why do you need the connection persistsent across a users session? I see no real need for it.

 

i really dont know. i feel that my application will be db-heavy and i dont want to overload it with i/o calls. maybe i am trying to reinvent the wheel with my approach.

 

if i were to use the singleton pattern and a pconnect, i could make sure that when the first query is ran on a page, the DB class only has oene instance of it running.

 

does that make sense? i guess i am trying to reuse the same object and not have multiple ones in memory

[per1os]

To answer the question at hand in order to keep the db class throughout the users session is to set it in a session variable. But remember that you have to define your class before the session_start() call IE:

 

include('yourdbclass.php');
session_start();

if (!isset($_SESSION['dbclass'])) {
     $_SESSION['dbclass'] = new dbClass();
}

$dbclass = $_SESSION['dbclass'];

 

Now note that if you are planning on storing information in the class such as how many queries ran, you need set the session back to the class at the end of your script process ie:

 

$_SESSION['dbclass'] = $dbClass;

 

That will set it up right.

 

Questions let me know.

[redbullmarky]

i've not read all the replies in detail, but from your first post, note this:

 

   public function __construct(){
      $this->_link = $this->connect();
   }
   
   public function connect(){
      $this->_link = mysql_connect($this->_host, $this->_user, $this->_pass) or die("<h1>Connection Error:</h1>". mysql_error());
      $this->_db  = mysql_select_db($this->_data, $link) or die("<h1>Could not Select Database:</h1>". mysql_error());
      $this->_mark = 'mark';
   }

 

in your constructor, you're assigning the RETURN of your connect method to $this->_link - only there isn't anything returned by 'connect'. So in this case, connect sets your $this->link property and then your __construct sets it to a null value. either change the constructor line to simply:

 

$this->connect();

 

or put

 

return $this->_link;

at the end of your connect method.

 

Hope that helps

Cheers

[trq]

if i were to use the singleton pattern and a pconnect, i could make sure that when the first query is ran on a page, the DB class only has oene instance of it running.

 

Using pconnect should be avoided, PHP is actually very efficient with dealing with database connections. I can't rememeber the details exactly but using a pconnect actually results in more connections being held in memory.

[emehrkay]

thanks redbullmarky, that was  a good catch. It was something that i've actually been trying to assign differnet vars to _link. even if i dont assign the return val to _link, it still doesnt exist and it is becuase you cannot serialize link resources.

 

thorpe, thanks for the headup

[utexas_pjm]

Using pconnect should be avoided, PHP is actually very efficient with dealing with database connections. I can't rememeber the details exactly but using a pconnect actually results in more connections being held in memory.

 

The difference is that pconnect creates a connection pool of database connections.  Connection pools are very common in Java apps.  Generally every time a user hits a page which accesses the database a new connection is created and then closed.  A connection pool keeps those connections open for a set period of time so that they can be reused.  This can cause problems, the most common being running out of MySQL Connection (I think the default is 50).  You really only need to use persistent connections when you are running a high traffic site or a site that is connecting to a database on another machine where the time needed to connect is substantial (since the connections are reused). 

 

Best,

 

Patrick

[emehrkay]

I went with the singleton approach.

 

 

my db class

class DB{

private $_host 	= 'localhost';
private $_user 	= 'root';
private $_pass 	= '';
private $_data 	= '8trk';
private $_link;
private $_db;
public static $_instance;

private function __construct(){
	$this->_link = $this->connect();
}

public static function getInstance(){
	if(self::$_instance === null){
		self::$_instance = new DB();
	}
	return self::$_instance;
}

public function connect(){
	$link = mysql_pconnect($this->_host, $this->_user, $this->_pass) or die("<h1>Connection Error:</h1>". mysql_error());
	mysql_select_db($this->_data, $link) or die("<h1>Could not Select Database:</h1>". mysql_error());
}

public function runQuery($query = '', $unbuff = ''){
	$method = (!$unbuff) ? 'mysql_query' : 'mysql_unbuffered_query';
	$result = $method($query );// TRY CATCH HERE!! or die ("<h2>Problem with query</h2>".$query ."<br />".mysql_error().$this->_link);
	return $result;	
}

public function createObj($r = ''){
	$res = ($r == '') ? $result : $r;
	$obj = array();
	$count = mysql_num_rows($res);
	for($i = 0; $i < $count; $i++){
		$row = mysql_fetch_object($res);
		$obj[] = $row;
	}
	return $obj;
}
}
?>

 

Now I have a class full of static functions for each database table, it makes it a lot easier to reuse queries and easily reference them in code table::action();

 

<?php
require_once('_db.php');

class usersSQL{

public static function getAll(){
	$db = DB::getInstance();
	$q = "
		SELECT
			*
		FROM
			users
	";
	$r = $db->runQuery($q);
	return mysql_fetch_array($r, MYSQL_ASSOC);
}
}
?>

 

works well, does what it is suppoed to by creating one instance of the DB class even when called by multiple methods.