In this article, we’re going to review PDO CRUD—a form builder and database management tool. PDO CRUD helps you build forms for your database tables with just a few lines of code, making it quick and easy to bootstrap a database application.
There are plenty of extensions available for database abstraction and specifically CRUD (create, read, update, and delete) generation for PHP and MySQL. And of course, you’ll also find commercial options that provide ready-to-use features and extended support. In the case of commercial options, you can also expect quality code, bug fixes and new enhancements.
Today, we’re going to discuss the PDO CRUD tool, available at Code Canyon for purchase at a very reasonable price. It’s a complete CRUD builder tool which allows you to build applications by just providing database tables and by writing a few lines of code.
It works with multiple database back-ends, including MySQL, Postgres and SQLite. In this article, we’ll see how to use PDO CRUD to build a CRUD system with the MySQL database back-end.
Installation and Configuration
In this section, we’ll see how to install and configure the PDO CRUD tool once you’ve purchased and downloaded it from Code Canyon.
As soon as you purchase it, you’ll be able to download the zip file. Extract it and you will find the directory with the main plugin code: PDOCrud/script. Copy this directory to your PHP application.
For an example, if your project is configured at /web/demo-app/public_html, you should copy the script directory to /web/demo-app/public_html/script.
Next, you need to enter your database back-end details in the configuration file. The configuration file is located at /web/demo-app/public_html/script/config/config.php. Open that file in your favorite text editor and change the following details in that file.
$config["script_url"] = "http://my-demo-app"; /************************ database ************************/ //Set the host name to connect for database $config["hostname"] = "localhost"; //Set the database name $config["database"] = "demo_app_db"; //Set the username for database access $config["username"] = "demo_app"; //Set the pwd for the database user $config["password"] = "demo_app"; //Set the database type to be used $config["dbtype"] = "mysql"
As you can see that, the details are self-explanatory. The $config["script_url"] is set to the URL which you use to access your site.
Once you’ve saved the database details, you’re ready to use the PDO CRUD tool. In our example, we’ll create two MySQL tables that holds employees and departments data.
- employees: holds employee information
- department: holds department information
Open your database management tool and run the following commands to create tables as we’ve just discussed above. I use PhpMyAdmin to work with the MySQL database back-end.
Firstly, let’s create the department table.
CREATE TABLE `department` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `department_name` varchar(255) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Next, we’ll create the employee table.
CREATE TABLE `employee` ( `id` int(12) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `dept_id` int(11) UNSIGNED NOT NULL, `first_name` varchar(255) NOT NULL DEFAULT '', `last_name` varchar(255) NOT NULL DEFAULT '', `email` varchar(255) NOT NULL DEFAULT '', `phone` varchar(255) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
As you can see, we’ve used the dept_id column in the employee table which holds the id of the corresponding department stored in the department table.
Once you’ve created the tables in your database, we’re ready to build a CRUD application interface using the PDO CRUD tool!
How to Set Up Basic CRUD
In this section, we’ll see how you could set up a basic CRUD interface using the PDO CRUD tool by writing just a few lines of code.
The Department Table
We’ll start with the department table.
Let’s create the department.php with the following contents. If your document root is /web/demo-app/public_html/, create the department.php file at /web/demo-app/public_html/department.php. Recall that we’ve already copied the script directory to /web/demo-app/public_html/script.
dbTable("department")->render();
And now, if you point your browser to the department.php file, you should see something like this:
Phew! With just two lines of code, you have a ready-to-use CRUD UI which allows you to perform all the necessary create, read, update, and delete actions on your model. Not to mention that, the default listing view itself contains a lot of features including:
- search
- built-in pagination
- export records to CSV, PDF or Excel format
- bulk delete operation
- sorting by columns
Click on the Add button on the right hand side, and it’ll open the form to add a department record.
Let’s add a few records using the Add button and see how it looks.
As you can see, this is a pretty light-weight and neat interface. With almost no effort, we’ve built a CRUD for the department model! Next, we’ll see how to do the same for the employee table.
The Employee Table
In this section, we’ll see how to build a CRUD for the employee table. Let’s create the employee.php with the following contents.
dbTable("employee")->render();
It’s pretty much the same code as last time, we just need to change name of the table. If you click on the Add button, it also brings you a nice form which allows you to add the employee record.
You might have spotted one problem: the Dept id field is a text field, but it would be better as a drop-down containing the name of the departments. Let’s see how to achieve this.
getPDOModelObj()->select("department");
$options = array();
foreach($data as $record) {
$options[$record['id']] = $record['department_name'];
}
// change the type of the dept_id field from textfield to select dropdown
$pdocrud->fieldTypes("dept_id", "select");
$pdocrud->fieldDataBinding("dept_id", $options, "", "","array");
echo $pdocrud->dbTable("employee")->render();
In this code we’ve accessed the department table through PDO CRUD so that we can associate the department name with the department ids. Then, we’ve updated the binding options for the department id field so that it will render as a dropdown (select) list.
Now, click on the Add button to see how it looks! You should see the Dept Id field is now converted to a dropdown!
Let’s add a few employee records and see how the employee listing looks:
That looks nice! But, we have another small issue here: you can see that Dept id column shows the ID of the department, and it would be nice to display the actual department name instead. Let’s find out how to achieve this!
Let’s revise the code of the employee.php with the following contents.
getPDOModelObj()->select("department");
$options = array();
foreach($data as $record) {
$options[$record['id']] = $record['department_name'];
}
$pdocrud->fieldTypes("dept_id", "select");
$pdocrud->fieldDataBinding("dept_id", $options, "", "","array");
$pdocrud->crudTableCol(array("first_name","last_name", "department_name", "email","phone"));
$pdocrud->joinTable("department", "employee.dept_id = department.id", "INNER JOIN");
echo $pdocrud->dbTable("employee")->render();
Here, we’ve created a join between the employee and department tables with $pdocrud->joinTable, and then told PDO crud to render only the employee name, department name and contact info with $pdocrud->crudTableCol.
And with that change, the employee listing should look like this:
As you can see, the PDO CRUD script is pretty flexible and allows you every possible option to customize your UI.
So far, we’ve discussed how to set up a basic CRUD interface. We’ll see a few more options that you could use to enhance and customize your CRUD UI in the next section.
Customization Options
In this section, we’ll see a few customization options provided by the PDO CRUD tool. Of course, it’s not possible to go through all the options since the PDO CRUD tool provides much more than we could cover in a single article, but I’ll try to highlight a couple of important ones.
Inline Edit
Inline editing is one of the most important features, allowing you to edit a record quickly on the listing page itself. Let’s see how to enable it for the department listing page.
Let’s revise the department.php script as shown in the following snippet.
setSettings("inlineEditbtn", true);
echo $pdocrud->dbTable("department")->render();
As you can see, we’ve just enabled the inlineEditbtn setting, and the inline editing feature is there right away!
This is really a handy feature which allows you to edit records on the fly!
Filters
As you might have noticed, the department listing page already provides a free text search to filter records. However, you may want to add your own custom filters to improve the search feature. That’s what exactly the Filters option provides as it allows you to build custom filters!
We’ll use the employee.php for this feature as it’s the perfect demonstration use-case. On the employee listing page, we’re displaying the department name for each employee record, so let’s build a department filter which allows you to filter records by the department name.
Go ahead and revise your employee.php as shown in the following snippet.
getPDOModelObj()->select("department");
$options = array();
foreach($data as $record) {
$options[$record['id']] = $record['department_name'];
}
$pdocrud->fieldTypes("dept_id", "select");//change state to select dropdown
$pdocrud->fieldDataBinding("dept_id", $options, "", "","array");//add data using array in select dropdown
$pdocrud->crudTableCol(array("first_name","last_name", "department_name", "email","phone"));
$pdocrud->joinTable("department", "employee.dept_id = department.id", "INNER JOIN");
$pdocrud->addFilter("department_filter", "Department", "dept_id", "dropdown");
$pdocrud->setFilterSource("department_filter", $options, "", "", "array");
echo $pdocrud->dbTable("employee")->render();
We’ve just added two lines, with calls to addFilter and setFilterSource, and with that, the employee list looks like the following:
Isn’t that cool? With just two lines of code, you’ve added your custom filter!
Image Uploads
This is a must-have features should you wish to set up file uploads in your forms. With just a single line of code, you can convert a regular field to a file-upload field as shown in the following snippet.
I’ll assume that you have a profile_image field in your employee table, and that you’re ready to convert it to a file-upload field!
fieldTypes("profile_image", "image");
echo $pdocrud->dbTable("employee")->render();
That’s it! Users will now be able to upload an image to the profile_image field.
CAPTCHA
Nowadays, if you want to save your site from spamming, CAPTCHA verification is an essential feature. The PDO CRUD tool already provides a couple of options to choose from.
It provides two options: CAPTCHA and ReCAPTCHA. If you select the CAPTCHA option, it presents a mathematical puzzle for the user to solve. On the other hand, if you select the ReCAPTCHA option, it presents a famous I’m not a robot puzzle!
If you want to add a simple CAPTCHA puzzle, you need to add the following line before you render your CRUD.
$pdocrud->formAddCaptcha("captcha");
On the other hand, if you prefer ReCAPTCHA, you can achieve the same by using the following snippet.
$pdocrud->recaptcha("your-site-key","site-secret");
You just need to replace the your-site-key and the site-secret arguments with valid credentials from Google.
So far, we’ve discussed options that enhance functionality of your application. Next, we’ll see how you could alter skin and thus look and feel of your application.
Skins
If you don’t like the default skin, you’ve a couple of options to choose from. The PDO CRUD tool provides dark, fair, green and advanced skins as other options to choose from.
For an example, the following listing is based on the green theme.
It looks nice, doesn’t it?
Pure Bootstrap
Although, the default skin already supports responsive layout, the PDO CRUD tool also supports the Bootstrap library integration!
You need to use the following snippet should you wish to build your layout using the Bootstrap library.
dbTable("department")->render();
And here’s what it looks like:
Conclusion
Today, we reviewed the PDO CRUD advanced database form builder and data management tool available at Code Canyon. This is a CRUD application interface builder tool at its core. It provides a variety of customization options that covers almost everything a CRUD system requires.
As I said earlier, it’s really difficult to cover everything the PDO CRUD tool provides in a single article, but hopefully the official documentation should give you some insight into its comprehensive features.
I hope you’re convinced that the PDO CRUD tool is powerful enough to fulfill your requirements and allows you to get rid of the repetitive work you’ve to do every time you want to set up a CRUD in your application. Although it’s a commercial plugin, I believe it’s reasonably priced considering the plethora of features it provides.
If you’ve any suggestions or comments, feel free to use the feed below and I’ll happy to engage in a conversation!
Powered by WPeMatico
