title: Records are not being written or being deleted in a parent table and I don't know why with MariaDB/MySQL and PHP. Ideas?
body: <p>Here is the structure of the database tables in quesiton.</p>
  <p>Officials:</p>
  <pre><code>CREATE TABLE IF NOT EXISTS `officials` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `last` varchar(250) NOT NULL DEFAULT '',
    `first` varchar(250) NOT NULL DEFAULT '',
    `middle` varchar(250) NOT NULL DEFAULT '',
    `address` varchar(250) NOT NULL DEFAULT '',
    `city` varchar(250) NOT NULL DEFAULT '',
    `state` varchar(250) NOT NULL DEFAULT '',
    `zip` varchar(250) NOT NULL DEFAULT '',
    `homeph` varchar(250) NOT NULL DEFAULT '',
    `workph` varchar(250) NOT NULL DEFAULT '',
    `cellph` varchar(250) NOT NULL DEFAULT '',
    `main_phone` varchar(10) DEFAULT NULL,
    `email` varchar(250) NOT NULL DEFAULT '',
    `notes` text NOT NULL,
    `blockreg` varchar(10) NOT NULL,
    `inactive` varchar(10) NOT NULL,
    `gender` varchar(100) NOT NULL,
    `minority` varchar(100) NOT NULL,
    `affiliate` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Is affiliate official? 0 = no. 1 = yes.',
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=14506 DEFAULT CHARSET=latin1;
  
  </code></pre>
  <p>Transactions (edited from original post to show foreign key constraints):</p>
  <pre><code>CREATE TABLE IF NOT EXISTS `transactions` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `offid` int(11) NOT NULL,
    `txn_id` varchar(64) NOT NULL,
    `txn_time` varchar(24) NOT NULL,
    `approval_code` varchar(24) DEFAULT NULL,
    `amount` varchar(8) NOT NULL,
    `result_message` varchar(100) NOT NULL,
    `ch_phone` varchar(10) NOT NULL COMMENT,
    `ch_card_type` varchar(4) NOT NULL,
    `ch_address` varchar(50) NOT NULL,
    `ch_state` varchar(15) NOT NULL,
    `ch_last_name` varchar(50) NOT NULL,
    `ch_email` varchar(100) NOT NULL,
    `ch_zip` varchar(10) NOT NULL,
    `ch_exp_date` varchar(4) NOT NULL,
    `ch_city` varchar(50) NOT NULL,
    `ch_first_name` varchar(30) NOT NULL,
    `ch_transaction_type` varchar(15) NOT NULL,
    `ch_card_number` varchar(18) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `txn_id` (`txn_id`) USING BTREE,
    KEY `offid` (`offid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4;
  
  ALTER TABLE `transactions`
    ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`offid`) REFERENCES `officials` (`id`);
  
  </code></pre>
  <p>Transaction details (edited from original post to show foreign key constraints):</p>
  <pre><code>CREATE TABLE IF NOT EXISTS `transaction_details` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `xact_id` varchar(64) NOT NULL,
    `detail_amount` float(5,2) NOT NULL,
    `sport` varchar(10) NOT NULL,
    `regyr` varchar(250) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `xact_id` (`xact_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4;
  ALTER TABLE `transaction_details`
    ADD CONSTRAINT `transaction_details_ibfk_1` FOREIGN KEY (`xact_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  </code></pre>
  <p>Code that executes upon receiving the data from the credit card company where rows are inserted into the table:</p>
  <pre><code>// this creates the main transaction
  
      $sql = &quot;INSERT INTO transactions (offid, txn_id, txn_time, approval_code, amount, result_message, ch_phone,
                            ch_card_type, ch_address, ch_state, ch_last_name, ch_email, ch_zip, ch_exp_date, ch_city, ch_first_name,
                            ch_transaction_type, ch_card_number) VALUES ('$offid', '$txn_id', '$txn_time', '$approval_code', '$amount', '$result_message',
                                                                         '$ch_phone', '$ch_card_type', '$ch_address', '$ch_state', '$ch_last_name', '$ch_email',
                                                                         '$ch_zip', '$ch_exp_date', '$ch_city', '$ch_first_name', '$ch_transaction_type', '$ch_card_number')&quot;;
      $result = mysqli_query($conn, $sql);
  
  // this creates the detail transactions
  if ($ssl_result == 0) {
      $detail_sports = array('fb', 'vb', 'sb', 'bb', 'wr', 'sw', 'so', 'ba', 'tr');
      foreach ($detail_sports as $sport) {
          $var = $sport . 'total';
          // cookie is set for fbtotal, vbtotal, etc. with an amount for that line item
          if (isset($_COOKIE[$var])) {
              $detail_amount = $_COOKIE[$var];
              $sql           = &quot;INSERT INTO transaction_details (xact_id, detail_amount, sport, regyr) VALUES ('$txn_id', '$detail_amount', '$sport', '$regyear')&quot;;
              $result = mysql_query($sql);
          }
      }
  }
  
  </code></pre>
  <p>The transaction table has id numbers 1, 2, 3, 5, 6, 8, 9, 11, 12, 13, 15, 16, 17, etc. These should be consecutively numbered, but they aren't. It's almost as if they were written to the transaction (parent) table and then deleted. But the code doesn't delete a record.</p>
  <p>The transaction detail table has a many-to-one relationship with transaction and all of its records contain consecutive ids.</p>
  <p>Any idea what's going on?</p>