A Case Study: Making Orders Proceed with Order Details in Websites that Generated by PHPMaker 9.1.0

This article was written in order to answer the question in PHPMaker discussion forum. It's about how to make an Order proceed with updating the Order Details. The difficult thing is how to make the ability of when user is selecting the main item, then the detail of items will be changed accordingly below.

The person in the forum initially asking how to create an Order form that contains a Product item selection which will have some Component items related to it. When a Product item is selected, then the Component items that belong to the selected Product will be shown up, afterwards user simply enter the quantity next to its Component. Each Product item may have a number of different Components between one to each other. In other words, we have to handle the possibility of dynamic content of the Component items.

Since PHPMaker does not have the special built-in feature for that (even it has a good Master/Detail feature), then I was thinking to customize the generated script files for the very first time. But, I thought that was a bad idea, since it was something that I had been always avoiding it all the time. Then I was thinking to use Server Events of PHPMaker.

Well, after several minutes writing the experimental code, then I found out the solution using Server Events and when I tested it, it worked like a charm. So here is the main idea for the solution I made. First of all, user is adding a Product item from the Add Order page. Secondly, after the Product is saved into the database, then user will automatically be redirected to the Order Details page that belongs to the new Order which related to the Product. So, from this Edit Orders/Order Details page, then user can simply enter the quantity of the Component, and finishing the Order process afterwards. It's a quite simple process, isn't?

I obviously am sharing this solution to all of you in order to give you the idea, how easily and quickly you can define your own business process in the Server Events of PHPMaker without having to write hundred or even thousand lines of code.

Updated on February 10, 2013: This customization has been implemented in PHPMaker version 9.2.0, it matches to each other, and as a result, it works properly.

1. First of all, make sure you have installed the latest version of PHPMaker. For your information, I was using PHPMaker 9.1.0 when I was writing this article.
2. Download PHPMaker_Orders-OrderDetails.zip file, and then extract it to your computer. There are two files inside: orders.pmp is a PHPMaker project file that related to the case in this article, and orders.sql for generating the database structure.
3. Create a new database, for example, name it with orders, then select that new database, afterwards run the orders.sql file using your favorite MySQL database tool in order to generate the database structure:

   -- ----------------------------
   -- Table structure for `components`
   -- ----------------------------
   DROP TABLE IF EXISTS `components`;
   CREATE TABLE `components` (
     `Id` int(11) NOT NULL AUTO_INCREMENT,
     `Id_Products` int(11) NOT NULL,
     `Description` varchar(100) NOT NULL,
     `Code_Maker` varchar(50) DEFAULT NULL,
     `Price` decimal(5,2) NOT NULL,
     PRIMARY KEY (`Id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
   
   -- ----------------------------
   -- Records of components
   -- ----------------------------
   INSERT INTO `components` VALUES ('1', '1', 'Component 1 of Product 1', 'EXAMPLE1', '2.00');
   INSERT INTO `components` VALUES ('2', '1', 'Component 2 of Product 1', 'EXAMPLE1-2', '5.00');
   INSERT INTO `components` VALUES ('3', '2', 'Component 1 of Product 2', null, '4.00');
   INSERT INTO `components` VALUES ('4', '2', 'Component 2 of Product 2', null, '1.00');
   INSERT INTO `components` VALUES ('5', '2', 'Component 3 of Product 2', null, '3.00');
   
   -- ----------------------------
   -- Table structure for `orders`
   -- ----------------------------
   DROP TABLE IF EXISTS `orders`;
   CREATE TABLE `orders` (
     `Id` int(11) NOT NULL AUTO_INCREMENT,
     `Id_Products` int(11) NOT NULL,
     `User` int(3) NOT NULL,
     `Date` date NOT NULL,
     PRIMARY KEY (`Id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
   
   -- ----------------------------
   -- Records of orders
   -- ----------------------------
   INSERT INTO `orders` VALUES ('1', '1', '0', '2013-01-23');
   INSERT INTO `orders` VALUES ('2', '2', '0', '2013-01-23');
   
   -- ----------------------------
   -- Table structure for `order_details`
   -- ----------------------------
   DROP TABLE IF EXISTS `order_details`;
   CREATE TABLE `order_details` (
     `Id` int(11) NOT NULL AUTO_INCREMENT,
     `Id_Orders` int(11) NOT NULL,
     `Id_Component` int(11) NOT NULL,
     `Quantity` int(5) NOT NULL,
     PRIMARY KEY (`Id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
   
   -- ----------------------------
   -- Records of order_details
   -- ----------------------------
   INSERT INTO `order_details` VALUES ('1', '1', '1', '1');
   INSERT INTO `order_details` VALUES ('2', '1', '2', '2');
   INSERT INTO `order_details` VALUES ('3', '2', '3', '4');
   INSERT INTO `order_details` VALUES ('4', '2', '4', '5');
   INSERT INTO `order_details` VALUES ('5', '2', '5', '6');
   
   -- ----------------------------
   -- Table structure for `products`
   -- ----------------------------
   DROP TABLE IF EXISTS `products`;
   CREATE TABLE `products` (
     `Id` int(11) NOT NULL AUTO_INCREMENT,
     `Description` varchar(100) NOT NULL,
     `Price` decimal(6,2) NOT NULL,
     PRIMARY KEY (`Id`)
   ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
   
   -- ----------------------------
   -- Records of products
   -- ----------------------------
   INSERT INTO `products` VALUES ('1', 'Product 1', '10.00');
   INSERT INTO `products` VALUES ('2', 'Product 2', '12.99');
   

4. Double click the extracted orders.pmp file from the second step above, and now it should open PHPMaker to run it.
5. As you can see, there are some Master/Detail relationships have been created that you can see it from Table setup of PHPMaker as follows:
   - products (Master) ---> components (Detail)
   - orders (Master) ---> order_details (Detail)
   - products (Master) ---> orders (Detail)
6. The most important thing that you should to know is, you have to give a checked mark at Detail Edit from Table -> List setup of PHPMaker that belongs to orders table. This will tell PHPMaker to generate the Edit Order Details page (see the results in the last step below).
7. Now click on orders table, and then go to its Server Events -> Table-Specific -> Common -> Row_Inserted, and then see the following code that were added into Row_Inserted function:

       global $conn;
       // Get component based on ID_Products
       $rs = $conn->Execute("SELECT Id FROM components WHERE Id_Products = ".$rsnew["Id_Products"]."");
       if ($rs->RecordCount() > 0) {
          $rs->MoveFirst();
          $component_id = "";
          while (!$rs->EOF) {
            $component_id = $rs->fields("Id");
            // Insert records to the "order_details" table based on the Order ID and Component ID that related to Id_Products above
            ew_Execute("INSERT INTO order_details VALUE ('0','".$rsnew["Id"]."',".$component_id.",'0')");
            $rs->MoveNext();
          }
          $rs->Close();
          // Redirect user to the Orders/Order Details to edit the quantity of each component
          $this->Page_Terminate("ordersedit.php?showdetail=order_details&Id=".$rsnew["Id"]."");
       }
   

   That code above will execute INSERT sql into order_details after you added a new order to orders table based on the value of Id_Products, afterwards you will be redirected to the Edit Orders/Order Details page so you can simply define the quantity of each Component.

8. Generate your script files using PHPMaker as always.
9. In order to try it, now open your browser, just type this address: http://localhost/orders/ordersadd.php?showdetail=, select your desired Id Products, and then click on Add button, afterwards, you will be automatically redirected to this address: http://localhost/orders/ordersedit.php?showdetail=order_details&Id=3 (please note, 3 is the new Id_Products value that was just inserted in the Row_Inserted event above) that contains Edit Orders/Order Details page to define/edit the quantity of each Component that belongs to the ordered Id_Products.