This post explains how to build a CRUD (Create-Read-Update-Delete) procedures in PHP and MySQL using PDO (PHP Data Objects). PDO is a PHP advance version that implements an intermediate to using MySQL databases in PHP. PDO is compact and strong. There are several great concepts of PDO. The greatest one is, it’s cross-database compatible. You don’t need to change your query function if you switch database server for your project. PDO extension helps different databases like MS SQL, MySQL, Oracle, SQLite, etc.ite, etc.
Error handling is another greater benefit of PDO extension. Using this we can able to write a code with try/catch block. It kept error logs as a file and user-friendly messages are displayed on a page. PDO also help to use prepared statements and stored methods. The important advantage of prepared statements is that you just need to prepare a query once and we can use many times with the same or different parameters.
In this post, we design the form and database table with create operation in CRUD procedure using PDO connection. Here we just post the form data and with the help of PDO connection object and stored in a database.
Step 1: Create a database with required table with columns
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS `li_ajax_post_load` ( `post_id` int(11) NOT NULL AUTO_INCREMENT, `post_title` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `post_desc` varchar(150) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; |
Step 2: Create a PDO connection file with database connection details and link with created database table.
|
1 2 3 4 5 6 7 |
<?php $DB_host = 'localhost'; $DB_username = 'root'; $DB_password = ''; $DB_name = 'li_demo'; $pdo_conn = new PDO( 'mysql:host='.$DB_host.';dbname='.$DB_name, $DB_username, $DB_password ); ?> |
Step 3: Make an HTML Form with required input fields and set the action file path and request method name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<form action="" method="POST"> <div class="form-group"> <label for="post_name" class="required" >Post name:</label> <input type="text" class="form-control" id="post_name" name="post_name" required /> </div> <div class="form-group"> <label for="message" class="required" >Post Description:</label> <textarea type="text" class="form-control" id="post_description" name="post_description" required ></textarea> </div> <div class="clearfix"></div> <div class="form-action-group"> <button type="submit" name="post_comment" class="btn btn-primary btn-form-action">Submit</button> <button type="reset" class="btn btn-danger btn-form-action btn-reset">Clear</button> </div> </form> |
Step 4: Write a PHP script to handle the posted form and save procedure to store the data to a database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<?php if(isset($_POST["post_comment"])) { $sql_query = "INSERT INTO li_ajax_post_load ( post_title, post_desc ) VALUES ( :post_name, :post_desc )"; $pdo_statement = $pdo_conn->prepare( $sql_query ); $result = $pdo_statement->execute( array( ':post_name'=>$_POST['post_name'], ':post_desc'=>$_POST['post_description'] ) ); if (!empty($result) ){ echo '<div class="alert alert-success">Data saved successfully.</div>'; } else{ echo '<div class="alert alert-danger">Data saving failed.</div>'; } } ?> |
