Creating relationship tables from JSON data....

[doni49]

Hi all.  I've been working on an android app that needs to sync its data to my MySQL server.  The data is being passed from the android client using JSON.  I've written a JSON parser that does it pretty well EXCEPT......
 
Three of the tables contain rows that point to another row in the same table (parent and child).  I need to design the DB layout & the parser so that it can create the parent & use the id number (auto increment) generated in step 1 to create the children.
 
For example, this is what the transactions table looks like:
 
CREATE TABLE transactions (
id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
date TIMESTAMP,
payee VARCHAR(30),
amount DECIMAL(10,2),

category VARCHAR(20),
parentID BIGINT(20),     # <--if parentID = 0 then this is not a child
)
INSERT INTO `transactions` (`date`,`payee`,`parentID`) VALUES (NOW(),"Joe Smith",0); 
SET @lastID = last_insert_id();
INSERT INTO `transactions` (`amount`,`category`,`parentID`) VALUES (10.25,"Groceries",@lastID),(5.75,"Dining Out",@lastID);
 
SELECT p.payee, sum(c.amount), FROM `transactions` AS p Join `transactions` AS c WHERE p.id = @lastID;
 
The above should return "Joe Smith", 16.00.
 
This is all pseudo-code typed from memory just to illustrate what I'm trying to do (untested) because I can't get the website to allow me to actually paste the real code in.
 
This is a sample of the JSON code.
$jsondata = [
{"payee":"John Doe","amount":"5.25"},       <--This is what an entry looks like when it is NOT a child and has no children

​{"payee":"Joe Smith","children":{"amount":"10.25","category":"Groceries"},{"amount":"5.75","category":"Dining Out"}}  <--This is what the JSON code for the sample MySQL data sample shown above might look like.
];

 

I'm designing the android data store (using SQLite) also which means I can design it in any manner that I need.

My parser consists of two nested foreach loops.  The outer loop separates each JSON object and the inner loop actually process it. The part that has me stumped is how to tell the parser to assign the id field of the parent row to the parentID field in the child rows.

 

I'm not here asking for actual code -- just some general thoughts as to how I can lay this out as it's got me really stumped.

 

TIA!

[Barand]

This should show you the logic you need to employee to process your data. (Note this is pseudocode, not runnable code)

$jsondata = '[{"payee":"John Doe","amount":5.25},{"payee":"Joe Smith","children":[{"amount":10.25,"category":"Groceries"},{"amount":5.75,"category":"Dining out"}]}]';

$data = json_decode($jsondata,1);

foreach ($data as $trans) {
    if (isset($trans['children'])) {
        insert into transaction (payee) VALUES '{$trans[payee]}'
        $parentid = last_insert_id();
        foreach ($trans['children'] as $child) {
            insert into transaction (parent,amount,category) VALUES ($parentid, $child[amount], $child[category])
        }
    }
    else {
        insert into transactions (payee,amount) VALUES ($trans[payee], trans[amount])
    }
}

[doni49]

Thanks Barand!  I think I was overcomplicating it a bit.  You simplified it greatly.

 

As far as psuedo code goes, that's completely fine with me.  I was really just looking for conceptual advice as to how I should tackle this.

[doni49]

Ok.  I can officially confirm that's exactly what I needed.  Now I need to travel back in time to before I started trying to over complicate it and use this instead.