How to insert master and multiple details via REST API call

This was a part of the recent DevClub webinar. In this article we will expalin how to implement a custom REST API that would allow to insert multiple records into several tables at once. We will use orders as master table that has to details tables, order details and users.

The following code goes to AfterAppInit event of your project. It accepts data in JSON format and also performs a basic authentication against a hardcoded API key. A more robust approach would be to store API in the database and you can have a separate API key for diffeent API users.

Note: this code assumes that master table's primary key column is auto-increment.

And here is a content of a PHP file that you can use for API testing purposes. You can create a new file under Style Editor -> Custom Files named apitest.php. After you built your project you can run it in the web browser as http://localhost:8086/apitest.php. If the URL of your application is different you also need to adjust it at the top of this file.

<?php
require_once("include/dbcommon.php");
$url = "http://localhost:8086/menu.php";
$res["mastertable"]="orders";
$res["data"] = array();

// master table data ( Orders)
$data = array();
$data["CustomerID"]="10";
$data["EmployeeID"] = "12";
$data["OrderDate"] = date("Y-m-d");
$res["data"] = $data;

// Order details table
$res["details"] = array();
$details = array();
$details["detailtable"] = "order details";
$details["masterkey"] = "OrderID";
$details["data"] = array();
$data = array();
$data["ProductID"] = 1;
$data["UnitPrice"] = 10;
$data["Quantity"] = 2;
$details["data"] = $data;
$res["details"][] = $details;
$data["ProductID"] = 2;
$data["UnitPrice"] = 24;
$data["Quantity"] = 1;
$details["data"] = $data;
$res["details"][] = $details;
$data["ProductID"] = 3;
$data["UnitPrice"] = 100;
$data["Quantity"] = 3;
$details["data"] = $data;
$res["details"][] = $details;
$data["ProductID"] = 4;
$data["UnitPrice"] = 3;
$data["Quantity"] = 15;
$details["data"] = $data;
$res["details"][] = $details;
$data["ProductID"] = 5;
$data["UnitPrice"] = 35;
$data["Quantity"] = 4;
$details["data"] = $data;
$res["details"][] = $details;

// second details table ( users )
$details["detailtable"] = "users";
$details["masterkey"] = "OrderID";
$details["data"] = array();
$data = array();
$data["username"] = "user1";
$data["password"] = "pass1";
$data["email"] = "email1";
$details["data"] = $data;
$res["details"][] = $details;
$data["username"] = "user2";
$data["password"] = "pass2";
$data["email"] = "email2";
$details["data"] = $data;
$res["details"][] = $details;
$str = json_encode($res);
$parameters["apidata"] = $str;

// authorization
$headers["auth"] = "1234567890";

// make a POST
$res = runner_post_request($url, $parameters, $headers);
if($res["error"])
echo $res["error"];
else
echo $res["content"];
exit();
?>

Once you run it, it will create a new order with five details and two associated users.

And, just in case here is a SQL script that creates required tabes in the database.

Enjoy!