How to Make Simple CRUD in PHP and MySQL Bootstrap

In this tutorial, I'll use Bootstrap to show you CRUD (Create, Read, Update, Delete) operations in PHP with PDO.

The four most basic data storage operations are: create, read, update, and delete. Any application that manipulates data directly. All four components will be implemented in PHP combination codes in this tutorial.

In this tutorial, I'll create a person table and perform these four CRUD procedures on it, as well as customize the records.

I have used PDO's latest extension of PHP in this tutorial. If you don't know about the principle of PDO, this tutorial has quickly solved your PDO-related question, PDO benefits, how to allow PDO drivers, and provides useful guidance. Go have a quick look and a ton of easy tutorials.

crud operations in php with pdo using bootstrap

Table Content

1. About PDO [ PHP Data Object ]

1.1 Benefits Of Use PDO

1.2 How To Enable PDO Driver

2. Project Set-Up In XAMPP Server

3. Database / Table Creating

4. Database Connection Utility

5. index.php [ read records ]

6. add.php [ create/insert records ]

6.1 PHP Insert Code With PDO

7. update.php [ update records ]

7.1 PHP Update Code With PDO

8. PHP Delete Code With PDO

1. About PDO [ PHP Data Object ]

PDO is set up below in the fifth edition of PHP implementing the MySQL extension. Its driver did not only allow access to MySQL to open multiple database connections Oracle, ODBC, Microsoft SQL Server, SQLite, PostgresSQL, and more.

1.1 Benefits Of Use PDO: -

PDO provides its features prepared statements that use this to decrease attacks on SQL injection.

PDO provides the built-in bindParam() and bindValue() function. The bindParam() function binds the placeholder of the parameter in the SQL query that holds a specific variable. The bindValue() function binds the value that the integer, string, and variables can specify.

PDO extension is comparable slightly with other extensions and is a great performer in the object-oriented approach.

Now present time PHP programmer does not use the deprecated MySQL extension MySQL and MySQLi. They all forward to PDO.

1.2 How To Enable PDO Driver: -

Go to the php.ini configuration file and enable the PDO extension driver.

;extension=php_pdo_mysql.dll

remove the semi-column.

extension=php_pdo_mysql.dll

2. Project Set-Up In XAMPP Server

I assume to have a XAMPP server installed. I set up this project in C:\xampp\htdocs\Insert-Update-Delete-PHP-PDO path place, as I have the XAMPP server installed in C: drive. See the project directory below.

3. Database / Table Creating

Import the following SQL code to develop a database and table to store the first name and last name of the person fields.

--
-- Database: `php_pdo_crud_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_person`
--

CREATE TABLE `tbl_person` (
  `id` int(11) NOT NULL,
  `firstname` varchar(15) NOT NULL,
  `lastname` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is a pretty simple table for tracking a person's information, as you can see in the table structure image (first name, and last name). We also desire a primary key that is automatically incremented (id).

Make a person named table in MySQL database

After developing a database, we need to make the following PHP files:

index.php – Retrieve [read] records in this file from MySQL table.

connection.php – Open connection of MySQL database to PDO extension in this file.

add.php – In this file insert [create] new person records in a table.

edit.php – Edit [update] records of a particular person in this file.

4. Database Connection Utility

Following the creation of the table, we need to write a PHP script to connect to the MySQL database server.

Simple and Quick Database setup code of PDO extension, modify the below file according to your database credentials, and save its name according to your criteria.

<?php
$db_host="localhost"; //localhost server 
$db_user="root"; //database username
$db_password=""; //database password   
$db_name="php_pdo_crud_db"; //database name

try
{
 $db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
 $e->getMessage();
}

?>

For a better UI experience, I designed all of the pages in this tutorial using the bootstrap package.

I avoided discussing design codes and instead focused on the main codes. The project zip file contains all of the codes and designs. You can get the source code zip file at the end of the tutorial.

5. index.php [ read records ]

First of all, start CRUD operations to retrieve data from the MySQL person table and set this design by bootstrap into table format.

<table class="table table-striped table-bordered table-hover">
    <thead>
        <tr>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Edit</th>
            <th>Delete</th>
        </tr>
    </thead>
    <tbody>
 <?php
 $select_stmt=$db->prepare("SELECT * FROM tbl_person"); //sql select query
 $select_stmt->execute();
 while($row=$select_stmt->fetch(PDO::FETCH_ASSOC))
 {
 ?>
        <tr>
            <td><?php echo $row['firstname']; ?></td>
            <td><?php echo $row['lastname']; ?></td>
            <td><a href="edit.php?update_id=<?php echo $row['id']; ?>" class="btn btn-warning">Edit</a></td>
            <td><a href="?delete_id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a></td>
        </tr>
    <?php
 }
 ?>
   </tbody>
</table>

This type of UI table can be seen on the index page.

Read Data from the Database and Set-Up in a Bootstrap Table

6. add.php [ create / insert records ]

Simple HTML form make with bootstrap, first name and last name are the fields I took here for a person.

The $errorMsg and $insertMsg variables, which display messages, can be found above the form tag.

The variable $errorMsg displays relevant error messages. The $insertMsg variable displays a successful message indicating that a new record in the database has been created.

Both error messages and successful record insert messages are generated by PHP inset codes.

<?php
if(isset($errorMsg)){
?>
    <div class="alert alert-danger">
        <strong>WRONG ! <?php echo $errorMsg; ?></strong>
    </div>
<?php
}
if(isset($insertMsg)){
?>
 <div class="alert alert-success">
  <strong>SUCCESS ! <?php echo $insertMsg; ?></strong>
 </div>
<?php
}
?>
<form method="post" class="form-horizontal">
     
 <div class="form-group">
 <label class="col-sm-3 control-label">Firstname</label>
 <div class="col-sm-6">
 <input type="text" name="txt_firstname" class="form-control" placeholder="enter firstname" />
 </div>
 </div>
      
 <div class="form-group">
 <label class="col-sm-3 control-label">Lastname</label>
 <div class="col-sm-6">
 <input type="text" name="txt_lastname" class="form-control" placeholder="enter lastname" />
 </div>
 </div>
   
 <div class="form-group">
 <div class="col-sm-offset-3 col-sm-9 m-t-15">
 <input type="submit"  name="btn_insert" class="btn btn-success " value="Insert">
 <a href="index.php" class="btn btn-danger">Cancel</a>
 </div>
 </div>
     
</form>

Insert Form :

6.1 PHP Insert Code With PDO

I have added the below PHP script beginning above the <! DOCTYPE html> tag. The person's first name and last name will be inserted into this script. If any failure has arisen, the bootstrap alert will display a relevant message.

<?php

require_once "connection.php";

if(isset($_REQUEST['btn_insert']))
{
 $firstname = $_REQUEST['txt_firstname']; //textbox name "txt_firstname"
 $lastname = $_REQUEST['txt_lastname']; //textbox name "txt_lastname"
  
 if(empty($firstname)){
  $errorMsg="Please Enter Firstname";
 }
 else if(empty($lastname)){
  $errorMsg="Please Enter Lastname";
 }
 else
 {
  try
  {
   if(!isset($errorMsg))
   {
    $insert_stmt=$db->prepare('INSERT INTO tbl_person(firstname,lastname) VALUES(:fname,:lname)');      
    $insert_stmt->bindParam(':fname',$firstname);
    $insert_stmt->bindParam(':lname',$lastname);   
     
    if($insert_stmt->execute())
    {
     $insertMsg="Insert Successfully........"; 
     header("refresh:3;index.php"); 
    }
   }
  }
  catch(PDOException $e)
  {
   echo $e->getMessage();
  }
 }
}

?>

Codes Explanation:

To make things easy, we'll initialize variables that need to be placed into the person table at the start of the script in the current script. These would be derived from user input in the $_REQUEST[ ] array variable, which would be submitted via a form.

I have used the empty() functions to set the values of the variables that aren't blank.

After that, I created the PDO insert query in the prepare statement.

The bindParam() function in the insert query binds the :fname and :lname parameter values to the $firstname and $lastname variables.

Finally, if there are no errors, the execute() function will run our insert query and add new records to the person table.

Within 3 seconds, the header() method displays the insert successfully message and redirects to the index page.

7. edit.php [ update records ]

I have covered how to insert new records in the MySQL table in the previous section. Now I will show you how to update an existing record in the table in this part.

The editing form is as simple as bootstrap designing an insert form. When you press the edit hyperlink, this form will obtain specific data for editing from the database.

<td><a href="edit.php?update_id=<?php echo $row['id']; ?>" class="btn btn-warning">Edit</a></td>

The $errorMsg and $updateMsg variables in the above form tag display suitable messages generated by PHP update operation codes.

<?php
if(isset($errorMsg)){
?>
    <div class="alert alert-danger">
        <strong>WRONG ! <?php echo $errorMsg; ?></strong>
    </div>
<?php
}
if(isset($updateMsg)){
?>
 <div class="alert alert-success">
  <strong>UPDATE ! <?php echo $updateMsg; ?></strong>
 </div>
<?php
}
?>
<form method="post" class="form-horizontal">
     
 <div class="form-group">
 <label class="col-sm-3 control-label">Firstname</label>
 <div class="col-sm-6">
 <input type="text" name="txt_firstname" class="form-control" value="<?php echo $firstname; ?>">
 </div>
 </div>
     
 <div class="form-group">
 <label class="col-sm-3 control-label">lastname</label>
 <div class="col-sm-6">
 <input type="text" name="txt_lastname" class="form-control" value="<?php echo $lastname; ?>">
 </div>
 </div>
      
 <div class="form-group">
 <div class="col-sm-offset-3 col-sm-9 m-t-15">
 <input type="submit" name="btn_update" class="btn btn-primary" value="Update">
 <a href="index.php" class="btn btn-danger">Cancel</a>
 </div>
 </div>
   
</form>

When you click the edit hyperlink, the update form will appear visually below this type.

Update Form :

7.1 PHP Update Code With PDO

I put the PHP update code before <! DOCTYPE html > tag in file 'update.php'. If data is selected for editing then old records will be removed and person new records updated, the same PHP script here's.

 <?php

require_once "connection.php";

if(isset($_REQUEST['update_id']))
{
 try
 {
  $id = $_REQUEST['update_id']; 
  $select_stmt = $db->prepare('SELECT * FROM tbl_person WHERE id =:id'); 
  $select_stmt->bindParam(':id',$id);
  $select_stmt->execute(); 
  $row = $select_stmt->fetch(PDO::FETCH_ASSOC);
  extract($row);
 }
 catch(PDOException $e)
 {
  $e->getMessage();
 }
 
}

if(isset($_REQUEST['btn_update']))
{
 
 $firstname_up = $_REQUEST['txt_firstname']; //textbox name "txt_firstname"
 $lastname_up = $_REQUEST['txt_lastname']; //textbox name "txt_lastname"
  
 if(empty($firstname_up)){
  $errorMsg="Please Enter Firstname";
 }
 else if(empty($lastname_up)){
  $errorMsg="Please Enter Lastname";
 } 
 else
 {
  try
  {
   if(!isset($errorMsg))
   {
    $update_stmt=$db->prepare('UPDATE tbl_person SET firstname=:fname_up, lastname=:lname_up WHERE id=:id'); 
    $update_stmt->bindParam(':fname_up',$firstname_up);
    $update_stmt->bindParam(':lname_up',$lastname_up); 
    $update_stmt->bindParam(':id',$id);
     
    if($update_stmt->execute())
    {
     $updateMsg="Record Update Successfully......."; 
     header("refresh:3;index.php"); 
    }
   } 
  }
  catch(PDOException $e)
  {
   echo $e->getMessage();
  } 
 } 
}
?>

Codes Explanation:

The update_id variable receives a specific table row id value when you click the edit hyperlink. I have retrieved this variable id value request using the $_REQUEST[ ] array variable.

The value of the update_id variable I assigned to the new $id variable.

I select a specific id row record using a SQL select query with the WHERE clause condition.

The bindParam() function binds the value of the :id parameter in a select query maintained in the $id variable. Following that, the execute() function runs a SQL select query.

PDO::FETCH ASSOC specifies that the result should be returned as an array key. The array keys will be the same as the column names in our table.

All keys are assigned to the variable $row. The $row variable is pasted into the extract() function. The array keys are used as variable names, while the values are used as variable values in this function.

Next, look at the updated code, which is similar to the insert codes we studied earlier. Only the query is changed in this code.

In the prepare() statement, I used the update query. The execute() function will execute our update query If there is no error caused.

If the update query was successfully executed, the header() function would display a record update successfully message for 3 seconds before redirecting to the index page.

8. PHP Delete Code With PDO

Next, I have placed the delete code simply over <! DOCTYPE html > tag inside the file 'index.php' file.

When you click the delete hyperlink, the delete_id variable receives a request for deletion with a specified id value.

<td><a href="?delete_id=<?php echo $row['id']; ?>" class="btn btn-danger">Delete</a></td>

First, I used the SQL select query to select the specific id. Following that, I applied the SQL delete query to delete the actual record from the database table.

<?php

require_once "connection.php";
 
if(isset($_REQUEST['delete_id']))
{
 // select record from db to delete
 $id=$_REQUEST['delete_id']; //get delete_id and store in $id variable
  
 $select_stmt= $db->prepare('SELECT * FROM tbl_person WHERE id =:id'); //sql select query
 $select_stmt->bindParam(':id',$id);
 $select_stmt->execute();
 $row=$select_stmt->fetch(PDO::FETCH_ASSOC);
  
 //delete an orignal record from db
 $delete_stmt = $db->prepare('DELETE FROM tbl_person WHERE id =:id');
 $delete_stmt->bindParam(':id',$id);
 $delete_stmt->execute();
  
 header("Location:index.php");
}
 
?>

Tuesday 4 June 2024

How to Make Simple CRUD in PHP and MySQL Bootstrap

Table Content

1. About PDO [ PHP Data Object ]

1.1 Benefits Of Use PDO: -

1.2 How To Enable PDO Driver: -

2. Project Set-Up In XAMPP Server

3. Database / Table Creating

4. Database Connection Utility

5. index.php [ read records ]

6. add.php [ create / insert records ]

Insert Form :

6.1 PHP Insert Code With PDO

7. edit.php [ update records ]

Update Form :

7.1 PHP Update Code With PDO

8. PHP Delete Code With PDO

Learn More PDO Tutorials:

No comments:

Post a Comment

Post Bottom Ad

Subscribe

Get new tutorials by email:

Like on Facebook

Tags

Labels

Popular posts

Contact Form

Connect With Us

Contact Form

Pages