 Photo by [Kat Yukawa](https://unsplash.com/@katyukawa?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral) ### 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. ### [Download Complete Source Code](https://github.com/phpcontrols/donation-manager) ### System Requirements * PHP 7+ * MySQL or MariaDB * phpGrid * phpChart (Part II) ### 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 are 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](https://hackernoon.com/tagged/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 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](https://hackernoon.com/tagged/phpgrid) datagrid component handles all internal database **CRUD** (**C**reate, **R**emove, **U**pdate, and **D**elete) operations for us, offering faster and better results with minimal coding. Be sure to [download a free copy of phpGrid](https://phpgrid.com/download/) 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  Photo by [Hal Gatewood](https://unsplash.com/@halgatewood?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral) 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. ### Donations Page 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 > 1\. On the first line, you create the phpGrid object to be displayed. > 2\. Then you call [display()](https://phpgrid.com/documentation/display/) to render the resulting datagrid on the webpage. You can learn more about how the basic datagrid works [here](https://phpgrid.com/example/example-1-a-basic-php-datagrid-2/). 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()](https://phpgrid.com/documentation/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()](https://phpgrid.com/documentation/set_col_currencycol_name-prefix-suffix-thousandsseparator-decimalseparator-decimalplaces2-defaultvalue0-00/). 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()](https://phpgrid.com/documentation/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 Page  Photo by [Akshay Chauhan](https://unsplash.com/@akshayspaceship?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral) The second page is the Donors page. It contains a list of donors and donation summary information. A [one-to-many](https://en.wikipedia.org/wiki/Master%E2%80%93detail_interface) 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](https://www.w3schools.com/sql/sql_join_inner.asp) 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 entire 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](https://www.w3schools.com/SQl/func_mysql_concat.asp), a SQL function that concatenates two or more strings. In MySQL, it has the following syntax: To create the donor’s full name, we also add a blank character after FirstName, which adds a space between the first and last names. We also want to display the total amount of donated by a given donor. This is where SQL [aggregation functions](https://en.wikipedia.org/wiki/Aggregate_function) come in handy. In MySQL, you can use the [SUM()](https://www.w3schools.com/sql/sql_count_avg_sum.asp) 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](https://www.w3schools.com/sql/sql_groupby.asp) 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](https://phpgrid.com/documentation/construct-our-phpgrid/) that uses the above SQL statement and the [display()](https://phpgrid.com/documentation/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()](https://phpgrid.com/documentation/set_col_hidden/) function. Each donor has an email. We can easily make this field an email hyperlink using [set\_col\_format()](https://phpgrid.com/documentation/set_col_format/). The `TotalDonation` column is a currency field. We can change this column to currency format using [set\_col\_currency()](https://phpgrid.com/documentation/set_col_currencycol_name-prefix-suffix-thousandsseparator-decimalseparator-decimalplaces2-defaultvalue0-00/). 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()](https://phpgrid.com/documentation/enable_editedit/) 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](https://phpgrid.com/example/master-detail-grid/) 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()](https://phpgrid.com/documentation/set_masterdetail/). 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](https://phpgrid.com/tag/master-detail/) and how to use them beyond the scope of this tutorial is also available. ### Campaigns Page  Photo by [rawpixel](https://unsplash.com/@rawpixel?utm_source=medium&utm_medium=referral) on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral) 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](https://phpgrid.com/documentation/set_masterdetail/) 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](https://www.w3schools.com/sql/sql_join_inner.asp). 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()](https://www.w3schools.com/sql/sql_count_avg_sum.asp) 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()](https://www.w3schools.com/sql/sql_count_avg_sum.asp). 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](https://www.w3schools.com/sql/sql_groupby.asp) 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()](https://phpgrid.com/documentation/enable_global_search/) which enables a multi-fields global search on any searchable columns. That’s all it takes. $dgCamp->enable_global_search(true); 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](https://phpgrid.com/example/master-detail-grid/) 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()](https://phpgrid.com/documentation/set_masterdetail/). 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 Part II. Stay tuned! ### [Download Complete Source Code](https://github.com/phpcontrols/donation-manager) ### Thanks for reading. If you enjoyed this article, please hit that clap button 👏 to help others find it.