Real-World OOP With PHP andMySQL

Once we determine our requirements, creating the basic structure of our class is relatively straightforward. Firstly, we need to make sure that we can do our basic MySQL operations. In order to achieve that, we need to implement the following functions:

select

insert

delete

update

connect

disconnect

Let's quickly see how our class definition looks:

<?php
class Database 
{
    private $db_host = '';
    private $db_user = '';
    private $db_pass = '';
    private $db_name = '';
    private $con = '';
    
    public function __contruct($db_host, $db_user, $db_pass, $db_name)
    {
        $this->db_host = $db_host;
        $this->db_user = $db_user;
        $this->db_pass = $db_pass;
        $this->db_name = $db_name;
    }

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

Basically, it's a skeleton class called Database which represents a database connection and provides methods to interact with the database. We've defined different private properties like $db_host, $db_user, $db_pass, $db_name, and $con. The class is used to store the database connection details and the connection object itself.

In the next section, we'll go through the actual implementation of this class.

Implement the Database Class

In this section, we'll implement methods that are required in order to perform different database operations.

The `connect` Method

Go ahead and add the connect method as shown in the following snippet.

<?php

public function connect()
{
    if (!$this->con) {
        $this->con = mysqli_connect($this->db_host, $this->db_user, $this->db_pass);

        if($this->con) {
            $seldb = mysqli_select_db($this->con, $this->db_name);

            if($seldb) {
                return true; 
            } else {
                return false;
            }
        } else {
            return false;
        }
    } else {
        return true;
    }
}

The purpose of the connect method is to establish a connection to a MySQL database using the mysqli extension. Firstly, we check if there is already a connection to the database, and if there is, the method simply returns true to indicate that the connection is already established and there is no need to reconnect. If there is no connection, it attempts to connect to the database using the mysqli_connect function.

Once the database connection is successful, it proceeds to select the database by calling the mysqli_select_db function. Finally, if the database selection is successful, it returns true to indicate that the connection is established and the database is selected. If either the connection or the database selection fails, it returns false.

The `disconnect` Method

Let's go through the disconnect method.

<?php

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

It checks if there is an active database connection, and if there is, it uses the mysqli_close() function to close the connection and set the $this->con property to false.

The `select` Method

It's one of the most important methods which will be used frequently. Firstly, let's create the tableExists method, which is used to check if the table exists or not in the MySQL database.

<?php

private function tableExists($table) 
{
    $tablesInDb = mysqli_query($this->con, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');

    if($tablesInDb) {
        if(mysqli_num_rows($tablesInDb) == 1) {
            return true;
        } else {
            return false;
        }
    }
}

Next, let's go ahead and implement the select method.

<?php

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)) {
        $result = $this->con->query($q);

        if($result) {
            $arrResult = $result->fetch_all(MYSQLI_ASSOC);

            return $arrResult;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

It's used to retrieve records from a database. It first builds a SQL query string using the input arguments. It checks if the specified table exists in the database, and if so, executes the query. If the query is successful, the resulting rows are fetched as an associative array and returned. If the query fails, the method returns false. If the specified table does not exist in the database, it also returns false.

The `insert` Method

Let's go through the insert method, as shown in the following snippet.

<?php

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++) {
            $values[$i] = mysqli_real_escape_string($this->con, $values[$i]);

            if (is_string($values[$i])) {
                $values[$i] = '"'.$values[$i].'"';
            }
        }

        $values = implode(',', $values);
        $insert .= ' VALUES ('.$values.')';
        $ins = mysqli_query($this->con, $insert);

        if ($ins) {
            return true;
        } else {
            return false;
        }
    }
}

It is used to insert data into a table in a MySQL database using the mysqli extension. The function takes three parameters: the table name, the values to be inserted, and optionally the columns to insert into. Firstly, it checks if the specified table exists in the database, and if it does, it constructs the SQL query to insert the data using the provided values and columns. We've used the mysqli_real_escape_string function to sanitize any string values.

Finally, the query is executed using the mysqli_query() function, and the function returns true if the query was successful and false otherwise.

The `delete` Method

Let's quickly go through the delete method.

<?php

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

        $del = $this->con->query($delete);

        if ($del) {
            return true; 
        } else {
           return false; 
        }
    } else {
        return false; 
    }
}

It is used to delete either a table or a row from our database.

The `update` Method

It's also one of the important methods which allows us to update the database information.

The update method implementation should look like this.

<?php

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);
        
        $setValues = [];
        foreach ($keys as $key) {
            $value = $rows[$key];
            $setValues[] = "`$key` = '" . mysqli_real_escape_string($this->con, $value)."'";
        }
        
        $update .= implode(',', $setValues);
        $update .= ' WHERE ' . $where;
        
        $query = $this->con->query($update);
        
        if ($query) {
            return true;
        } else {
            return false;
        }
    } else {
        return false;
    }
}

The update method has three arguments: $table, $rows, and $where. The $where array is then parsed to generate the SQL WHERE clause for the update query. The $rows array is parsed to generate the SQL SET clause for the update query. The array keys represent the column names, while the array values represent the new values for the columns. We've used the mysqli_real_escape_string function to sanitize any string values.

With that, we've completed creating the methods that are required to do database manipulation. You can save it as a Database.php file.

In the next section, we'll see how to use it.

How to Use the `Database` Class

Firstly, let's create a MySQL table in our database so that we can test the CRUD operations on that table.

Go ahead and run the following SQL to create a table.

CREATE TABLE `mysqlcrud` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
);

It should create the mysqlcrud table in your database.

How to Insert a New Row

Let's see how to insert a new row.

<?php
require "Database.php";

$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");

if ($db->connect()) {
    $db->insert('mysqlcrud', array(1,"Foo Bar","[email protected]"));
} else {
    echo "There was some error connecting to the database.";
}
?>

Firstly, we've created a new Database object with the database credentials passed as parameters. You need to replace it with your credentials. Next, the connect method of the Database class is called to establish a connection to the database. If the connection is successful, the insert method is called with the table name as the first parameter and an array of values for the new row as the second parameter.

If everything goes smoothly, it should create a new row in the mysqlcrud table.

How to Update a Row

Let's see how the update operation works.

<?php
require "Database.php";

$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");

if ($db->connect()) {
    $db->update('mysqlcrud',array('name'=>'Updated Foo Bar'), array('id',1));
} else {
    echo "There was some error connecting to the database.";
}
?>

As you can see, we've called the update method to update the name column of the row.

How to Delete a Row

Next, let's see how to delete a specific row.

<?php
require "Database.php";

$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");

if ($db->connect()) {
    $db->delete('mysqlcrud', "`id` = '1'");
} else {
    echo "There was some error connecting to the database.";
}
?>

It should delete the row with id equal to 1.

How to Retrieve Records

Let's see how we can retrieve all the records from our table.

<?php
require "Database.php";

$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");

if ($db->connect()) {
    $rows = $db->select('mysqlcrud');
} else {
    echo "There was some error connecting to the database.";
}
?>

As you can see, the select method allows us to fetch all the records from the desired table.

So that's how you can perform CRUD operations by using our class.

1	<?php
2
3	public function update($table, $rows, $where)
4	{
5	if ($this->tableExists($table)) {
6	// Parse the where values
7	// even values (including 0) contain the where rows
8	// odd values contain the clauses for the row
9	for ($i = 0; $i < count($where); $i++) {
10	if ($i % 2 != 0) {
11	if (is_string($where[$i])) {
12	if (($i + 1) != null) {
13	$where[$i] = '"' . $where[$i] . '" AND ';
14	} else {
15	$where[$i] = '"' . $where[$i] . '"';
16	}
17	}
18	}
19	}
20
21	$where = implode('=', $where);
22
23	$update = 'UPDATE ' . $table . ' SET ';
24	$keys = array_keys($rows);
25
26	$setValues = [];
27	foreach ($keys as $key) {
28	$value = $rows[$key];
29	$setValues[] = "`$key` = '" . mysqli_real_escape_string($this->con, $value)."'";
30	}
31
32	$update .= implode(',', $setValues);
33	$update .= ' WHERE ' . $where;
34
35	$query = $this->con->query($update);
36
37	if ($query) {
38	return true;
39	} else {
40	return false;
41	}
42	} else {
43	return false;
44	}
45	}

An Interest In:

Web News this Week

Some of Our Sources

Help Webnuz

Implement the Database Class

The `connect` Method

The `disconnect` Method

The `select` Method

The `insert` Method

The `delete` Method

The `update` Method

How to Use the `Database` Class

How to Insert a New Row

How to Update a Row

How to Delete a Row

How to Retrieve Records

TutsPlus - Code

1	<?php
2	class Database
3	{
4	private $db_host = '';
5	private $db_user = '';
6	private $db_pass = '';
7	private $db_name = '';
8	private $con = '';
9
10	public function __contruct($db_host, $db_user, $db_pass, $db_name)
11	{
12	$this->db_host = $db_host;
13	$this->db_user = $db_user;
14	$this->db_pass = $db_pass;
15	$this->db_name = $db_name;
16	}
17
18	public function connect() {}
19	public function disconnect() {}
20	public function select() {}
21	public function insert() {}
22	public function delete() {}
23	public function update() {}
24	}

1	<?php
2
3	public function connect()
4	{
5	if (!$this->con) {
6	$this->con = mysqli_connect($this->db_host, $this->db_user, $this->db_pass);
7
8	if($this->con) {
9	$seldb = mysqli_select_db($this->con, $this->db_name);
10
11	if($seldb) {
12	return true;
13	} else {
14	return false;
15	}
16	} else {
17	return false;
18	}
19	} else {
20	return true;
21	}
22	}

1	<?php
2
3	public function disconnect()
4	{
5	if ($this->con) {
6	if (mysqli_close($this->con)) {
7	$this->con = false;
8	return true;
9	} else {
10	return false;
11	}
12	}
13	}

1	<?php
2
3	private function tableExists($table)
4	{
5	$tablesInDb = mysqli_query($this->con, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
6
7	if($tablesInDb) {
8	if(mysqli_num_rows($tablesInDb) == 1) {
9	return true;
10	} else {
11	return false;
12	}
13	}
14	}

1	<?php
2
3	public function select($table, $rows = '*', $where = null, $order = null)
4	{
5	$q = 'SELECT '.$rows.' FROM '.$table;
6
7	if($where != null)
8	$q .= ' WHERE '.$where;
9
10	if($order != null)
11	$q .= ' ORDER BY '.$order;
12
13	if($this->tableExists($table)) {
14	$result = $this->con->query($q);
15
16	if($result) {
17	$arrResult = $result->fetch_all(MYSQLI_ASSOC);
18
19	return $arrResult;
20	} else {
21	return false;
22	}
23	} else {
24	return false;
25	}
26	}

1	<?php
2
3	public function insert($table, $values, $rows = null)
4	{
5	if ($this->tableExists($table)) {
6	$insert = 'INSERT INTO '.$table;
7
8	if ($rows != null) {
9	$insert .= ' ('.$rows.')';
10	}
11
12	for ($i = 0; $i < count($values); $i++) {
13	$values[$i] = mysqli_real_escape_string($this->con, $values[$i]);
14
15	if (is_string($values[$i])) {
16	$values[$i] = '"'.$values[$i].'"';
17	}
18	}
19
20	$values = implode(',', $values);
21	$insert .= ' VALUES ('.$values.')';
22	$ins = mysqli_query($this->con, $insert);
23
24	if ($ins) {
25	return true;
26	} else {
27	return false;
28	}
29	}
30	}

1	<?php
2
3	public function delete($table, $where = null)
4	{
5	if ($this->tableExists($table)) {
6	if ($where == null) {
7	$delete = 'DELETE '.$table;
8	} else {
9	$delete = 'DELETE FROM '.$table.' WHERE '.$where;
10	}
11
12	$del = $this->con->query($delete);
13
14	if ($del) {
15	return true;
16	} else {
17	return false;
18	}
19	} else {
20	return false;
21	}
22	}

1	CREATE TABLE `mysqlcrud` (
2	`id` INT(11) NOT NULL AUTO_INCREMENT,
3	`name` VARCHAR(255) NOT NULL,
4	`email` VARCHAR(255) NOT NULL,
5	PRIMARY KEY (`id`)
6	);

1	<?php
2	require "Database.php";
3
4	$db = new Database("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
5
6	if ($db->connect()) {
7	$db->insert('mysqlcrud', array(1,"Foo Bar","[email protected]"));
8	} else {
9	echo "There was some error connecting to the database.";
10	}
11	?>

An Interest In:

Web News this Week

Some of Our Sources

Help Webnuz

Real-World OOP With PHP andMySQL

Implement the Database Class

The connect Method

The disconnect Method

The select Method

The insert Method

The delete Method

The update Method

How to Use the Database Class

How to Insert a New Row

How to Update a Row

How to Delete a Row

How to Retrieve Records

TutsPlus - Code

The `connect` Method

The `disconnect` Method

The `select` Method

The `insert` Method

The `delete` Method

The `update` Method

How to Use the `Database` Class