In this part of the tutorial, we will continue from where we left off in Build a Donation Manager Part I. You will learn how to use phpChart, an easy-to-use charting and graphing component, that will seamlessly integrates with phpGrid to create professional-looking, interactive reports for your Donation Manager application.

Before we start, we need to install phpChart. To fully benefit from this tutorial, I recommend you obtain the full version of phpChart since the free version – phpChart Lite – supports only the line chart.

Setup phpChart

It is very important that phpGrid and phpChart be kept in its separate folders. Below is the recommended folder hierarchy.

That’s it.

Report Design

Our report consists of a bar chart and a pie chart followed by a campaign summary datagrid. The datagrid provides the data used to plot the two charts.

phpGrid phpChart Integration

First of all, include both conf.php files (phpGrid and phpChart) at the beginning of the code.

Bar Chart

The first chart we will create is the bar chart. Below is the complete code to create an EMPTY bar chart. We first must create an empty chart is because the campaign data used to plot the chart is not available at the time when the chart is initialized.

Let’s break this baby apart. For the sake of simplicity, we will only cover high level implementation and skip over details where the code is actually self-explanatory.

The first line is the constructor. We pass an empty array, array(null), as the series data because we don’t wish to have any data displayed in the bar chart when it is first rendered. We also give our graph an unique name, BarGraph.

Next, we use the add_plugins() function to include some additional new elements to our bar chart. For this tutorial, we will include the canvasTextRenderer, canvasAxisTickRenderer, and pointLabels plugins. A list of supported plugins can be found here.

In the series default function, be sure to set the renderer type to BarRenderer. We can also set the rendererOptions and pointLabels properties here.

A bar chart has two sets of data – one set for the x axis and another for the y. In this example, the x axis shows the total amount donated to a given campaign and the y axis displays the name of the campaign. We can set the properties for both x and y in one statement by using the set_axes() function. Notice how label, renderer, and ticks properties are used.

Complete documentation for phpChart methods and properties documentation can be found online.

Finally, the last line reserves space for a chart that is 600px high and 400px wide.

Pie Chart

Now let’s move on to the second chart in our report. Below is the entire code to create an EMPTY pie chart.

Let’s do a quick review of the above script.

The first line is the phpChart constructor. Again, the first parameter is an empty array, array(null), as the series data will be loaded later from the Campaign Summary datagrid.

We then set the renderer to PieRenderer and set the its properties using the series default function.

We also want to display the legend to the west, or left, of the chart.

Lastly, we draws the pie chart.

You should now have two blank charts on the page.

Campaign Summary Datagrid

The Campaign datagrid used in this report is the same as what was used to create the Campaign page in Part 1. We just need to add one more thing – an event handler.

In phpGrid, we can add an event handler with the add_event() function. add_event() binds an event handler, which is essentially a JavaScript function, for a specific phpGrid event. A list of possible events can be found here.

Since we must wait for the datagrid to finish loading before sending its data needed to plot the charts, we use the event jqGridLoadComplete.

phpGrid 101 – jqGridLoadComplete Event

jqGridLoadComplete is the last event that occurs once the whole datagrid body has finished loading. Note that the grid body will be reloaded if the user changes the sorting of a column or sets a filter.

Send Data Using Javascript

The following is the Javascript event handler for jqGridLoadComplete.

The Javascript function is the bread and butter that makes our report come alive. It requires a certain level of knowledge of basic Javascript.

Right off the bat, we declare two empty arrays to store x and y axis data points. Since we are now using JavaScript, the syntax will be different from that used in PHP.

The following two lines return data from the datagrid column TotalDonation and CampaignName:

The for loop iterates each column and then stores the results in the x and y axis arrays.

Next, we assign the barData from the datagrid to the bar chart object. Lastly always call jqPlot replot function to load the chart.

phpChart 101 – JavaScript Chart Object Name

The JavaScript chart object name is the same as the name used in the phpChart constructor, e.g. BarChart, prefixed with the underscore character “_”.

We have just completed our bar chart.

Next, we move on to the pie chart. The JavaScript code for this is similar to what we used for the bar chart except that a pie chart does not have x and y axes. Instead, it uses a two-dimensional array.

That’s all there is to it!

Conclusion

Hopefully, after finishing this tutorial, you have become a true campaign master yourself – one who is capable of developing a fully functional donation manager system from start to finish.

* Included in Ultimate edition.

The post A Step-by-Step Guide to Building a Donation Manager from Scratch in PHP – Part II appeared first on phpGrid - PHP Datagrid.

Introduction

This Donations Manager application can be used by charitable organizations to manage the processing of donations as well as ensure accurate data entry and seamless communications with donors. In addition, we will include a donation summary report with charts in our application.

System Requirements

• PHP 5.3+
• MySQL or MariaDB
• phpGrid
• phpChart

Database Overview

Our Donation Manager system has four objects.

• Donor – Personal information about the donor.
• Donation – The amount donated for a particular campaign
• Campaign – Donation campaign information
• Organization – The organization that manages the donations

Our database is simple and straightforward. Each table is an entity that represents one of the above objects.

Programming Terminology – Object or Entity

An entity is a database term that’s typically represented by a table in a database schema.
An object is an object-oriental programming term often mapped to a database table.

In our data model, donations is used to store the information about donations; campaigns is used to compile campaign data and information about the campaign; the organization managing the campaign is stored in org.

Set up the Donation Manager Database

Obtain the donation_manager_db.sql SQL script from this tutorial’s GitHub repo, and then execute the SQL script using a MySQL tool such as MySQL Workbench or Sequel Pro. This will create a new database named donation_manager and the tables we will need in this tutorial.

Setup phpGrid

Our simple project management application contains many datagrids. A datagrid is a spreadsheet-like data table that displays rows and columns which representing records and fields stored in the database table. These grids provide the end-user with the ability to read and write to the database tables from a web page.

The phpGrid datagrid component handles all internal database CRUD (Create, Remove, Update, and Delete) operations for us, offering faster and better results with minimal coding.

Be sure to download a free copy of phpGrid before you proceed.

To install phpGrid, follow these steps:

1. Unzip the phpGrid download file.
2. Upload the phpGrid folder to the phpGrid folder.
3. Complete the installation by configuring the conf.php file.

Before we begin coding, we must include the following information in conf.php, the phpGrid configuration file.

UI Design

The basic design is simple. It is composed of a single-level top menu bar and a datagrid. In the Reports section, we will add charts using the phpChart.

Menus

The Donations Manager has four menu items. Each item represents a corresponding table in the Donation Manager database.

The include file for the menu is stored in the inc folder named menu.php. The code for the menu is straightforward. For the sake of focus, we will not go into great detail. Feel free to look at the content stored inside the inc folder.

We have also added a menu item named Reports which we will address in Part II of this tutorial.

Pages

We will use the same page template we used for the CRM and Project Management tutorials.

Donations

Our first Donation Manager page is the Donations page on which we display donation information retrieved from the Donations table in a datagrid.

Remember that each table in the Donation Manager database has the id as the primary key. So, we can easily create our first datagrid with the following two lines of code.

phpGrid 101 – A Basic Datagrid

A basic PHP datagrid requires only as little as TWO lines of code.

1. On the first line, you create the phpGrid object to be displayed.
2. Then you call display() to render the resulting datagrid on the webpage.

You can learn more about how the basic datagrid works here.

Here’s what our Donations datagrid looks like:

Now, we will make a few enhancements to the Donations datagrid.

First of all, we don’t need to show the Donations primary key id as it is only used internally and is meaningless to our users. We can hide it using set_col_hidden().

Next, the Amount column is a currency value. We can format this column to be displayed as currency using set_col_currency().

We also want our datagrid to display a human-readable Campaign description and Donor name instead of displaying meaningless integers in columns CampaignId and DonorId. We can easily obtain the campaign description and donor name from Campaigns and Donors table respectively using the set_col_edittype() function.

We also want to make the Donations grid editable. We can do this by adding the line:

The complete code for the Donations datagrid:

Here’s how the Donations datagrid looks after our enhancements:

Looks good, doesn’t it? Let’s move on!

Donors

The second page is the Donors page. It contains a list of donors and donation summary information.

A one-to-many relationship exists between the table Donors and Donations because one donor can make one or more donations. The Donations table has a foreign-key field donorid which references the Donors table.

We can join both tables, Donors and Donations by using an INNER JOIN to populate our Donations datagrid.

SQL 101 – INNER JOIN

Use an INNER JOIN to create a one-to-many relationship between two database tables. The table on the “one” side of the “one-to-many” relationship should have a primary key column. The other table has a foreign key that points to the primary key in the first table.

Here’s our SQL statement.

To make our data look more interesting, we create a full name by concatenating the first name FirstName and last name LastName fields with CONCAT, a SQL function that concatenates two or more strings. In MySQL, it has the following syntax:

We also want to display the total amount of donated by a given donor. This is where SQL aggregation functions come in handy. In MySQL, you can use the SUM() function to return the total sum of a numeric column. We name our total TotalDonation.

Because we want to display the summary information, we need to add a GROUP BY statement that groups identical data in the result-set. In this case, we assume that if the Address and Email fields are them same it means they refer to the same donor. The GROUP BY clause groups records into summary rows for each group. It is typically used with aggregate functions such as COUNT, MAX, MIN, SUM, and AVG.

Putting it all together, here’s the final SQL statement that we will use to create our datagrid.

Just in case you were wondering about what the d and dn are for in the SQL statement, they are the SQL table aliases.

SQL 101 – Table Alias

SQL aliases are temporary names used for table or column. Aliases are often used to make column or table names more readable. It only exists for the duration of the query.

Donors Datagrid

Below is the code for our Donors datagrid. It is composed of a phpGrid constructor that uses the above SQL statement and the display() function.

Our Donations datagrid looks like this:

Let’s add a few things to improve its usability.

First of all, the first column displays the table’s primary key id. We don’t need to show this to our users. Let’s hide it using the set_col_hidden() function.

Each donor has an email. We can easily make this field an email hyperlink using set_col_format().

The TotalDonation column is a currency field. We can change this column to currency format using set_col_currency().

And both the Name and TotalDonation fields should be read-only.

Finally, we make the Donors datagrid editable I(except for the fields we just made read-only) with the enable_edit() function.

The complete Donors datagrid code:

Here’s how the Donors datagrid looks like after the changes (with edit window open).

Donation Detail Grid

As one-to-many relationship exists between table Donors and Donations, we can easily present this relationship using the phpGrid master detail feature.

Datagrid 101 – Master Detail

A one-to-many relationship between two data models can be presented in a master-detail format using datagrids.

The detail grid is a regular phpGrid datagrid object and can use the same methods as any other datagrid such as description title, sort, and update etc. The detail grid is dynamically rendered based on the row selected from the parent (master) grid.

We will simply reuse the code from Donations datagrid we created earlier in this tutorial.

Note that we did NOT include the last line $dgDonations->display().

Finally, we set the master-detail relationship in our code using set_masterdetails(). The second parameter is the foreign-key defined in the detail table Donations. The third parameter is the foreign-key defined in the master table Donors.

A series of phpGrid master-detail demos and how to use them beyond the scope of this tutorial is also available.

Campaigns

The Campaigns page contains campaign information and donations details. Note that a given campaign can receive many donations from one or many donors. Again, we can use phpGrid’s master-detail feature as we what did on the Donors page.

Let’s start with the Campaigns.

Since the table Campaigns and Donations have a one-to-many relationship, we can join the two tables by creating an INNER JOIN.

Note that c and dn are the SQL table aliases for the Campaigns and Donations table respectively.

We also want to show the total amount that has been donated to a particular campaign. Again, we can use the SQL aggregation function SUM() to return the total of the Amount column from the Donations table.

One of the key ways to measure the success of a donation campaign, besides the its total amount of donations received, is the total number of the donations. Here, we introduce another useful SQL aggregation function called COUNT().

The COUNT() function, well, as its name suggests, counts the number of rows returned in a SELECT statement. We give it the name DonationCount.

Lastly, since we want to display the campaign summary information, we will add a GROUP BY statement which will group records and print their totals into summary rows for each group.

SQL 101 – GROUP BY & Aggregates

Use GROUP BY whenever you need to use the SQL aggregate functions such as COUNT, MAX, SUM, and AVG.

The complete SQL SELECT statement for our Campaigns datagrid looks like this:

Next, we are going to include a few phpGrid functions to jazz up our Campaigns datagrid.

First of all, as we did with the others, let’s hide the column id.

Then, let’s display our TotalDonation field in a currency format.

Don’t forget to make the Campaigns grid editable.

Lastly, we add a global search by adding the function enable_global_search() which enables a multi-fields global search on any searchable columns. That’s all it takes.

That’s it for the Campaigns grid. Let’s take a look:

The complete code for the Campaigns grid:

Donations Detail Grid

Remember that we mentioned earlier that the Campaigns and Donations table have a one-to-many relationship? We can present their relationship using the phpGrid master detail feature similar to the way what we did for the Donors and Donations tables.

We will simply reuse the same code we used for the Donations datagrid we made earlier in the tutorial.

Note that we did NOT include the last line $dgDonations->display().

Finally, we set the master-detail relationship in our code using set_masterdetails(). Campaigns is the master grid, and Donations is the detail grid.

Thus, the complete code for the Campaigns page is:

This marks the end of the code needed to create the datagrids required for this tutorial. However, we are not done yet. There is still one more page we need to create – Reports. We will cover this section in the Part 2.

Stay tuned!

* Included in Ultimate edition.

 
Update (7/31)
The PART II of this tutorial is now available here!

The post A Step-by-Step Guide to Building a Donation Manager from Scratch in PHP – Part I appeared first on phpGrid - PHP Datagrid.