Creating relationship between two arrays

Question

I am getting data from an endpoint which returns a list of JSON data like this

***API DATA
{"addresses": [
    {
    "address": "address1",
    "total_sent": 000000000000000000,
    "total_received": 00000000000000,
    "final_balance": 000000000000000,
    "n_tx": 0000
}],
"txs": [{
    "hash": "hhhdhhdhgggdhhdjjjjdkkdkkdjjdjjjdjjj",
    "confirmations": 0000,
    "change": 000000000000000,
    "time_utc": "2018-04-30T02:59:43Z"

},{
"hash": "hhhdhhdhgggdhhdjjjjdkkdkkdjjdjjjdjjj",
    "confirmations": 0000,
    "change": 000000000000000,
    "time_utc": "2018-04-30T02:59:43Z"
}],
 "addresses": [
    {
    "address": "address2",
    "total_sent": 000000000000000000,
    "total_received": 00000000000000,
    "final_balance": 000000000000000,
    "n_tx": 0000
}],
"txs": [{
    "hash": "hhhdhhdhgggdhhdjjjjdkkdkkdjjdjjjdjjj",
    "confirmations": 0000,
    "change": 000000000000000,
    "time_utc": "2018-04-30T02:59:43Z"

},{
    "hash": "hhhdhhdhgggdhhdjjjjdkkdkkdjjdjjjdjjj",
    "confirmations": 0000,
    "change": 000000000000000,
    "time_utc": "2018-04-30T02:59:43Z"
  }]
}

So this JSON output prints out addresses and different transactions under it. What I want to do is to save this data in a database and create a relationship between addresses and its transactions(txs). I have created two tables one for address and transactions.

***ADRESS TB****
 CREATE TABLE `address` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `address` varchar(100) DEFAULT NULL,
    `total_sent` bigint(50) NOT NULL,
    `total_received` bigint(50) NOT NULL,
    `final_balance` bigint(50) NOT NULL,
    `n_tx` int(10) NOT NULL,
     PRIMARY KEY (`id`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


 *****Transactions DB*******
 CREATE TABLE `transactions` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `hash_key` varchar(100) DEFAULT NULL,
     `confirmations` bigint(20) NOT NULL,
     `change_hash` bigint(50) NOT NULL,
     `time_utc` datetime DEFAULT NULL,
     `address_id` bigint(11) unsigned NOT NULL,
      PRIMARY KEY (`id`),
      FOREIGN KEY (`address_id`) REFERENCES `address` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

this is what i have tried, i dont to what to do from here am kinda stuck

$decoded =json_decode( $getresponse, true );//decoding json data
 if (is_array($decoded) || is_object($decoded)){
      foreach ($decoded['addresses'] as $addresseschunk){ 
             foreach ($decoded['addresses'] as $addresseschunk){
                         $address        =$addresseschunk['address'];
                         $totalsent      =$addresseschunk['total_sent'];
                         $totalreceived  =$addresseschunk['total_received'];
                         $final_balance  =$addresseschunk['final_balance'];
                         $n_tx           =$addre


                         $hash            =$txschunk['hash'];
                         $confirmations   =$txschunk['confirmations'];
                         $change          =$txschunk['change'];
                         $timeutc         =$txschunk['time_utc'];
                         #$n              =$txschunk['n'];
                         $newtime         =date('Y-m-d h:i:s', strtotime($timeutc));
               $conn->setAttribute(PDO::ATTR_ERRMODE,  PDO::ERRMODE_EXCEPTION);
               $conn->beginTransaction();
               $stmt = $conn->prepare("SELECT * FROM address WHERE address=?");
               $stmt->execute([$address]);
               $curencyAddress = $stmt->fetch();
               if ($curencyAddress) {
                echo "user exist already";
                } else {
               $sql = "INSERT INTO address (address, total_sent,  total_received,final_balance,n_tx,currency_id)
    VALUES  ('$address','$totalsent','$totalreceived','$final_balance','$n_tx','$curencyID')";
                 $conn->exec($sql);
                 $new_id = $conn->lastInsertId();
                 $sql2 = "INSERT INTO hashkey (hash_key, confirmations, change_hash,time_utc,address_id)
     VALUES ('$hash','$confirmations','$change','$newtime','$new_id')";
            $conn->exec($sql2);

           }                                    
          }
       }
    }

score 0 · Answer 1 · answered Mar 20 '19 at 13:49

I didn't read very far, because my "internal" php interpreter gave an error message here:
$hash=$txschunk['hash'];

That should give an "undefined" because $txschunk is not defined.

Backing up, this looks sketchy, and perhaps a typo (or a copy/paste boo-boo):

foreach ($decoded['addresses'] as $addresseschunk){ 
             foreach ($decoded['addresses'] as $addresseschunk){

You might want to turn on all php errors and warnings in the script, or at least check the php error log. Fixing that problem should get you to a different place, which is not to say the program will work, only that you should make progress.

Creating relationship between two arrays

1 Answers1