Hot File

World OOP With PHP and MySQL

View: 956    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: MySQL   Fields: Other

9 point/14 review File has been tested

Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

Introduction

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.

class Database
{
    public function connect()   {   }
    public function disconnect()    {   }
    public function select()        {   }
    public function insert()        {   }
    public function delete()        {   }
    public function update()    {   }
}

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.

private db_host = ‘’; 
private db_user = ‘’; 
private db_pass = ‘’; 
private db_name = ‘’; 
 
public function connect()
    {
        if(!$this->con)
        {
            $myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass);
            if($myconn)
            {
                $seldb = @mysql_select_db($this->db_name,$myconn);
                if($seldb)
                {
                    $this->con = true; 
                    return true; 
                } else
                {
                    return false; 
                }
            } else
            {
                return false; 
            }
        } else
        {
            return true; 
        }
    }

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.

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.

public function disconnect()
{
    if($this->con)
    {
        if(@mysql_close())
        {
                       $this->con = false; 
            return true; 
        }
        else
        {
            return false; 
        }
    }
}

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.

private $result = array(); 
 
private function tableExists($table)
    {
        $tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
        if($tablesInDb)
        {
            if(mysql_num_rows($tablesInDb)==1)
            {
                return true; 
            }
            else
            { 
                return false; 
            }
        }
    }

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.

public function select($table, $rows = '*', $where = null, $order = null)
    {
        $q = 'SELECT '.$rows.' FROM '.$table;
        if($where != null)
            $q .= ' WHERE '.$where;
        if($order != null)
            $q .= ' ORDER BY '.$order;
        if($this->tableExists($table))
       {
        $query = @mysql_query($q);
        if($query)
        {
            $this->numResults = mysql_num_rows($query);
            for($i = 0; $i < $this->numResults; $i++)
            {
                $r = mysql_fetch_array($query);
                $key = array_keys($r); 
                for($x = 0; $x < count($key); $x++)
                {
                    // Sanitizes keys so only alphavalues are allowed
                    if(!is_int($key[$x]))
                    {
                        if(mysql_num_rows($query) > 1)
                            $this->result[$i][$key[$x]] = $r[$key[$x]];
                        else if(mysql_num_rows($query) < 1)
                            $this->result = null; 
                        else
                            $this->result[$key[$x]] = $r[$key[$x]]; 
                    }
                }
            }            
            return true; 
        }
        else
        {
            return false; 
        }
        }
else
      return false; 
    }

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.

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.

public function insert($table,$values,$rows = null)
    {
        if($this->tableExists($table))
        {
            $insert = 'INSERT INTO '.$table;
            if($rows != null)
            {
                $insert .= ' ('.$rows.')'; 
            }
 
            for($i = 0; $i < count($values); $i++)
            {
                if(is_string($values[$i]))
                    $values[$i] = '"'.$values[$i].'"';
            }
            $values = implode(',',$values);
            $insert .= ' VALUES ('.$values.')';
            $ins = @mysql_query($insert);            
            if($ins)
            {
                return true; 
            }
            else
            {
                return false; 
            }
        }
    }

As you can see, this function is a lot simpler than our rather complex select statement. Our delete function will actually be even simpler.

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.

public function delete($table,$where = null)
    {
        if($this->tableExists($table))
        {
            if($where == null)
            {
                $delete = 'DELETE '.$table; 
            }
            else
            {
                $delete = 'DELETE FROM '.$table.' WHERE '.$where; 
            }
            $del = @mysql_query($delete);
 
            if($del)
            {
                return true; 
            }
            else
            {
               return false; 
            }
        }
        else
        {
            return false; 
        }
    }

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:

for($i = 0; $i < count($where); $i++)
            {
                if($i%2 != 0)
                {
                    if(is_string($where[$i]))
                    {
                        if(($i+1) != null)
                            $where[$i] = '"'.$where[$i].'" AND ';
                        else
                            $where[$i] = '"'.$where[$i].'"';
                    }
                   else
                   { 
                        if(($i+1) != null)
                            $where[$i] = $where[$i]. ' AND ';
                        else
                            $where[$i] = $where[$i];
                  }
                }
            }

The next section will create the part of the update statement that deals with actually setting the variables. Since you can change any number of values, I opted to go with an array where the key is the column and the value is the new value of the column. This way we can even do a check to see how many different values were passed to be updated and can add comma’s appropriately.

$keys = array_keys($rows); 
            for($i = 0; $i < count($rows); $i++)
            {
                if(is_string($rows[$keys[$i]]))
                {
                    $update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
                }
                else
                {
                    $update .= $keys[$i].'='.$rows[$keys[$i]];
                }
                // Parse to add commas
                if($i != count($rows)-1)
                {
                    $update .= ','; 
                }
            }

Now that we’ve got those two bits of logic out of the way, the rest of the update statement is easy. Here it is presented below:

public function update($table,$rows,$where)
    {
        if($this->tableExists($table))
        {
            // Parse the where values
            // even values (including 0) contain the where rows
            // odd values contain the clauses for the row
            for($i = 0; $i < count($where); $i++)
            {
                if($i%2 != 0)
                {
                    if(is_string($where[$i]))
                    {
                        if(($i+1) != null)
                            $where[$i] = '"'.$where[$i].'" AND ';
                        else
                            $where[$i] = '"'.$where[$i].'"';
                    }
                }
            }
            $where = implode('=',$where);
             
             
            $update = 'UPDATE '.$table.' SET ';
            $keys = array_keys($rows); 
            for($i = 0; $i < count($rows); $i++)
           {
                if(is_string($rows[$keys[$i]]))
                {
                    $update .= $keys[$i].'="'.$rows[$keys[$i]].'"';
                }
                else
                {
                    $update .= $keys[$i].'='.$rows[$keys[$i]];
                }
                 
                // Parse to add commas
                if($i != count($rows)-1)
                {
                    $update .= ','; 
                }
            }
            $update .= ' WHERE '.$where;
            $query = @mysql_query($update);
            if($query)
            {
                return true; 
            }
            else
            {
                return false; 
            }
        }
        else
        {
            return false; 
        }
    }

Now that we have that we’ve finished our last function, our simple CRUD interface for MySQL is complete. You can now create new entries, read specific entries from the database, update entries and delete things. Also, be creating and reusing this class you’ll find that you are saving yourself a lot of time and coding. Ah, the beauty of object oriented programming.

So we've got our class all made, but how do we use it? This part is simple. Lets start by creating a very simple system database to use in our testing. I created a database called test, and then ran the MySQL statment. You can place it in any database that you like, just make sure that you change the connection variables at the top of the script to match:

The first line is commented out simply because not everyone will need it. If you need to run that more than once, you will need to uncomment it the second time to ensure that it creates the table.

Now that our table is created and populated, it's time to run a few simple queries on it.

connect();
$db->select('mysqlcrud');
$res = $db->getResult();
print_r($res);
?>

If done correctly, you should see the following:

Likewise we can go a step further and run an update query, and then output the results:

update('mysqlcrud',array('name'=>'Changed!'),array('id',1));
$db->update('mysqlcrud',array('name'=>'Changed2!'),array('id',2));
$res = $db->getResult();
print_r($res);
?>

We should see this

Now for a simple insert statement:

;insert('mysqlcrud',array(3,"Name 4","this@wasinsert.ed"));
$res = $db->getResult();
print_r($res);
?>
 

World OOP With PHP and MySQL

World OOP With PHP and MySQL Posted on 12-01-2016  Setting up the skeleton of our class is fairly simple once we figure out exactly what we need. 0.64285714285714/10 956

Comment:

To comment you must be logged in members.

Files with category

  • SQL Query to Find Nth Highest Salary of Employee with java

    View: 196    Download: 0   Comment: 0   Author: none  

    SQL Query to Find Nth Highest Salary of Employee with java

    Category: MySQL
    Fields: Other

    0/0 review
    This is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works.

  • World OOP With PHP and MySQL

    View: 956    Download: 0   Comment: 0   Author: none  

    World OOP With PHP and MySQL

    Category: MySQL
    Fields: Other

    0.32142857142857/14 review
    Setting up the skeleton of our class is fairly simple once we figure out exactly what we need.

  • How to Build a Newsletter System With PHP and MySQL

    View: 1183    Download: 0   Comment: 0   Author: none  

    How to Build a Newsletter System With PHP and MySQL

    Category: MySQL
    Fields: Other

    1.125/4 review
    When starting any project, I like to layout the folders before I start coding, so lets do that now.

  • Top 20+ MySQL Best Practices that can not be ignored

    View: 893    Download: 0   Comment: 0   Author: none  

    Top 20+ MySQL Best Practices that can not be ignored

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    Database operations often tend to be the main bottleneck for most web applications today.

  • Create a PHP/MySQL Powered Forum from Scratch

    View: 1040    Download: 0   Comment: 0   Author: none  

    Create a PHP/MySQL Powered Forum from Scratch

    Category: MySQL
    Fields: Other

    1.5/3 review
    In this tutorial, we're going to build a PHP/MySQL powered forum from scratch.

  • Triggers Introduction to MySQL

    View: 256    Download: 0   Comment: 0   Author: none  

    Triggers Introduction to MySQL

    Category: MySQL
    Fields: Other

    1.6666666666667/3 review
    I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL.

  • How to Stored Procedures in MySQL 5

    View: 247    Download: 0   Comment: 0   Author: none  

    How to Stored Procedures in MySQL 5

    Category: MySQL
    Fields: Other

    2.25/2 review
    MySQL 5 introduced a plethora of new features - stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

  • The strength of the PDO and. MySQLi: Which Should You Use?

    View: 267    Download: 0   Comment: 0   Author: none  

    The strength of the PDO and. MySQLi: Which Should You Use?

    Category: MySQL
    Fields: Other

    1.5/3 review
    When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one?

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd