# Real-World OOP With PHP and MySQL

Numerous examples from robots to bicycles have been offered as “easy” explanations of what OOP is. I’ve opted to show you how OOP works with a real-life example, for a programmer. By creating a MySQL CRUD class you can easily create, read, update and delete entries in any of your projects, regardless of how the database is designed.

Setting up the skeleton of our class is fairly simple once we figure out exactly what we need. First we need to make sure that we can do our basic MySQL functions. In order to do this, we need the following functions:

• Select
• Insert
• Delete
• Update
• Connect
• Disconnect

Those seem pretty basic, but I’m sure that as we go through, we’ll notice that a lot of them utilize some similar aspects, so we may have to create more classes. Here is what your class definition should look like. Notice that I made sure that the methods were created with the public keyword.

 1 class Database  2 {  3  public function connect() { }  4  public function disconnect() { }  5  public function select() { }  6  public function insert() { }  7  public function delete() { }  8  public function update() { }  9 } 

### function connect()

This function will be fairly basic, but creating it will require us to first create a few variables. Since we want to make sure that they can’t be accessed from outside our class, we will be setting them as private. These variables will be used to store the host, username, password and database for the connection. Since they will pretty much remain constant throughout, we don’t even need to create modifier or accessor methods for it. After that, we’d just need to create a simple mysql statement to connect to the database. Of course, since as programmers we always have to assume the user (even if it is us) will do something stupid, lets add an extra layer of precaution. We can check if the user has actually connected to the database first, and if they have, there really isn’t a need to re-connect. If they haven’t then we can use their credentials to connect.

 1 private db_host = ‘’;  2 private db_user = ‘’;  3 private db_pass = ‘’;  4 private db_name = ‘’;  5 6 public function connect()  7  {  8  if(!$this->con)  9  {  10 $myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);  11  if($myconn)  12  {  13  $seldb = @mysql_select_db($this->db_name,$myconn);  14  if($seldb)  15  {  16  $this->con = true;  17  return true;  18  } else  19  {  20  return false;  21  }  22  } else  23  {  24  return false;  25  }  26  } else  27  {  28  return true;  29  }  30  }  As you can see, it makes use of some basic mysql functions and a bit of error checking to make sure that things are going according to plan. If it connects to the database successfully it will return true, and if not, it will return false. As an added bonus it will also set the connection variable to true if the connection was successfully complete. ### public function disconnect() This function will simply check our connection variable to see if it is set to true. If it is, that means that it is connected to the database, and our script will disconnect and return true. If not, then there really isn’t a need to do anything at all.  1 public function disconnect()  2 {  3  if($this->con)  4  {  5  if(@mysql_close())  6  {  7  $this->con = false;  8  return true;  9  }  10  else  11  {  12  return false;  13  }  14  }  15 }  ### public function select() This is the first function where things begin to get a little complicated. Now we will be dealing with user arguments and returning the results properly. Since we don’t necessarily want to be able to use the results right away we’re also going to introduce a new variable called result, which will store the results properly. Apart from that we’re also going to create a new function that checks to see if a particular table exists in the database. Since all of our CRUD operations will require this, it makes more sense to create it separately rather than integrating it into the function. In this way, we’ll save space in our code and as such, we’ll be able to better optimize things later on. Before we go into the actual select statement, here is the tableExists function and the private results variable.  1 private$result = array();  2 3 private function tableExists($table)  4  {  5 $tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');  6  if($tablesInDb)  7  {  8  if(mysql_num_rows($tablesInDb)==1)  9  {  10  return true;  11  }  12  else  13  {  14  return false;  15  }  16  }  17  } 

This function simply checks the database to see if the required table already exists. If it does it will return true and if not, it will return false.

 1 public function select($table,$rows = '*', $where = null,$order = null)  2  {  3  $q = 'SELECT '.$rows.' FROM '.$table;  4  if($where != null)  5  $q .= ' WHERE '.$where;  6  if($order != null)  7 $q .= ' ORDER BY '.$order;  8  if($this->tableExists($table))  9  {  10 $query = @mysql_query($q);  11  if($query)  12  {  13  $this->numResults = mysql_num_rows($query);  14  for($i = 0;$i < $this->numResults;$i++)  15  {  16  $r = mysql_fetch_array($query);  17  $key = array_keys($r);  18  for($x = 0;$x < count($key);$x++)  19  {  20  // Sanitizes keys so only alphavalues are allowed  21  if(!is_int($key[$x]))  22  {  23  if(mysql_num_rows($query) > 1)  24 $this->result[$i][$key[$x]] =$r[$key[$x]];  25  else if(mysql_num_rows($query) < 1)  26 $this->result = null;  27  else  28  $this->result[$key[$x]] =$r[$key[$x]];  29  }  30  }  31  }  32  return true;  33  }  34  else  35  {  36  return false;  37  }  38  }  39 else  40  return false;  41  } 

While it does seem a little scary at first glance, this function really does a whole bunch of things. First off it accepts 4 arguments, 1 of which is required. The table name is the only thing that you need to pass to the function in order to get results back. However, if you want to customize it a bit more, you can do so by adding which rows will be pulled from the database, and you can even add a where and order clause. Of course, as long as you pass the first value, the result will default to their preset ones, so you don’t have to worry about setting all of them. The bit of code right after the arguments just serves to compile all our arguments into a select statement. Once that is done ,a check is done to see if the table exists, using our prior tableExists function. If it exists, then the function continues onwards and the query is performed. If not, it will fail.

The next section is the real magic of the code. What it does is gather the columns and data that was requested from the database. It then assigns it to our result variable. However, to make it easier for the end user, instead of auto-incrementing numeric keys, the names of the columns are used. In case you get more than one result each row that is returned is stored with a two dimensional array, with the first key being numerical and auto-incrementing, and the second key being the name of the column. If only one result is returned, then a one dimensional array is created with the keys being the columns. If no results are turned then the result variable is set to null. As I said earlier, it seems a bit confusing, but once you break things down into their individual sections, you can see that they are fairly simple and straightforward.

### public function insert()

This function is a lot simpler than our prior one. It simply allows us to insert information into the database. As such we will require an additional argument to the name of the table. We will require a variable that corresponds to the values we wish to input. We can simply separate each value with a comma. Then, all we need to do is quickly check to see if our tableExists, and then build the insert statement by manipulating our arguments to form an insert statement. Then we just run our query.

 1 public function insert($table,$values,$rows = null)  2  {  3  if($this->tableExists($table))  4  {  5 $insert = 'INSERT INTO '.$table;  6  if($rows != null)  7  {  8  $insert .= ' ('.$rows.')';  9  }  10 11  for($i = 0;$i < count($values);$i++)  12  {  13  if(is_string($values[$i]))  14  $values[$i] = '"'.$values[$i].'"';  15  }  16  $values = implode(',',$values);  17  $insert .= ' VALUES ('.$values.')';  18  $ins = @mysql_query($insert);  19  if($ins)  20  {  21  return true;  22  }  23  else  24  {  25  return false;  26  }  27  }  28  }  As you can see, this function is a lot simpler than our rather complex select statement. Our delete function will actually be even simpler. ### public function delete() This function simply deletes either a table or a row from our database. As such we must pass the table name and an optional where clause. The where clause will let us know if we need to delete a row or the whole table. If the where clause is passed, that means that entries that match will need to be deleted. After we figure all that out, it’s just a matter of compiling our delete statement and running the query.  1 public function delete($table,$where = null)  2  {  3  if($this->tableExists($table))  4  {  5  if($where == null)  6  {  7  $delete = 'DELETE '.$table;  8  }  9  else  10  {  11  $delete = 'DELETE FROM '.$table.' WHERE '.$where;  12  }  13 $del = @mysql_query($delete);  14 15  if($del)  16  {  17  return true;  18  }  19  else  20  {  21  return false;  22  }  23  }  24  else  25  {  26  return false;  27  }  28  } 

And finally we get to our last major function. This function simply serves to update a row in the database with some new information. However, because of the slightly more complex nature of it, it will come off as a bit larger and infinitely more confusing. Never fear, it follows much of the same pattern of our previous function. First it will use our arguments to create an update statement. It will then proceed to check the database to make sure that the tableExists. If it exists, it will simply update the appropriate row. The hard part, of course, comes when we try and create the update statement. Since the update statement has rules for multiple entry updating (IE – different columns in the same row via the cunning use of comma’s), we will need to take that into account and create a way to deal with it. I have opted to pass the where clause as a single array. The first element in the array will be the name of the column being updated, and the next will be the value of the column. In this way, every even number (including 0) will be the column name, and every odd number will be the new value. The code for performing this is very simple, and is presented below outside the function: