Thursday, 18 May 2017

I need help in writing the MySQL INSERT query for a family tree

I am new in back-end programming and need help in writing insert queries for this below my SQL schema which i made. Please any help will be appreciated thanks in advance: I need help in the POST req, to store organisations with relations (parent to child relation). Organization name is unique. All relations and organisations are inserted with one request

dbSchema

 CREATE TABLE `orgs` (
 `id` int(10) UNSIGNED NOT NULL,
 `org_name` varchar(250) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf16;


 CREATE TABLE `orgs_relation` (
 `org_id` int(10) UNSIGNED NOT NULL,
 `parent_org_id` int(10) UNSIGNED NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf16;

ALTER TABLE `orgs`
 MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

inserting data should be in JSON format.. example data is this:

"org_name": "Paradise Island", 
"daughters": [{ 
"org_name": "Banana tree", 
"daughters": [{ 
"org_name": "Yellow Banana" 
}, { 
"org_name": "Brown Banana" 
}, { 
"org_name": "Black Banana" 
}] }, 

I have succeeded in going this far with the POST request but need help in proceeding with the query and how to perform the recursion: Here is what I have done so far;

app.post("/api/create", function (req, res) {
    var body = '';
    req.on('data', function (data) {
        body += data;
        // If someone is trying to nuke RAM, nuke the request
        // 1e6 === 1 * Math.pow(10, 6) === 1 * 1000000 ~~~ 1MB
        if (body.length > 1e6) {
            req.connection.destroy();
        }
    });
    req.on('end', function () {
        process_request(body);
    });
    function process_request(body) {
        // Continue with parsing json string in body and inserting organisation
        // json string is stored in the body variable
        var org = JSON.parse(body);
        console.log(org);
        res.end(body);
    }
      var q =
         `  // "INSERT INTO orgs (id, org_name) VALUES 


              `;
     connection.query(q, body, function(err, result) {
        if(err){
          console.log(err);
        }
        console.log(result);
        res.redirect("/get/:name");
    });
});



via giresse

No comments:

Post a Comment