Applying oop in php in mysql database manipulation

In this tutorial. I’m going to show you how you can make a class for manipulating mysql database in php.

Like you I’m still a beginner. But I want to keep track of what I have learned. That’s why I made this blog. The codes you will see here are not optimized and secured. This is only for the purpose of sharing what I know.

Let’s begin by creating a new class. If you’re not yet familiar with object oriented programming. Then you should learn java. I believe that java is good for beginners.

<?php
    class mysqlcon{
 
    }
?>

You can name the class whatever you want to name it. That class will contain all the functions and fields that will be used to manipulate mysql database.

To refresh your memories. Here’s how we connect to mysql database in a procedural way:

<?php //procedural $lname="Ancheta"; $uid="1"; $con=mysql_connect("localhost","root","1234"); $db=mysql_select_db("objordb",$con); $selects=mysql_query("SELECT * FROM testtable"); $inserts=mysql_query("INSERT INTO testtable(LNAME)

VALUES('$lname')"); $updates=mysql_query("UPDATE testtable

SET LNAME='$lname' WHERE UID='$uid'"); $deletes=mysql_query("DELETE FROM testtable WHERE UID='$uid'"); if(myql_num_rows()==0){//check if record exists echo "Does not exist!"; }else{ //if record exist while($row=mysql_fetch_assoc($selects)){//fetch all matches echo $row['LNAME']; } } ?>

It looks messy right?

We can avoid this and make our code more organized if we used the principles of object oriented programming. In the applications that were going to build.

The analogy here, is that we make a single class which will contain all the things that we need to manipulate our mysql database. A single repository for all the tools we need. So that things will be more organized. So that there’s no need to rewrite code.

 

Fields

First thing that we need to do after declaring a new class. Is to declare the member fields. Or variables that will be used throughout the whole class.

private $connection; //variable that will store the database configuration
private $database; //variable that will store the name of the database
public $query; //variable that will store the queries

 

Connection

Second, we declare the function that will open the database connection:

 function open_con(){
     
    $this->connection=mysql_connect("localhost", "root", "1234"); 
    $this->database=mysql_select_db("cms", $this->connection);
     
 }

Nothing new here, we just encapsulated the built in php functions for manipulating mysql database.

 

Constructor

Third, we declare a constructor. The constructor is the function which is called the moment you declare an object of the class where it belong.

In this case, we have to call the open_con() function on the constructor. So that we don’t have to call the open_con() function every time we want to connect.

function __construct(){
        $this->open_con();   
}

You can also call other functions in the constructor if you want.

 

Closing connection

If we have open, then we must also have close connection. This is not mandatory. Because oftentimes we don’t really close the connection.

function close_con(){
 
    if(isset($this->connection)){
        mysql_close($this->connection);
        unset($this->connection);
    }    
}

What I just did was to check if the connection variable has contents. If it contains anything then I call the mysql_close function and supplied the connection variable as the argument. Then lastly, I unset the connection variable. To make sure that it contains no information about the database. The next time anyone uses it.

 

Queries

Fifth, the query part. My codes here are not fully object oriented because I just reused what is available, and encapsulated them.

Here’s a function that will need a String argument. And after it has been called, considering that the correct argument has been supplied. In this case, a query string. Then it will return the query equivalent of that query string. Where it is no longer a string variable but a resource. Which can be used in other mysql functions.

function make_query($queryString){
        return mysql_query($queryString);
    
}

Here’s another example. A function which returns the number of rows that a query string has returned. As you can see this function makes use of the make_query() function to convert the query string into a resource. Then calls the mysql_num_rows function and supplied the query, as the argument. Then finally returned the value.

function num_rows($queryString){
        $this->query=$this->make_query($queryString);
        return mysql_num_rows($this->query);
}

Its similar to what you see in phpmyadmin when you execute a select query:

image

In the screenshot , the total number of rows returned is 8.

 

Here’s the last example, a function which performs a delete query:

function deletes($table, $field, $id){ $this->query=$this->make_query("DELETE FROM {$table}

WHERE {$field}={$id}"); }

Its pretty much the same as the previous examples. But we used an argument as part of the query.

 

Calling

This is the final part of this tutorial. Where we actually call the functions that we declared in our class.

First thing that we need to do is to create a new php file, and include the file that we created earlier:

include('mysqlclass.php');

Then we create an object of the class. Make sure that the name that you type after the ‘new’ keyword  matches the actual name of the class:

$con=new mysqlcon();

You can scroll to the top, to verify things.

And here’s how to call functions:

$rows = $mysqlcon->num_rows("SELECT * FROM users");
echo $rows;

This will echo out, 8. As we have seen earlier on the screenshot.

Here’s another which deletes the user with the ID of 5 from the users table:

$mysqlcon->deletes('users', 'ID', 5);

Note that the number 5 isn’t surrounded with quotes.

 

 

Conclusion

You have learned how to apply oop in php. This may not be the best tutorial but I hope you learned something.

2 thoughts on “Applying oop in php in mysql database manipulation

  1. that is really helpful at least for beginners, they can further make changes into it according to their requirement of the application

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