PHP lessons - MySQL class example

<?php
class mysqldb {
//set up the class
var $dbhost;
var $db;
var $dbuser;
var $dbpassword;
var $sql;
var $result;
var $numberrows;
var $dbconnection = false;
var $insert_id;

function get_insert_id(){ $this->insert_id=mysql_insert_id(); return $this->insert_id;}

function getdb(){return $this->db;}

function setdb($req_db){$this->db = $req_db;}

function setdbuser($req_user){$this->dbuser = $req_user;}

function setdbpassword($req_password){$this->dbpassword = $req_password;}

function getsql(){return $this->sql;}

function setsql($req_sql) {$this->sql = $req_sql;}

function getnumberrows() {return $this->numberrows;}

function setnumberrows($req_numberrows) {$this->numberrows = $req_numberrows;}

function setdbconnection($req_dbconnection){$this->dbconnection = $req_dbconnection;}

function closedbconnection(){
if($this->dbconnection=$TRUE) mysql_close($this->dbconnection);
}

function real_escape($string) {
return mysql_real_escape_string($string,$this->dbconnection);
}

function mysqldb(){
$HOST           =    "localhost";
$DB             =    "your_db_name";
$WEBUSER        =    "your_mysql_username";
$WEBPASSWORD    =    "your_mysql_password";
$this->setdb($DB);
$this->setdbuser($WEBUSER);
$this->setdbpassword($WEBPASSWORD);
$this->opendbconnection();
}

function opendbconnection(){
$this->dbconnection=mysql_connect("$this->dbhost","$this->dbuser","$this->dbpassword");
if ($this->dbconnection)//if we have connected select and return true
{
mysql_select_db($this->db,$this->dbconnection) or die("Unable to select database");
}
else {$this->dbconnection=false;}

// unset the data so it couldn't be dumped
$this->dbhost='';
$this->db='';
$this->dbuser='';
$this->dbpassword='';
}

function selectquery(){
$this->qry=@mysql_query($this->sql,$this->dbconnection);
if(!$this->qry){$this->numberrows=0; return false;}//query error

else{//query passed
$this->numberrows=@mysql_numrows($this->qry);
//if we have any result fill in the result array
if($this->numberrows>=0) {
for($x=0;$x<$this->numberrows;$x++){$this->result[$x]=@mysql_fetch_array($this->qry);} return true; }  else{$this->numberrows=0; return false;}//if we don't have results give error
}//end query passed
}
}//end of class mysqldb  
?>  

Here is the explanation of the code: As you can see from the first few lines we are declaring a class. It's name is mysqldb and will hold all our database handling functions such as: opendbconnection(), selectquery(), etc... As you know in Object Oriented Programming these functions are called member functions, because when we create a new 'member', 'object', 'instance'(synonyms in OOP) from this class those functions will automatically attach and belong to the new member.

There are also member variables such as: $dbhost, $db, etc... They are helpful because they are used to exchange data through the all class functions. It's also worth mentioning that every newly created(also called derived in OOP) object could hold its own different data in those variables.
In this database class we'll mainly use member functions to perform various operations and return result such as TRUE, FALSE or a data filled member variable. This way we'll simplify the whole coding process.

But let's first explain the main functions:
function mysqldb() is used to set up our database: host, name and credentials such as username and password needed in order to connect. Their values are being hardcoded as variables, but you could load and use them from another file. Next these variables are set to the object via member functions so they become member variables - accessible from every function inside the class.
Next comes a call to opendbconnection(). As we can see there mysql_connect() uses our member variables to connect to the database host and select the proper database. We have used the variable $dbconnection to show whether our connection has launched successfully. Then we reset our connection variables. This way if somehow our object is hacked and dumped, these variables will be empty, so the attacker won't knew our username and password to gain access to our database.

I. Initial setup example:
Change:

$DB             =    "your_db_name";
$WEBUSER        =    "your_mysql_username";
$WEBPASSWORD    =    "your_mysql_password";

and save the above code as mysql_oop.php

then in a separate php file run:

<?
include_once("mysql_oop.php");
$db= &new mysqldb;
?>

This way we include our database class and create new instance(object) of it. From now on we'll have an access point to that object(or our database) via the variable $db.

II. Selecting, Updating and Deleting data
Function selectquery() is handy because it runs a given sql query and returns its calculated result. In order to use it we must first call function setsql() like this:

<?
$db->setsql("SELECT * from our_table");//fill in the $db->sql variable;
if(!$db->selectquery()) {echo "query returned empty dataset";}
$info = $db->result;
print_r($info);
?>

our result will show up in the $info array. Don't forget to initialize the class first with the 2 lines from our first example. Go ahead and try it!

III. More examples:
Before running a query we must check the input variables. It's easy. For example to check and sanitize the escape characters of variable $password just do:

$new_password=$db->real_escape($new_password);

To get the returned number of rows:

echo $numberrows = $db->getnumberrows();

To debug or show your input sql query code you can use:

echo $db->getsql();

function get_insert_id() displays the id of our last inserted row

and closedbconnection() is used to close the connection to our database server

This object oriented approach is very flexible. Just try the above code and if you have questions go ahead and ask!

Update: please see the following video for working examples:
Next: Try sessions! by Nevyan Neykov

---