PHP: Mysql Database Wrapper Class

Posted: December 9, 2011 in PHP, PHP, Programming, Tutorial, Web Development
Tags: , , , , ,

Out of boredom I decided to create a very simple wrapper class for mysql database management. This class, as I mentioned, is extremely simple. However, for those wishing to learn more about Object Oriented programming, and how to implement a class that is useful, this is a good place to start. Anyone wishing to learn more about OOP could take this basic class, and make it into something that is much more useful.

Note: This class is untested. I haven’t actually ran the code, but I reviwed it to make sure any simple syntax/conceptual errors were removed.

If there is a syntax error or anything you see, leave a comment below so I can fix it!

Functionality

The class has very limited functionality. It basically provides normal mysql functionality (like querying, fetching rows, and fetching all the rows of a result set) and has a built in sanitizing function.

Improvements

This class is by no means complete or even useful. However, there are many improvements that someone could add to this that would not only make this class fairly useful, but is also great practice. Some obvious improvements could be as follows:

  • function to build and execute a query based on function parameters (like table, what to select, and where/order by statements(s)
  • an insert function that builds and executes a query based on parameters (table to insert, array of columns, and an array of corresponding values
  • stronger validation/sanitization

There are many many more things you could add to this class. A little competition for the readers: Submit your version of the class below with added features. The best/coolest class will get a free page with links/advertisement to their website (or a website of their choice)


<?php
class DB_manager {
 private $database;//database you wish to connect to
 private $username;//mysql username
 private $password;//myysql password
 private $server;//mysql server to connect to
 private $link;//variable to hold mysql connection link
 private $currentResult = false;

 private $query;

 //simple error reporting function.
 private function reportError($error){
 echo $error;
 }

 //constructor
 public function __construct($username, $password, $server="localhost"){
 $this->server = $server;
 $this->username = $username;
 $this->password = $password;
 }

 //connect
 public function connect($database){
 $this->link=mysql_connect($this->server,$this->user,$this->pass);
 //make sure we connected correctly
 if (!$this->link) {//open failed
 $this->reportError("Could not connect to server: <b>$this->server</b>.");
 }
 $this->database = $database
 if(!@mysql_select_db($this->database, $this->link)) {//no database
 $this->reportError("Could not open database: <b>$this->database</b>.");
 }
 }

 //run query q
 public function query($q){
 $this->query = $this->string_escape($q);
 $this->currentResult = mysql_query($q);
 if (!$this->currentResult){
 $this->reportError("There was an error with your query: " . mysql_error();
 $this->currentResult = false;
 return false;
 }
 return $this->currentResult;
 }
 //returns row from the result a query returned
 public function fetch_row($assoc = true){
 if (!$this->currentResult){
 $this->reportError("There is no valid result set to return data from.");
 return false;
 }

 if ($assoc)
 return mysql_fetch_assoc($this->currentResult);
 else
 return mysql_fetch_array($this->currentResult);

 }

 public function string_escape($s){
 return mysql_real_escape_string($s);
 }

 public function fetch_all($assoc = true){
 if (!$this->currentResult)
 $this->reportError("There is no valid result set to return data from.");
 return false;
 }

 $array = [];
 if ($assoc)
 while ($row = mysql_fetch_assoc($this->currentResult)){
 $array[] = $row;
 }
 }
 else {
 while ($row = mysql_fetch_array($this->currentResult)){
 $array[] = $row;
 }
 }

 return $array;
 }
}

//usage
$db = new DB_Manager('username', 'password', 'server (usually localhost');
$db->connect("databse_name");

$db->query("SELECT * FROM sometable");

$rows = $db->fetch_all();

foreach($rows as $r){
 print_r($r);
}

&nbsp;

&nbsp;

?>

If you have any questions, leave a comment!

Comments
  1. HeftyByte says:

    Nice class. Theres one problem I see here. The query function automatically escapes the whole SQL statement. If the statement used quotes for the table name or field names in an INSERT statement then those would be escaped, making the query invalid. Some database classes I’ve seen make escaping easier by allowing you to put a token in place of the value and then passing in the value in another parameter.

    For example:

    $query = “SELECT * FROM “.DB_PREFIX.”users WHERE username = %s AND active=%i “;

    %s means that a string needs to be escaped and %i menas that an int needs to be validated

    the values would be passed in consecutive order
    $db->query($query, “myUsername”, 1);

    Theres a cool “Unframework” that has a lot of helper classes like this. http://flourishlib.com/docs very useful.

    • Thanks for the feedback! I’m not sure what you mean when you say: “If the statement used quotes for the table name or field names in an INSERT statement then those would be escaped, making the query invalid.” Could you perhaps provide an example of a query that would become invalid?

      However, you advice is interesting. I too have seen what you describe quite a bit actually, and I must say it would definitely be an improvement! The point of this class was the basically get newer programmers to think about different/better ways to implement this class, and the concept of your alteration is definitely an improvement!

      One question I have as far as the actual implementation of your change however. How will you determine where to replace the actual values with the tokens? The problem with using the % character is that % is a SQL operator, so what do you do when you have one of those in your query? For example, if my query is something like

      $q = “SELECT * FROM table WHERE some_column LIKE ‘%s%'”;

      and our method call could be

      $db->query($q, “some string search value”);

      In this case, S is what you want to replace, however, do we replace just the S? Do we replace the %s? In either case, the query is different from what was intended.

      Obviously there is a solution, but it wouldn’t be a trivial solution, and may take a bit of doing. However, I do like the idea, and I think it would be worth analyzing and finding the solution to this small problem. You could use the sprintf function as a sort of workaround, but things still get somewhat complicated when you want to use the LIKE operator, and this also requires the programmer using the class to be aware of the fact that sprintf is being used, so this may not be the best option.

      Obviously, the simplest work around would probably to use a different token character instead of % (perhaps #, or another punctuation character) to get around the problem with the LIKE operator, but there is still the problem of determining whether the character is one the programmer wanted to be replaced, or one the programmer wants to be part of the string literal. For example, if we use #, our query may be

      $q = “SELECT * FROM table WHERE varchar_column = ‘#s’ AND int_column=#i”;
      our function call COULD be one of the following
      $db->query($q, “value to replace”, 5);
      $db->query($q, 5);//here we only replace the integer

      However, how will we know whether the programmer wanted to have the #s replaced with some string, or he literally wanted the select the row with a value of #s for the column `varchar_column`. As I mentioned earlier, here is definitely a solution, but it is not a trivial solution by any means.

      What is your opinion on this? How would you go about solving the problem I mentioned?

      Again, thank you very much for the feedback, and I hope this class was helpful to you as a learning tool! Hope to see you comment again 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s