doni49

Just to confirm that a foreign key field CAN have a null value, I temporarily removed the three foreign keys from the transactions able and inserted the following. It accepted it without complaint. Each transaction has a field called "parentID". There is a foriegn key (fkTxnParentID) in which this field references the id field for the same table. A "top level" transaction has a null parentID. A child has a value in the parentID field that points to the top level transaction. INSERT INTO `transactions` (`id`, `ref`, `date`, `payee`, `amount`, `memo`, `catID`, `acctXID`, `acctID`, `version`, `userID`, `type`, `parentID`, `defCatID`) VALUES (4893, NULL, '2000-01-04 07:19:00', 'John Trivett', NULL, 'NULL', NULL, NULL, 1, 0, 4, NULL, NULL, NULL), (4894, NULL, NULL, NULL, '8.76', NULL, 0, 2, NULL, 0, 4, NULL, 4893, 0), (4895, NULL, NULL, NULL, '4.84', NULL, 207, 0, NULL, 0, 4, NULL, 4893, 0), (4896, NULL, NULL, NULL, '2.28', NULL, 211, 0, NULL, 0, 4, NULL, 4893, 0);

I'm trying to do pretty much the same thing as shown in this example: (http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#Listing_4a). But when I try to insert rows in which the fk field value is NULL, I get the error message about "can not add or update a child row". This is for what amounts to an electronic checkbook. The transactions will have three possible settings that will be displayed as the category in the UI: a transfer to another account, a value from the defaultCategories table or a user defined category (categories table). So acctXID, defCatID or catID will have a value. There is another field ("type") that is an enum that will tell which of these should be expected. This is the table in question: CREATE TABLE IF NOT EXISTS `transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ref` varchar(10) DEFAULT NULL, `date` timestamp NULL DEFAULT NULL, `payee` varchar(50) DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL, `memo` varchar(50) DEFAULT NULL, `catID` bigint(20) unsigned DEFAULT NULL, `acctXID` smallint(6) unsigned DEFAULT NULL, `acctID` smallint(6) unsigned DEFAULT NULL, `version` int(11) DEFAULT '0', `userID` bigint(20) DEFAULT NULL, `type` enum('normalD','normalU,'transfer') DEFAULT NULL, `parentID` bigint(20) unsigned DEFAULT NULL, `defCatID` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkTxnParentID` (`parentID`), KEY `fkAcctID` (`acctID`), KEY `fkCatID` (`catID`), KEY `fkAcctXID` (`acctXID`), KEY `fkDefCatID` (`defCatID`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4875 ; ALTER TABLE `transactions` ADD CONSTRAINT `fkAcctID` FOREIGN KEY (`acctID`) REFERENCES `accounts` (`id`), ADD CONSTRAINT `fkAcctXID` FOREIGN KEY (`acctXID`) REFERENCES `accounts` (`id`), ADD CONSTRAINT `fkCatID` FOREIGN KEY (`catID`) REFERENCES `categories` (`id`), ADD CONSTRAINT `fkDefCatID` FOREIGN KEY (`defCatID`) REFERENCES `defaultCategories` (`id`), ADD CONSTRAINT `fkTxnParentID` FOREIGN KEY (`parentID`) REFERENCES `transactions` (`id`); This is the definition of the categories, defaultCategories and accounts tables. #categories Table CREATE TABLE IF NOT EXISTS `categories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `userID` smallint(5) unsigned NOT NULL, `type` enum('income','expense') NOT NULL, `parentID` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkCatParentID` (`parentID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ; #defaultCategories Table CREATE TABLE IF NOT EXISTS `defaultCategories` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `parentID` varchar(48) DEFAULT NULL, `type` enum('income','expense') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; #accounts Table Definition CREATE TABLE IF NOT EXISTS `accounts` ( `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `type` enum('cash','credit','checking') NOT NULL DEFAULT 'checking', `userID` smallint(6) unsigned NOT NULL, `nextCheck` smallint(5) unsigned DEFAULT NULL, `beginBal` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Ok thanks that does make sense. Because I'm getting these messages when trying to CREATE the fk's, I was focused on the possibility that I had an error in the code I was using to create them.

Anybody have any advice on this one?

Thanks Mac. I would never have guessed that. I think I can handle the temp var suggestion. The data set you're seeing is only test data. After I get this working, this php script will be receiving data in json format and there will almost always be a lot more transactions. That's why I'm doing it in a for loop.

This is what my browser displays (per the print_r instruction). Array ( [ref] => null [date] => 2015-12-17 [payee] => Jack McCoy [memo] => null [acctID] => 1 [amount] => 10.25 [catID] => 4 [acctXID] => NULL [type] => normal ) And this is what was added to the db table. (4403, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, 0, NULL)

Mac, It's worse that I thought. After seeing your post, I went back and looked again. I found a spot in my php file where $txn['date'] did actually have an invalid value -- outside of the for loop where the execute is located. So I removed that line and have found that it's not using the variable values as they're defined at the time the execute statement runs. I thoguht that was one of the main points of prepared statements (in addition to better control of what gets entered). Now that I removed the line that set the variable outside the for loop, this is what the entire php file looks like. The entire row of data that gets added to the table has NULL values. But the directly above the execute line, I did a print_r showing that the values aren't NULL. <doctype HTML> <html> <head> </head> <body> <?php //header ("Content-type: application/json; charset=utf-8"); require("/home/user/includes/db_Connect.inc.php"); $tableName = 'transactions'; $putData = json_encode(array( array("ref"=>"null","date"=>"2015-12-17","payee"=>"Jack McCoy","memo"=>"null","acctID"=>'1',"amount"=>"10.25","catID"=>"4","acctXID"=>"NULL","type"=>"normal") )); $arr = json_decode($putData); $cnt = -1; $uID = 4; try{ $isTrue = 'true'; $isFalse = 'false'; $q = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`acctXID`,`ref`,`type`,`hasChildren`,`amount`,`catID`,`userID`) VALUES (?,?,?,?,?,?,?,?,?,?,?)"; if($normalStmt = $conn->prepare($q)){ $normalStmt->bind_param("sssiissidii",$txn['payee'],$txn['date'],$txn['memo'],$txn['acctID'],$txn['acctXID'],$txn['ref'],$txn['type'],$isFalse,$txn['amount'] ,$txn['catID'], $uID); }else { printf("(Normal) Error message: %s\n<br>", $conn->error); } foreach ($arr as $txn){ $cnt++; $txn = (array)$txn; unset($query); echo "<pre>"; print_r($txn); echo "</pre><br />"; $query = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`ref`,`amount`,`catID`,`userID`) VALUES ('" . $txn['payee'] . "'," . $txn['date'] . ",'" . $txn['memo'] . "'," . $txn['acctID'] . "," . $txn['ref'] . "," . $txn['amount'] . "," . $txn['catID'] . "," . $uID . ");"; if ($normalStmt->execute()){//$conn->query($query) === TRUE) { $rowID[] = $conn->insert_id; } else { throw new Exception($conn->error); } } }catch(Exception $e) { echo '\nCaught exception: ', $e->getMessage(), "\n"; } } ?> </body> </html>

But I'm almost 100% sure the answer is no.

I'll look when I get home. Thanks Mac.

I'm trying to setup a prepared statement to insert data in my table. When I insert the data, it appears to work fine unti........... I look at the data that was actually inserted -- the date is listed in the DB with all zeros. Here's what the statement looks like: $isTrue = 'true'; $q = "INSERT INTO `" . $tableName . "` (`payee`,`date`,`memo`,`acctID`,`ref`,`hasChildren`,`userID`) VALUES (?,?,?,?,?,?,?);"; $stmt = $conn->prepare($q); $stmt->bind_param("sssisii",$txn['payee'] , $txn['date'] , $txn['memo'] ,$txn['acctID'] ,$txn['ref'] ,$isTrue, $uID); This is the code I'm using to execute the insert: echo "<pre>"; print_r($txn); echo "</pre>"; if($stmt->execute()){ $txnegories[]=$lastID = $conn->insert_id; } else { throw new Exception($conn->error); } This is what the print_r displays in the browser. Array ( [ref] => null [date] => 2015-12-10 [payee] => John SMith [memo] => null [acctID] => 1 ) This is what the table definition looks like as well as the contents of the table: CREATE TABLE IF NOT EXISTS `transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ref` varchar(10) DEFAULT NULL, `date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `payee` varchar(50) DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL, `memo` varchar(50) DEFAULT NULL, `catID` bigint(20) unsigned DEFAULT NULL, `acctXID` smallint(6) unsigned DEFAULT NULL, `acctID` smallint(6) unsigned DEFAULT NULL, `version` int(11) DEFAULT NULL, `userID` bigint(20) DEFAULT NULL, `type` enum('normal','transfer') DEFAULT 'normal', `hasChildren` tinyint(1) NOT NULL DEFAULT '0', `parentID` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkTxnParentID` (`parentID`), KEY `fkAcctID` (`acctID`), KEY `fkCatID` (`catID`), KEY `fkAcctXID` (`acctXID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4315 ; INSERT INTO `transactions` (`id`, `ref`, `date`, `payee`, `amount`, `memo`, `catID`, `acctXID`, `acctID`, `version`, `userID`, `type`, `hasChildren`, `parentID`) VALUES (4283, NULL, '0000-00-00 00:00:00', 'Jack McCoy', '10.25', 'null', 4, NULL, 1, NULL, 4, 'normal', 0, NULL);

http://stackoverflow.com/a/23532364/1042053

I'm reading through some older threads on the forum -- I find that reading other people's issues and the answers they receive is a great education in itself -- and saw some code in a reply posted by Barand that I don't understand. Here's a link to the post in question http://forums.phpfreaks.com/topic/299309-cumulative-sum-in-mysql-view-calculating-running-profitloss/?do=findComment&comment=1525829 The code in question: JOIN (SELECT @cum:=0) init @cum:=@cum+profit as cum_profit I get that the @ symbol indicates a global variable. But what is the := for? And is this "init" being used similar to a for loop?

I'm trying to setup foreign keys for my DB. It's accepting SOME of them but not all. I'm hoping someone here might be able to assist. This is for what is basically an electronic checkbook. This is the table to which I'm trying to add my fk's. All of this works (including the portion that adds fk's below -- they're the ones that it did accept) in fact I exported the table definition and that's what I'm showing you. CREATE TABLE IF NOT EXISTS `transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ref` varchar(10) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `payee` varchar(50) NOT NULL, `amount` decimal(10,2) NOT NULL, `memo` varchar(50) DEFAULT NULL, `catID` bigint(20) unsigned DEFAULT NULL, `acctXID` smallint(6) unsigned DEFAULT NULL, `acctID` smallint(6) unsigned DEFAULT NULL, `version` int(11) NOT NULL, `userID` bigint(20) NOT NULL, `type` enum('normal','transfer') NOT NULL DEFAULT 'normal', `parentID` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkTxnParentID` (`parentID`), KEY `fkAcctID` (`acctID`), KEY `fkCatID` (`catID`), KEY `fkAcctXID` (`acctXID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=214 ; ALTER TABLE `transactions` ADD CONSTRAINT `fkAcctID` FOREIGN KEY (`acctID`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fkTxnParentID` FOREIGN KEY (`parentID`) REFERENCES `transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; Then I have two other tables (accounts and categories). Each transaction has a field called "acctID" that references the id field in the accounts table and a field called "catID" that references the id field in the categories table. Each transaction also has an enum field called "type": If transaction.type = 'transfer' then acctXID refers to the id number of the account to which the money is being transferred. So I'm trying to put a second fk that also points to the id field in the accounts table. If transaction.type = 'normal' then catID should reference the id field in the categories table. This is what the accounts & categories tables look like. #accounts table CREATE TABLE `accounts` ( `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `type` enum('cash','credit','checking') NOT NULL DEFAULT 'checking', `userID` smallint(6) unsigned NOT NULL, `nextCheck` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; #categories table CREATE TABLE `categories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(48) NOT NULL, `userID` smallint(5) unsigned NOT NULL, `type` enum('income','expense') NOT NULL, `parentID` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fkCatParentID` (`parentID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=102 ; ALTER TABLE `categories` ADD CONSTRAINT `fkCatParentID` FOREIGN KEY (`parentID`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; Now this is my attempt to add the new fk's to the transactions table. It just throws the following error message: #1452 - Cannot add or update a child row: a foreign key constraint fails (`#sql-588b_17858f1`, CONSTRAINT `fkCatID` FOREIGN KEY (`catID`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

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.

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.