Building a MySQL Class Using OOP

on Sat Jul 05 08:35:17 GMT 2008 in PHP and viewed 10060 times

How to use object oriented programming in php to create a class to make interacting with MySQL databases much easier.

OOP, as I have mentioned, is a quite useful concept. And different softwares use it to manage the MySQL work. An example is Invision Power Board. To start out, you must create your own class. I called mine “mysql”. It doesn’t really matter.

  class mysql{

  }

Ok. There’s one down. Now we need some functions. One will open the mysql connection, one close it.

  class mysql {

  function Connect($host, $name, $pass, $db){

  $connection = mysql_connect("$host",
  "$name",
  "$pass");
  mysql_select_db("$db", $connection);

  }

  function Close(){

  mysql_close($this->connection);

  }

  }

There. Now you have an easy connection when you instantiate the class. If you want to get fancy and have PHP5, you could make it connect on instantiation, but we aren’t that cool. So next we need to make a function to query the database.

  class mysql {

  function Connect($host, $name, $pass, $db){

  $connection = mysql_connect("$host",
  "$name",
  "$pass");
  mysql_select_db("$db", $connection);

  }//ends the connection function

  function Close(){

  mysql_close($this->connection);

  }//ends the close function

  function Query($sql){
  $query = mysql_query($sql) or die(mysql_error());
  return $query;
  }//ends the query function

  }//ends the class

Now you have a much easier way of doing queries. Like instead of writing everything out, you could say:

  $DB = new mysql();
  $sql = "SELECT * FROM table";
  $query = $DB->Query($sql);

Easy, eh?

Next I’ll just add some quick functions and present the fully done class with all of the really needed functions.

  class mysql {

  function Connect($host, $name, $pass, $db){

  $connection = mysql_connect("$host",
  "$name",
  "$pass");
  mysql_select_db("$db", $connection);

  }//ends the connection function

  function Close(){

  mysql_close($this->connection);

  }//ends the close function

  function FetchRow($query){
  $rows = mysql_fetch_row($query);
  return $rows;
  }

  function FetchArray($query){
  $array = mysql_fetch_array($query);
  return $array;
  }

  function FetchNum($query){
  $num = mysql_num_rows($query);
  return $num;
  }

  function Query($sql){
  $query = mysql_query($sql) or die(mysql_error());
  return $query;
  }//ends the query function

  }//ends the class

Now we have all of the basic functions. mysql_fetch_row(), mysql_fetch_array(), mysql_query(), and mysql_num_rows() are all covered. Each function will take the query variable to execute the function except for Query(), which will take an SQL string stored in a variable. To illustrate some of these functions, we can see a test page:

  include("mysql_class.php");

  $DB = new mysql();

  $host = "localhost";
  $name = "username";
  $pass = "password";
  $db = "dbname";

  $connection = $DB->Connect($host, $name, $pass, $db);

  //define an SQL statement and execute it
  $sql = "SELECT title,author FROM news";
  $query = $DB->Query($sql);

  //fetch a single row and output it
  $newsrow = $DB->FetchRow($query);
  $title = $newsrow[0];
  $author = $newsrow[1];
  echo "<b>Single Row</b><br /><br />Title: $title<br>
  Author: $author<br><br>";

  //output all rows from the statement
  while($array = $DB->FetchArray($query)){
  extract($array);
  echo "<b>All rows</b><br /><br />Title: $title<b>
  Author: $author<br />";
  }

  //find the number of rows
  $num = $DB->FetchNum($query);
  echo "Number of rows: $num";

  //close the connection
  $DB->Close();

So we first instantiate our mysql class into the variable $DB. Then we can define our variables to connect to the database. Then we have a basic SQL statement and execute it into $query. We then can use the different functions as defined in the class, like finding a single row, fetching an array, and finding the number of rows. We can then disconnect from the database and that’s done.