Basic PHP-MySql OOP

basicMySQL Getting the Basic with PHP-MySql

Design Patterns are general OOP solutions for any computer language, and yet each language has different typical uses. One of the most important uses of PHP is to communicate with MySql. As the “middleman” between HTML and MySql, developers need to juggle three languages well in the HTML<->PHP<->MySql sequence and shuffle. All of the OOP work, though, belongs to PHP since neither HTML nor MySql is object oriented. This post is dedicated to the fundamental OOP questions surrounding HTML<->PHP connections, instantiating class instances and creating re-usable MySql classes. I’d also like to touch on using comments in code and perhaps stir up a little controversy with comments about certain “standards” for comments in PHP.

The first thing to do is to take the program for test drive and look at the code. Run the program by clicking the Play button and click the Download button to get the files.

The HTML Communication

Object oriented programming is all about objects communicating with one another. The first communication is between HTML and the PHP object it first calls. As the user interface (UI), the HTML document must get data from the user and pass it to PHP for processing. In PHP object communication, each object can call the object as a whole or specify a method or property in the other object to perform a specific task. With HTML, we pretty much have to be satisfied with a call to an object (or object trigger) and depend on PHP to carry the ball from that point on. However, if an HTML document has several forms, each form can call a different object, or each form submit button can have a different name. By using the unique name for each submit button a single Client class can determine which operation the user requests. The following HTML is set up for that purpose:

^?View Code HTML






Basics of PHP OOP




  Basic Classes for Basic Tasks I
  Keeping objects clear and focused on a single task

  Entering Data into a Table and Retrieving it

Data are entered into a table through a UI, but automatically generated data also may be entered.



  Developer Info:

    Name:
    
    

    Email:
    
    
  
  Programming Language Used Most

    
    C#:
    

    
    JavaScript:
    

    
    PHP:
    

    
    Python:
  
  

  
    
  


Display All Data in Table with a new Form
  
  

    
  



The PHP class uses 4 MySql fields to store and retrieve the information:

  id
  name
  email
  lang

PHP

  
  Update and Drop Files

You may also want to take a quick look at the CSS file that goes with the HTML.

^?View Code CSS

@charset "UTF-8";
/* CSS Document */
/*66635D (gray)  E54140 (red) F7EBD5 (cream) 6CC5C1 (aqua) 000000 (black)   */
body
{
    background-color: #F7EBD5;
    font-family: sans-serif;
    color: #66635D;
}
a
{
    color: #E54140;
    text-decoration: none;
    font-weight: bold;
}
 
.warning
{
    color: #E54140;
    font-weight: bold;
}
 
h1
{
    font-family: "Arial Black", Verdana, Helvetical,sans-serif;
     background-color: #6CC5C1 ;
     color: #E54140;
     text-align: center;
}
header
{
    background-color: #E54140;
     color: #E54140;
     padding:1em;
     color: #F7EBD5;
     text-align: center;
}
 
.fl
{
    float: left;
}
 
div
{
    padding-left: 1em;
}
h2
{
    color: #000000;
}
 
li
{
    font-weight: bold;
}
iframe
{
    background-color:#6CC5C1;
}

One thing that may stir the pot (among developers) found in the HTML document is the use of iframe tags. HTML5 retained iframes, and in the jQuery Mobile documentation, you will find the use of iframes. They no longer present a hazard to SEOs; and so I don’t see the harm using them to display PHP output. Besides, I could not find a suitable replacement. (I’m not about to mix PHP and HTML to create a workable hack straight out of the 9th circle of Hell.) So see if you can live with iframes for now, and we’ll get smelling salts for the fainters.

The Client Sorts it Out

When the UI has many options that must be handled by a client object to make the appropriate request from a collection of objects, each with a single responsibility, there’s going to be some kind of conditional statement to sort out which object to use. For lots of good reasons that I won’t go into here, switch/case and if/ifelse statements are to be used sparingly and judiciously in OOP and design pattern programming. An alternative approach would be to add several methods to the Client class and then have individual trigger files launched from the different forms in the HTML document call the appropriate Client method. That’s messy, but because HTML cannot address a class method, that may actually be a more orthodox approach. (Keep those smelling salts handy.)

The basic logic of the approach used is this:

When a submit button is pressed it can be trapped by the isset() method
Provide a name for each submit button
Test for variable being set and not NULL
Once tested use unset() to remove set state
Call object of set variable

Now take a look at the Client class to see how that reasoning has been implemented:

^?View Code PHP


ERROR_REPORTING( E_ALL | E_STRICT );
ini_set("display_errors", 1);
function __autoload($class_name) 
{
    include $class_name . '.php';
}
class Client
{
        //Variable to select the correct class
        private $task;
 
        //Which submit button used?
        public function __construct()
        {
            if(isset($_POST['insert']))
            {
                unset($_POST['insert']);
                $this->task= new DataEntry();   
            }
            elseif(isset($_POST['all']))
            {
                unset($_POST['all']);
                $this->task= new DataDisplay();
            } 
            elseif(isset($_POST['update']))
            {
                unset($_POST['update']);
                $this->task= new DataUpdate();
            }
            elseif(isset($_POST['kill']))
            {
                unset($_POST['kill']);
                $this->task= new DeleteRecord();
            } 
        }     
}
$worker = new Client();
?>

The Client has four operations reflecting the four classes:

DataEntry()
DataDisplay()
DataUpdate()
DeleteRecord()

That’s all there is to the communication between HTML and the PHP Client class. The Client works (as do all client classes) to make requests. The origin of the request is the user through the HTML UI.

The Implemented Responsibilities

Now that we know what the four responsibilities are (the names of the four classes), you will find that each executes a Structured Query Language (SQL) statement and takes care of getting information to and from the MySql database.

Entering Data (DataEntry class)

The data passed from HTML are superglobals, and they can be picked up by any of the classes called by the Client. So once we know what operation the user wants, it’s simply a matter of provided the data it needs the achieve its goal.

So, the first task is to enter data into a MySql database. Using the static method in the UniversalConnect class the entire connection can be accomplished in a single line:

$this->hookup=UniversalConnect::doConnect();

(See the origin of the class and interface in this post.) All of the data from the superglobals (the $_POST[] array) is then placed in private variables. The following class shows the entire operation:

^?View Code PHP


class DataEntry
{
        //Variable for MySql connection
        private $hookup;
        private $sql;
        private $tableMaster;
 
        //Field Variables
        private $name;
        private $email;
        private $lang;
 
        public function __construct()
        {
            //Get table name and make connection
            $this->tableMaster="basics";
            $this->hookup=UniversalConnect::doConnect();
 
            //Get data from HTML form
            $this->name=$_POST['name'];
            $this->email=$_POST['email'];
            $this->lang=$_POST['lang'];
 
            //Call private methods for MySql operations
            $this->doInsert();
            $this->hookup->close();
        }
 
        private function doInsert()
        {
                $this->sql = "INSERT INTO $this->tableMaster (name,email,lang) VALUES ('$this->name','$this->email', '$this->lang')";
 
                try
                {     
                        $this->hookup->query($this->sql);
                        printf("User name: %s 
 Email: %s 
 uses %s the most for programming.",$this->name,$this->email,$this->lang);
                }
 
                catch (Exception $e)
                {
                        echo "There is a problem: " . $e->getMessage();
                        exit();
                }
        }
}
?>

The doInsert() method executes a SQL insertion command using a PHP mysqli() method within a try/catch container. The printf() sends information back to the user.

The rest of the classes take on a single responsibility as well, using a single SQL command in a PHP wrapper:

Retrieving and showing all Data (DataDisplay() class)

This next operation simply scoops up all of the records and displays them. Like all other returns sent to the screen, the results show up in the iframe container in the HTML document. The SQL command requires only that the name of the table is what you want. The wildcard symbol (*) indicates that it wants all all fields and records.

^?View Code PHP


class DataDisplay
{
        //Variable for MySql connection
        private $hookup;
        private $sql;
        private $tableMaster;
 
        public function __construct()
        {
            //Get table name and make connection
            $this->tableMaster="basics";
            $this->hookup=UniversalConnect::doConnect();
            $this->doDisplay();
            $this->hookup->close();       
        }
 
        private function doDisplay()
        {
            //Create Query Statement
            $this->sql ="SELECT * FROM $this->tableMaster";
 
            try
            {
                $result = $this->hookup->query($this->sql);
 
                while ($row = $result->fetch_assoc()) 
                {
                    printf("ID: %s Name: %s Email: %s 
Computer Language: %s",$row['id'], $row['name'],$row['email'],$row['lang'] );
                }
 
                $result->close();
            }
            catch(Exception $e)
            {
                echo "Here's what went wrong: " . $e->getMessage();
            }
        }
}
?>

Changing (updating) Data (DataUpdate() class)

Updating the data takes three methods instead of one. The operation is a bit more elaborate since SQL can only handle updating one field at a time. (See the previous post on using update with a Template Method.)

^?View Code PHP


class DataUpdate
{
   private $hookup;
   private $tableMaster;
   private $sql;
   //Fields
   private $id;
   private $name;
   private $email;
   private $lang;
 
   public function __construct()
   {
      $this->id=intval($_POST['id']);
      $this->name=$_POST['name'];
      $this->email=$_POST['email'];
      $this->lang=$_POST['lang'];
 
      $this->tableMaster="basics";
      $this->hookup=UniversalConnect::doConnect();
 
       //Call each update
      $this->doName();
      $this->doEmail();
      $this->doLang();
 
        //Close once
      $this->hookup->close();
   }
 
   private function doName()
   {
      $this->sql ="UPDATE $this->tableMaster SET name='$this->name' WHERE id='$this->id'";
      try
      {
         $result = $this->hookup->query($this->sql);
         echo "Name update complete.
";
      }
      catch(Exception $e)
      {
         echo "Here's what went wrong: " . $e->getMessage();
      } 
   }
 
   private function doEmail()
   {
      $this->sql ="UPDATE $this->tableMaster SET email='$this->email' WHERE id='$this->id'";
      try
      {
         $result = $this->hookup->query($this->sql);
         echo "Name update complete.
";
      }
      catch(Exception $e)
      {
         echo "Here's what went wrong: " . $e->getMessage();
      } 
   }
 
   private function doLang()
   {
      $this->sql ="UPDATE $this->tableMaster SET lang='$this->lang' WHERE id='$this->id'";
      try
      {
         $result = $this->hookup->query($this->sql);
         echo "Computer language update complete.
";
      }
      catch(Exception $e)
      {
         echo "Here's what went wrong: " . $e->getMessage();
      } 
   }
}
?>

Deleting a Record (DeleteRecord() class)

The operation for deleting a record must be done carefully because once a record has been deleted, it’s gone for good. By allowing only a unique field name (or number in the case of the id field) you do not run the risk of removing all records where the name is “John” when you only want to remove “John” where the id number is “23.”

^?View Code PHP


class DeleteRecord
{
        //Variables for MySql connection
        private $hookup;
        private $sql;
        private $tableMaster;
 
        //From HTML
        private $deadman;
 
        public function __construct()
        {
            $this->deadman =intval($_POST['idd']);
            //Get table name and make connection
            $this->tableMaster="basics";
            $this->hookup=UniversalConnect::doConnect();
            $this->recordKill();
            $this->hookup->close();       
        }
 
        private function recordKill()
        {
            //Create Query Statement
            $this->sql ="Delete FROM $this->tableMaster WHERE id='$this->deadman'";
 
            try
            {
                $result = $this->hookup->query($this->sql);
                printf("Record with ID=%s: has been dropped.
",$this->deadman );
            }
            catch(Exception $e)
            {
                echo "Here's what went wrong: " . $e->getMessage();
            }
        }
}
?>

The last two operations were launched from a linked HTML document, but they both used the same submit button id to display all of the data in the table. As you can see, it is very similar to the first HTML page.

^?View Code HTML






Easy Update PHP-MySql OOP




  Basic Classes for Basic Tasks II
  Keeping objects clear and focused on a single task

  Updating Data in a Table and Dropping Records

Update all data in a record.



  Record Info:

    ID Number:
    
    
    Developer Update:

    Update Name:
    
    

    Update Email:
    
    

  
  Update Programming Language Used Most

    
    C#:
    

    
    JavaScript:
    

    
    PHP:
    

    
    Python:
  
  

  
    
  



  Record to Delete:

    ID Number:
    
    
    By clicking this button the record will be deleted forever.

     
  

Display All Data in Table with a new Form
  
  

    
  



The PHP class uses 4 MySql fields to store and retrieve the information:

  id
  name
  email
  lang

PHP

   Enter Records

Both HTML pages call the same Client.php file; so it’s pretty easy to create.

A Simple Set

All of the files you will need are in the zip file you can get by clicking the Download button. In the “table work” folder you will find the code for creating the table used as well as the files for connecting to your database. (You will need to place your own user connection information in the IConnectinfo.php file.) However, the real focus of this post are the four classes that do the fundamental MySql tasks. They can be modified to adjust to your own needs, but if you use them as a basic starting point, you’ll come to understand the value of OOP in PHP-MySql programming. In a future post, I’d like to return to this one and see how all of this could be fit into a design pattern. In the meantime play around with it and let me know if you have any ideas on this subject of your own.

30 Responses to “Basic PHP-MySql OOP”

Feed for this Entry Trackback Address

Rob
January 1, 2014 at 12:34 am

Bill,

What an excellent tutorial! I *finally* have a grasp on how OOP is used in websites, something I’ve been struggling with for weeks. Cheers!

Rob
Bill Sanders
January 1, 2014 at 5:30 am

Hi Rob,

Great to hear from you. Not only can OOP save a huge amount of time in development, I found that it’s a lot of fun to learn, especially working with design patterns. While getting used to OOP, you might want to take a peek at the short post on this blog, No Time for OOP or Design Patterns. You’ll find that everyone struggles with OOP initially.

Kindest regards,
Bill
Cool Robert
February 24, 2014 at 5:56 pm

Man, thank you soo much! This fills in a lot of blanks for me!
Marta
May 2, 2014 at 5:49 am

Absolutely brilliant tutorial. It helped so much, feel like I got to know different and better! side of php!!! Thanks a million Bill.

Best regards
Marta
Bill Sanders
May 2, 2014 at 9:09 am

Hi Marta,

You’re more than welcome. OOP PHP is indeed a different side of PHP–more fun too!

Kindest regards,
Bill Sanders
Marta
May 8, 2014 at 6:31 am

Hi Bill.
Just a quick question there. I know you display retrieved data inside the IFrame but would it be possible to display it in html form?? I know it probably would but trying to make it work for the last two days and no success at all. Any advise?

Best regards,
Marta

Bill Sanders

May 8, 2014 at 12:00 pm

Hi Marta,

That’s a very good question. First of all, if you’re worried about using the IFRAME tag, don’t. A lot of comments online are fairly inaccurate because they deal with pre-HTML5. If that IS a concern, just test it.

Second, I wasn’t sure where in a form you might want PHP-generated data; so I created a simple example using a text field. If you launch from the Client class, here’s one way to do in: (All classes were in a single listing, but each can be placed in a separate file.)

^?View Code PHP


error_reporting(E_ALL | E_STRICT);
ini_set("display_errors", 1);
function __autoload($class_name) 
{
    include $class_name . '.php';
}
class Client
{
    private $pdoc;
    public function __construct()
    {
        $this->pdoc = new Pdoc();
    } 
}
$worker=new Client();
?>
 

class Pdoc
{
    private $fromPhp;
    private $test;
    public function __construct()
    {
        $this->test = new ShowMarta();
        $this->buildHTML();
    }
 
    private function buildHTML()
    {
        $docSub = <<
        
        
        
        Show PHP in Form
        
        
        Test to see if Php results come to a Text Input Form
        
          test->doForm()}>
          
          
        
        
        
DOCFORM;
        echo $docSub;
    } 
}
?>
 

class ShowMarta
{
    public function doForm()
    {
        return "Hi_Marta";
    }
}
?>

That’s a fairly clunky way of handling what you want, but it will show you one path to get what you need.

Play around with it and see what else you can come up with.

Kindest regards,
Bill

Dennis
June 12, 2014 at 6:25 am

I’m orientating myself a little with OOP, because I want to take a step forward in programming, but I still find it hard to understand. (it’s a way off thinking). And above is going a little bit to fast for me.

When is try to think back to my own scripts. I wouldn’t know why I would use it. It’s a lot off script for something that can be made in just a few lines..

I have to change my way off thinking I guess.
Bill Sanders
June 12, 2014 at 6:48 am

Dennis,

You hit the nail on the head: OOP requires a different way of thinking about how you code. With small programs using simple algorithms, OOP might look like swatting a fly with a bazooka. However, as your programs grow and become more professional, it seems very natural. Also, you will save time in re-writing code by re-using fully developed and encapsulated modules. For me, the best part is “thinking in OOP.” Instead of thinking sequentially, “first I’ll do this, and second I’ll do that…” like you’re crawling through a long dark tunnel; with OOP, you can see your entire project in a glance. Object A does X; Object B does Y; and Object C communicates with A & B to accomplish Z.

Kindest regards,
Bill
daru79
July 22, 2014 at 8:59 am

Hi Bill,

Your post is awesome! 🙂 i am still learning OOP in PHP and Your post helps me a lot! 🙂 Have a great day!

daru79
Bill Sanders
July 22, 2014 at 9:43 am

Hi Daru79,

Please let me know what kinds of posts I can add that will help you out. I have a range from very simple posts to fairly advanced–all about OOP.

If you’re going to be in Boston in September, drop by the conference and introduce yourself!

Kindest regards,
Bill
Pavan.Maganti
October 28, 2014 at 6:55 am

Great tutorial man. Very useful for beginners.
Bill Sanders
October 29, 2014 at 5:09 am

Pavan,

Let me know what might be helpful or if you have any questions.

Kindest regards,
Bill
daru79
October 31, 2014 at 10:03 am

Hi Bill,

I’ve made an app (uploading banners in order to show them to the Customers) using Your advices in this post! If You want to take a look on it -> simple let me know 😉

Doc Magu

November 8, 2014 at 10:47 am

Good post. I have been using Classes since PHP 4, and PHP 5.x makes things more interesting :).
Question though … you make an assertion”
“SQL can only handle updating one field at a time”
Really ? *grins*
See a method .. (and yes I should use exceptions)

^?View Code PHP

           public function updateRecord($table, $pkey, $data) {
                if (empty ($data)) return false;
                if (! is_array($data)) return false;
                $q = "UPDATE ".$this-&gt;db."`".$table ."` SET ";
                foreach ($data as $field =&gt; $value) {
                        $q .= "`".$field."` = '".$this-&gt;myDB-&gt;real_escape_string($value)."', ";
                }
                $q = trim($q, ' ');$q = trim($q, ',');
                $q .= " WHERE `".$pkey[0]."` = '".$pkey[1]."' ";
                $res = $this-&gt;myDB-&gt;query($q) or die (sprintf("Error: %s\n", $this-&gt;myDB-&gt;sqlstate));
                return $this-&gt;myDB-&gt;affected_rows;
        }

Kind regards

Bill Sanders
November 9, 2014 at 6:52 am

@Doc Magu,

Every time I say, You cannot do something in SQL (or PHP) I should have my head examined. You are right, of course, and I thank you for your comment and added wisdom to this blog. I am particularly grateful for your code to make such a good point.

Kindest regards,
Bill
S.k.joy
March 6, 2015 at 5:32 am

Absolutely,it’s a brilliant tutorials.It’s helps me lot!Thanks a million!
Bill Sanders
March 6, 2015 at 2:20 pm

S.K. Joy,

Glad it was helpful.

Bill
wafix
March 21, 2015 at 1:41 am

Love this tutorial, love you Bill
I’ve been trying to understand the OOP part of php mysql for so long and your tutorial has provided some insight into understanding this bit. Thank God and thank you so much. Be blessed for helpingtme and people like me understand this. 🙂
Bill Sanders
March 21, 2015 at 6:54 am

Thanks Wafix,

I just updated the draft of the book on beginners PHP/OOP. You can download the PDF file for it HERE free.

Kindest regards,
Bill
Marta
March 23, 2015 at 10:56 am

Hey Bill,
Never got a chance to say thank you for that piece of code you created to show me how to display data inside HTML form. That is a one good piece of example. You’re my OOP PHP guru :)Thank you Bill
sarfraz
June 28, 2015 at 7:16 am

very good helpful effort in understanding oop.Thanks.
Bill Sanders
June 30, 2015 at 5:34 am

Hi Sarfraz,

Thanks, and just let me know what else might help in getting OOP in PHP!

Bill
MagicPhoton
August 4, 2015 at 4:22 pm

Dear Bill,

First of all thank you and congratulations for the great job documented in this post.

I have been running your example in an ARM based processor board and I get the next error when I try to display all data:

Fatal error: Call to a member function fetch_assoc() on a non-object in /usr/local/apache/htdocs/MySqlOOPBasics/DataDisplay.php on line 27

Any suggestions?

Kind regards.
Bill Sanders
August 5, 2015 at 5:50 am

Hi MagicPhoton,

If you’re running the code on your computer with MySql, check the version of PHP you’re using. The OOP style object, array mysqli_result::fetch_assoc ( void ) was not introduced until PHP 5; anything older than PHP 5 will not recognize fetch_assoc ( ).

If you run the program on this site, I’m assuming that when you click the Display All button everything is working. I’m pretty sure it’s not the ARM processor. Also, you need to have your MySql in and tested to make sure that your data is stored correctly.

Here’s a set of steps:
1. Check your version of PHP. Update it to PHP 5 or greater if it is less than PHP 5.
2. Use this post to test your MySql connection. Test your MySql connection.
3. Please add another comment to let me know if you got everything working or not; if not I’ll see if I can come up with other solutions.

Kindest regards,
Bill
sana
October 8, 2015 at 9:52 am

hy if we went use POO for many tablesmysql?
Bill Sanders
October 8, 2015 at 1:42 pm

Hy Sana,

I’m not sure what you mean, but if you are wondering if you can use PHP OOP for working with multiple tables and mysql, the answer is “yes.” In fact, if you’re using multiple tables and even multiple mysql, OOP make the job a lot easier.

Kindest regards,
Bill
kila
February 29, 2016 at 7:34 am

Thanks a lot fella! this tutorial has opened the new way of programming to me, from pure structural to OOP
hacker
April 30, 2016 at 6:41 am

thanks a lot but l dnt seem to find the database
Bill Sanders
June 11, 2016 at 9:05 am

Thank Kila!

@Hacker: Where were you looking for the database? The output for the database appears in the iframe window. To use this program, you need to have access of a MySql server. However, here, you can use mine to enter data and see how it works.

Let me know exactly where you’re looking for the database and I’ll see if I can help you.

Take care,
Bill