PHP MySql OOP CRUD Example Tutorial

PHP OOP Database Class Example

A simple PHP Database Class using PDO as the extension.

In this post I will be showing you how to create a reusable, readable Object-Oriented Programming(OOP) Database connection using PHP Pdo to connect to a MySQL Database.

Quick Explanation

By Default PHP comes with 3 MySQL API

You can read Choosing between MySQL, MySQLi and PDO for a comparison for each.

In this example we will be using the PDO API

Here’s a brief explanation of some PDO codes that we will be using.

• dsn [Required] — The Data Source Name that contains information required to connect to the database
• username [Required] — MySQL Username
• password [Required] — MySQL Password

• statement [Required] — An SQL statement to execute
  Example:
  $instance->prepare(‘Select * from tableName’);
  Returns a PDOStatement or Throws an error

• Example:
  $pdoStatement = $instance->prepare(‘Select * from tableName where $pdoStatement->execute([‘id’=>1]); $data = $pdoStatement->fetchAll(); //var_dump($data);
  Returns object or array

Creating the Class:

Preparing our Class name, variables and functions. class DatabaseClass < private $connection = null; // this function is called everytime this class is instantiated public function __construct()< >// Insert a row/s in a Database Table public function Insert( ) < >// Select a row/s in a Database Table public function Select( ) < >// Update a row/s in a Database Table public function Update( ) < >// Remove a row/s in a Database Table public function Remove( ) < >// execute statement private function executeStatement( ) < >> Now that we have a simple design for our Database class. Lets fill the functions with some codes.

Establish the MySQL connection in the costructor // this function is called everytime this class is instantiated public function __construct( $dbhost = «localhost», $dbname = «myDataBaseName», $username = «root», $password = «»)< try< $this->connection = new PDO(«mysql:host=;dbname=;», $username, $password); $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); >catch(Exception $e)< throw new Exception($e->getMessage()); > >
The constructor will have 4 parameters

• $dbhost — The database host.
• $dbname — The database name.
• $username The database User.
• $password — The database password for the User.

A Function that will execute all statements // execute statement private function executeStatement( $statement = «» , $parameters = [] )< try< $stmt = $this->connection->prepare($statement); $stmt->execute($parameters); return $stmt; >catch(Exception $e)< throw new Exception($e->getMessage()); > > We will be passing our SQL Statements to this function (Insert, Select, Update and Remove).
Returns a PDOStatement object or throws an exception if it get’s an error.

Insert Function // Insert a row/s in a Database Table public function Insert( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); return $this->connection->lastInsertId(); >catch(Exception $e)< throw new Exception($e->getMessage()); > > Insert will add a row and will return an integer of the last ID inserted or throws an exception if it get’s an error.

Select Function // Select a row/s in a Database Table public function Select( $statement = «» , $parameters = [] )< try< $stmt = $this->executeStatement( $statement , $parameters ); return $stmt->fetchAll(); >catch(Exception $e)< throw new Exception($e->getMessage()); > > Select will return all row/s or throws an exception if it get’s an error.

Update Function // Update a row/s in a Database Table public function Update( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); >catch(Exception $e)< throw new Exception($e->getMessage()); > > Update will update a row/s or throws an exception if it get’s an error.

Remove Function // Remove a row/s in a Database Table public function Remove( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); >catch(Exception $e)< throw new Exception($e->getMessage()); > > Remove will remove a row/s or throws an exception if it get’s an error.

Our Database Class example:

class DatabaseClass< private $connection = null; // this function is called everytime this class is instantiated public function __construct( $dbhost = "localhost", $dbname = "myDataBaseName", $username = "root", $password = "")< try< $this->connection = new PDO(«mysql:host=;dbname=;», $username, $password); $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); >catch(Exception $e)< throw new Exception($e->getMessage()); > > // Insert a row/s in a Database Table public function Insert( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); return $this->connection->lastInsertId(); >catch(Exception $e)< throw new Exception($e->getMessage()); > > // Select a row/s in a Database Table public function Select( $statement = «» , $parameters = [] )< try< $stmt = $this->executeStatement( $statement , $parameters ); return $stmt->fetchAll(); >catch(Exception $e)< throw new Exception($e->getMessage()); > > // Update a row/s in a Database Table public function Update( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); >catch(Exception $e)< throw new Exception($e->getMessage()); > > // Remove a row/s in a Database Table public function Remove( $statement = «» , $parameters = [] )< try< $this->executeStatement( $statement , $parameters ); >catch(Exception $e)< throw new Exception($e->getMessage()); > > // execute statement private function executeStatement( $statement = «» , $parameters = [] )< try< $stmt = $this->connection->prepare($statement); $stmt->execute($parameters); return $stmt; >catch(Exception $e)< throw new Exception($e->getMessage()); > > >

Using the Database Class:

Create/Instantiate the Database Class. $db = new Database( «MySQLHost», «myDatabaseName», «myUserName», «myUserPassword» ); Insert Example $id = $db->Insert(«Insert into `TableName`( `column1` , `column2`) values ( :column1 , :column2 )», [ ‘column1’ => ‘column1 Value’, ‘column2’ => ‘column2 Value’, ]); Select Example $db->Select(«Select * from TableName»); Update Example $db->Update(«Update TableName set `column1` = :column1 where ‘id’ => 1, ‘column1’ => ‘a new column1 value’ ]); Remove Example $db->Remove(«Delete from TableName where ‘id’ => 1 ]);

Tips:

• Minimize connections to your server.

Take this as an example: for( $x = 1; $x Select(«Select * from TableName where // do something with $data > The above code will create 1000 connections and this could lead to your server to slowing down.

A better way to do this is to create the DatabaseClass object before the looping: $db = new Database( «MySQLHost», «myDatabaseName», «myUserName», «myUserPassword» ); for( $x = 1; $x Select(«Select * from TableName where // do something with $data > The above code will create 1 connection and will use it inside the loop.

I also have created a PHP MySQLi version of this class. You can read how i created it here Creating a MySqli Database Class in PHP

Источник

Laravel Livewire Crud with Bootstrap Modal Example

Hello Friends, In this blog, I would like to share with you how perform crud opeartion with bootstrap modal livewire in laravel application.I will.

PHP MySql OOP CRUD Example

In This Blog, I would like to share perform crud operation using oop in PHP application. We will perform crud opeartion like create read upadte delete operation in PHP oop.

CRUD is basic step of any core language framework. CRUD stands for Create Read Update and Delete. So In this blog we will learn you insert upadte and delete in PHP oop with mysql database.

You just need to follow few step and you will get basic crud using php oop with mysql database.

Here I will give you full example for crud operation example in OOP PHP. So, let’s follow few step to create example of PHP oop crud application tutorial.

Following file Structure

CREATE TABLE `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `salary` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now, We will create class for MySQL database connections and CRUD operations, like Select, insert, update and delete with MySQL database. We’ll have a displayData() method for selecting customer records, an insertData() method for inserting customer records, an updateRecord() method for updating customer data, and a deleteRecord() method for deleting customer records. and displayRecordById() method for fetch single record. Here is a complete Employee class with all its methods. All you need to do is change the MySQL database connection details while it is running on your server.

con = new mysqli($this->servername, $this->username,$this->password,$this->database); if(mysqli_connect_error()) < trigger_error("Failed to connect to MySQL: " . mysqli_connect_error()); >else< return $this->con; > > // Insert customer data into customer table public function insertData($post) < $name = $this->con->real_escape_string($_POST['name']); $email = $this->con->real_escape_string($_POST['email']); $salary = $this->con->real_escape_string($_POST['salary']); $query="INSERT INTO customers(name,email,salary) VALUES('$name','$email','$salary')"; $sql = $this->con->query($query); if ($sql==true) < header("Location:index.php?msg1=insert"); >else < echo "Registration failed try again!"; >> // Fetch customer records for show listing public function displayData() < $query = "SELECT * FROM customers"; $result = $this->con->query($query); if ($result->num_rows > 0) < $data = array(); while ($row = $result->fetch_assoc()) < $data[] = $row; >return $data; >else < echo "No found records"; >> // Fetch single data for edit from customer table public function displyaRecordById($id) < $query = "SELECT * FROM customers WHERE "; $result = $this->con->query($query); if ($result->num_rows > 0) < $row = $result->fetch_assoc(); return $row; >else < echo "Record not found"; >> // Update customer data into customer table public function updateRecord($postData) < $name = $this->con->real_escape_string($_POST['uname']); $email = $this->con->real_escape_string($_POST['uemail']); $salary = $this->con->real_escape_string($_POST['usalary']); $id = $this->con->real_escape_string($_POST['id']); if (!empty($id) && !empty($postData)) < $query = "UPDATE customers SET name = '$name', email = '$email', salary = '$salary' WHERE "; $sql = $this->con->query($query); if ($sql==true) < header("Location:index.php?msg2=update"); >else < echo "Registration updated failed try again!"; >> > // Delete customer data from customer table public function deleteRecord($id) < $query = "DELETE FROM customers WHERE "; $sql = $this->con->query($query); if ($sql==true) < header("Location:index.php?msg3=delete"); >else < echo "Record does not delete try again"; >> > ?>

Now we will insert customer record using insertData() method from customers Class. For this, we will design a customer HTML Form in add.php file.

insertData($_POST); > ?>         
PHP MySql OOP CRUD Example Tutorial
 
 
 
 
 
 
 
Insert Data
 
 
  
Name: 
Email 
Salary: 
 
 
 
 
     

Now we will display customer records using displyaData() method from customers Class

deleteRecord($deleteId); > ?>         
PHP MySql OOP CRUD Example Tutorial
 

 
 
 × Your Registration added successfully
 "; > if (isset($_GET['msg2']) == "update") < echo "
× Your Registration updated successfully
 "; > if (isset($_GET['msg3']) == "delete") < echo "
× Record deleted successfully
 "; > ?> 
View Records
 displayData(); foreach ($customers as $customer) < ?>  ?>
 
 
Id
 
Name
 
Email
 
Salary
 
Action
 
 
 
 
 
 
 
 
">  " onclick="confirm('Are you sure want to delete this record')">  
 
 
 

displyaRecordById($editId); > // Update Record in customer table if(isset($_POST['update'])) < $customerObj->updateRecord($_POST); > ?>         
PHP MySql OOP CRUD Example Tutorial
 
 
 
 
 
 
 
Update Records
 
 
 
 
Name: " required="">
 
Email " required="">
 
Salary: " required="">
 
">