Introduction

We will learn how to build a fully functional, single-page database content administration system using phpGrid and other PHP components with a minimum amount of code. The application will perform common data operations such as Create, Read, Update, Delete, otherwise known as CRUD on MySQL database tables.

Note the demo application requires phpGrid version 7 and above. The demo uses MySQL, however, the same basic code should work with other databases with the use of a different SQL schema SELECT statement.

Design

Our application will have a list of database schemas and tables on the left side – the sidebar – and a database table on the main screen – the main. Both the sidebar and the main are datagrids. On the main datagrid, which contains the database table, we will also add a search bar so users can search within the displayed grid.

Our design mockup:

2-Column Layout

Let’s start by creating the layout. What we need is a fixed-width sidebar on the left while the main – a grid – stays “fluid” so it fills the remaining document width. The main grid will be populated with data from the database table selected from the left sidebar.

Luckily, CSSPortal has an excellent layout tool which easily generates a column-based layout with only point and click.

http://www.cssportal.com/layout-generator/

An alternative is to download this boilerplate (File -> Save As) which contains all the HTML and CSS code needed for this demo application.

Setup

Let’s start coding. Presumably, you have already installed phpGrid on your web server. If not, follow this guide to learn how – http://phpgrid.com/documentation/installation/

First, add the following lines to the TOP of our page

The first line adds the phpGrid namespace so PHP knows where to find phpGrid in the system. Otherwise, PHP will throw a nasty error – “Fatal error: Class ‘C_DataGrid’ not found”.

The second line references phpGrid by including its conf.php. The Installation Guide – http://phpgrid.com/documentation/installation/ – explains this in greater length.

Sidebar

The left sidebar contains a list of the tables found in each respective database in a subgrid.

Sidebar – Step 1

The first step is to return a list of current schemata from the selected database. In MySQL, the following SQL statement retrieves a list of schema names.

The PHP code to return a list of schemas is as follows:

How the above code works:

set_dimension sets the width of the left sidebar to 300 pixels.
set_pagesize sets the height of the page to a very large number, e.g. 999. Thus, all data will be displayed on a single page. set_scroll removes the pagination controls normally found in the bottom footer.

Sidebar – Step 2

The second step displays a list of available database tables in each schema in a subgrid.

The PHP code to return a list of tables appears below.

How the above code works:

First, we hide the TABLE_SCHEMA column because it is redundant. The page size is set to a large number (999) and scroll is set to true. Finally, and  most importantly, set_col_dynalink forms a dynamic URL for each database table used to populate the main datagrid.  You can learn more about the set_col_dynalink function here: http://phpgrid.com/documentation/set_col_dynalink/

Sidebar – Step 3

Lastly, set the subgrid. The subgrid displays an inline detail grid for each row in the parent grid. If you are not familiar with phpGrid subgrids, check out the subgrid demo online – http://phpgrid.com/example/subgrid/.

Main Column

The main column contains the datagrid for the database table. Recall that in the code to create the sidebar, Step 2, we used a function called “set_col_dynalink” which set a dynamic URL for every database table name in the URL query string. Now we need to retrieve the schema name and table name by using the following code:

The first line of code gets the schema name and the second gets the table name. There’s nothing special about this except we set a default value for each variable to be used in cases where the parameters are missing – such as as when the page is first loaded.

Below is the code for the main grid:

How the above code works:

The first line uses phpGrid’s multiple database reference feature. This is essential because we need to reference different tables found in different databases. See http://phpgrid.com/example/multiple-databases-support/ for more information about how phpGrid can be configured to work with multiple databases.

Note that it is important to use the correct corresponding primary key when referencing the default database table.  In this case, we are referencing the “orders” table with the primary key “orderNumber”.

enable_global_search adds the global search bar to top of the grid. Finally, the last line displays our datagrid.

That’s all there is to it! You now have a web-based database content management console using fewer than 15 lines of code.

But Wait, There’s More!

We can beef up the grid and take advantage of the fluid layout design by adding a few more lines of code.

First, we add a caption to show the current schema and table name. Then, we set the auto width, enable edit and then add a global search to the top.

Finally, before we go, let’s add the following javascript event to make sure that the grid will only resize in its container and not in the browser window itself.

Summary

Congrats! You have built a content management system for your database!