Basic MySQL database connection using OOP

[j.smith1981]

Hi there, I am having a bit of trouble understanding what I am doing, basically all I want at the moment is to be able to make a very basic introductory guide for myself on my own Database class implementation.

 

I know there are some around on the web but I thought for my own education I would create one of my own, just to see if I can fully understand what it is I am doing.

 

It won't at this present moment involve anything more than a standard class, with 3 properties:

 

protected $_connect

This will be the basis for mysql_connect stuff.

 

protected $_query

As you probably would have guessed the basis for the mysql_query strings this application will do.

 

protected $_error

Then finally any errors that occur will go through this function, right thats the properties done for now (please feel free to add any should you feel I should need them though, why I am asking for your help  ).

 

Right here is my code, it is all on the same file, say class.db.php or something simple, just to see how I can get this working firstly, then I will segregate (or more split the new Db class into its own file), then have my actual form generation going from another class file and then do a real implement of the whole thing.

 

Again this is only for my own education, nothing else!

 

Here is my class file for the database:

class Db {

protected $_connect; // dont allow credentials in here yet!
protected $_query; // holds the query info?
protected $_error; // raises an error message when needed!

public function __construct($host,$username,$password) {

	$this->_connect = mysql_connect($host,$username,$password);

}

}

$newDatabase = new Db("localhost","dbuser","password");

echo "<pre>";
print_r($newDatabase);
echo "</pre>";

 

What it's bringing up though is along the lines of this:

Db Object

(

    [_connect:protected] => Resource id #2

    [_query:protected] =>

    [_error:protected] =>

)

 

Is there any obvious error I am doing to not allowing the properties to fill up or something?

 

I cant seem to get this working, again any helps appreciated,

Jeremy.

[j.smith1981]

Actually scratch that, reason why its saying resource ID is because its connecting lol.

 

But is there by any means improving that, apart from maybe making an if statement to check against the connection string like if (!$connect) then call a private function which would call an error function, I can then use throughout my application?

 

Just out of interest.

 

Sorry for being a bit of a douchebag lol,

Jeremy.

[PFMaBiSmAd]

Methods can return values, but the constructor only returns the instance of the object that was created.

 

There are two common ways that you could make the connection error information available -

 

1) Don't make the connection in the constructor. Make a separate method (connect() as an example) and then call that method as a separate step and have that method return a TRUE or FALSE value. You would store the actual error information in your $_error property. This would allow you to test the value returned by the connect() method.

 

2) Make the connection in the constructor and set a connect_error property with a TRUE or FALSE value and store the actual error information in your $_error property. You would then get and test the connect_error properly.

[j.smith1981]

Ah yes of course, but going off the theory you used, would this be suffice do you know?

 

I mean it works the way I would expect it for now of course, but as a guide for myself (so I don't end up going down a route thats a bad habbit so to speak), I have created this so far:

 

class Db {

protected $_connect; // dont allow credentials in here yet!
protected $_query; // holds the query info?
protected $_error; // raises an error message when needed!

public function connect($host,$username,$password,$name) { // must put database name here, unfortunately

	$this->_connect = mysql_connect($host,$username,$password); // $connect will then carry a value but we need to set this to a value to check against?
	if(!$this->_connect){

		$this->error("Failed to connect at line 14");
	} else {
		// select database somehow?
		$selectDb = mysql_select_db($name,$this->_connect);
		if(!$selectDb) {
			$this->error("Cannot select database on line 17");
		}
	}

}

private function error($errorMessage)
{
	$this->_error = $errorMessage;
	echo $this->_error;
}

public function query($sql)
{
	$this->_query = mysql_query("");
	if(!$this->_query){
		$this->error("Failed to connect at line 29 on querying database");
	}
}

}

$newDatabase = new Db();

$newDatabase->connect('localhost','myuser','mypassword','dbname');

 

I am quite impressed with myself, can get why to use this through my own work, going to keep further developing etc.

 

I look forward to your next reply,

Jeremy.

[PFMaBiSmAd]

echo $this->_error;

 

^^^ Your method should return the error, not echo it. What if you want to style it a particular way in one application and style it a different way in another application and/or you want to log it to a file instead of outputting it to the browser? Your application code that calls the method should decide what to do with the error message. Your method should just return the string.

[j.smith1981]

Ah yes of course, keeping presentation as seperate from the logic as possible.

 

I have remove the echo and put return but having a bit of trouble understanding how to output the error.

 

Hmmm.

[j.smith1981]

Sorry I am doing it again, lol.

 

I have it connecting correctly so the error property won't of course fill up, I am so sorry.

 

I will get the hang of this eventually I promise!

 

I deleted the first char out of the database select arguement and its now filling up saying it cant select the database, well just a database error on line such and such.

 

Thanks, like so:

 

class Db {

protected $_connect; // dont allow credentials in here yet!
protected $_query; // holds the query info?
protected $_error; // raises an error message when needed!

public function connect($host,$username,$password,$name) { // must put database name here, unfortunately

	$this->_connect = mysql_connect($host,$username,$password); // $connect will then carry a value but we need to set this to a value to check against?
	if(!$this->_connect){

		$this->error("Failed to connect at line 14");
	} else {
		// select database somehow?
		$selectDb = mysql_select_db($name,$this->_connect);
		if(!$selectDb) {
			$this->_error = $this->error("Cannot select database on line 17");
		}
	}

}

private function error($errorMessage)
{
	$this->_error = $errorMessage;
	return $this->_error; // never echo it!
}

public function query($sql)
{
	$this->_query = mysql_query("");
	if(!$this->_query){
		$this->error("Failed to connect at line 29 on querying database");
	}
}

}

$newDatabase = new Db();

$newDatabase->connect('localhost','mysuer','mydbpassword','wrongdatabase');
print_r($newDatabase);

 

Brings up:

 

Db Object

(

    [_connect:protected] => Resource id #2

    [_query:protected] =>

    [_error:protected] => Cannot select database on line 17

)

 

Is this the best way of doing this at all?

 

Thanks and again apologies for that, not having a terribly good day today, but once I have this nailed should be relatively easy the rest of this task I am trying to do.

 

Thanks again and I look forward to your reply,

Jeremy.

[j.smith1981]

Ok I have done this but a bit worried should I be using the what was a private propery error and now public like so:

 

class Db {

protected $_connect; // dont allow credentials in here yet!
protected $_query; // holds the query info?
//	protected $_error; // raises an error message when needed!
public $_error; // raises an error message when needed!

public function connect($host,$username,$password,$name) { // must put database name here, unfortunately

	$this->_connect = mysql_connect($host,$username,$password); // $connect will then carry a value but we need to set this to a value to check against?
	if(!$this->_connect){

		$this->error("Failed to connect at line 14");
	} else {
		// select database somehow?
		$selectDb = mysql_select_db($name,$this->_connect);
		if(!$selectDb) {
			$this->_error = $this->error("Cannot select database on line 17");
		}
	}

}

private function error($errorMessage)
{
	$this->_error = $errorMessage;
	return $this->_error; // never echo it!
}

public function query($sql)
{
	$this->_query = mysql_query("");
	if(!$this->_query){
		$this->error("Failed to connect at line 29 on querying database");
	}
}

}

$newDatabase = new Db();

$newDatabase->connect('localhost','myuser','mypassword','myinvalid');
echo "<pre>";
print_r($newDatabase);
echo "</pre>";

if($newDatabase->_error != ''){
printf("%s",$newDatabase->_error);
}

[ignace]

$db = Db::factory('mysql://user:pass@host/database'); // get database specific class
$db->throwsExceptions(false); // tell the $db object not to throw exceptions
$result = $db->query('ERROR'); // query the database, uses Lazy-Loading to connect to the database
if($result->isException()) { // were we able to query the database?
    echo $result->getMessage(); // why not?
}

$db->throwExceptions(true);
try {
    $result = $db->query('RORRE');
} catch(Exception $e) {
    echo $e->getMessage();
}

[j.smith1981]

I wanted to do it the hard way (as I always do), so that when it comes to doing it using Libraries, I can appreciate why they are there.

 

So I plugged on with what I was doing, took me a while to do but here goes nothing:

 

<?php
class Database {

protected $_host;
protected $_user;
protected $_password;

protected $_connection;
protected $_error;

public function __construct($host,$user,$password)
{
	if($host != '' && $user != '' && $password != '')
	{
		$this->_host = $host;
		$this->_user = $user;
		$this->_password = $password;

		$connect = mysql_connect($this->_host, $this->_user, $this->_password);

		$this->_connection = false;

		if($connect)
		{
			$this->_connection = true;
		} else {
			$this->_error = $this->error("Connection to database server failed check connection!");
		}

	}
}

public function getConnection()
{
	return $this->_connection;
}

private function error($error)
{
	return $error;
}

public function getError()
{
	return $this->_error;
}


}

$myDatabase = new Database('localhost','mydbuser','mypass');

$connect = $myDatabase->getConnection();
if(!$connect)
{
$error = $myDatabase->getError();

} else {

}

 

Without using any libs would there be anyway of improving this at all?

[ignace]

Without using any libs would there be anyway of improving this at all?

 

You could take a look at the other libs for inspiration DB is so common that the implementation can almost be called a pattern.

[j.smith1981]

Yes thats fair enough.

 

Going to keep going with what I am doing, doing some basic queries to the database and showing them.

 

Want to get the logic sorted in my head, seperate the 3 layers out, just for the fun of it and then appreciate why we have libs/frameworks in the first place.

 

Thanks for both your replies, learnt allot, can't understand why I didnt grasp the concept of the first example of protected properties and outputting them before lol, oh well makes sense now.

 

Thanks again to both of you,

Jeremy.