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.
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 HTML Table with Read operation in CRUD procedure using PDO connection. Here we just get the data from database with the help of PDO connection object.
Step 1: 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 2: Make an HTML Table with table dataset values in a loop. Here PDO object help to get the DB table values and display the value one by one with a foreach loop.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?php $pdo_statement = $pdo_conn->prepare("SELECT * FROM li_ajax_post_load ORDER BY post_id DESC limit 5"); $pdo_statement->execute(); $result = $pdo_statement->fetchAll(); ?> <table border='1'> <thead> <tr> <th class="table-header" width="5%">SNo</th> <th class="table-header" width="20%">Post Title</th> <th class="table-header" width="60%">Description</th> <th class="table-header" width="15%">Actions</th> </tr> </thead> <tbody id="table-body"> <?php $snno = 1; if(!empty($result)) { foreach($result as $row) { ?> <tr class="table-row"> <td><?php echo $snno++; ?></td> <td><?php echo $row["post_title"]; ?></td> <td><?php echo $row["post_desc"]; ?></td> <td align="center"> <a class="btn btn-sm btn-primary" href='view_post.php?post_id=<?php echo $row['post_id']; ?>'><i class="fa fa-eye"></i></a> <a class="btn btn-sm btn-warning" href='edit_post.php?post_id=<?php echo $row['post_id']; ?>'><i class="fa fa-edit"></i></a> <a class="btn btn-sm btn-danger" href='delete_post.php?post_id=<?php echo $row['post_id']; ?>'><i class="fa fa-trash"></i></a> </td> </tr> <?php } } ?> </tbody> </table> |
Step 3: Here we fetch the single record details with the help of primary key id value using PDO object, and get the result and store into a variable object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php $post_id = $_REQUEST['post_id']; $pdo_statement = $pdo_conn->prepare("SELECT * FROM li_ajax_post_load where post_id=" . $post_id); $pdo_statement->execute(); $result = $pdo_statement->fetchAll(); ?> <table border='1' class="cust-table"> <tr> <th width="20%">Post Title</th><td width="60%"><?php echo $result[0]['post_title']; ?></td> </tr> <tr> <th>Post Description</th><td><?php echo $result[0]['post_desc']; ?></td> </tr> </table> |
