Jump to content

doni49

Members
  • Posts

    515
  • Joined

  • Last visited

Everything posted by doni49

  1. 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);
  2. 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 ;
  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.
  4. Anybody have any advice on this one?
  5. 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.
  6. 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)
  7. 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>
  8. 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);
  9. http://stackoverflow.com/a/23532364/1042053
  10. 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?
  11. 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)
  12. 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.
  13. 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.
  14. 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!
  15. Mac, I have no idea what I changed that did it, but I seemed to have fixed it while trying to gather the info you requested. Very odd. Regarding the issue of trying to help with what info I was giving you, I can definitely understand what you're saying there -- on another forum that I frequent, I'm one of the guys that spends quite a bit of time helping others on the board and have dealt with quite a few issues where I felt like I was pulling teeth to get the info I needed to try and help them -- and I do greatly appreciate your effort. I was trying to avoid posting the entire code and asking you (or anyone else) to sort through it all to try to diagnose the issue. I really thought there must have been something with the code that posted -- just something obscure that I wasn't recognizing. EDIT: Regarding what I said about "what I changed", I don't REMEMBER changing anything other than adding a couple comments to help you understand what the code is doing. I really don't know what to make of that.
  16. Sorry I really thought my reply answered that. But based on your statement, I went back and focused on the code other than line 47 and noticed that there was code further down in the for loop that reset the $values array as a string. I've since corrected that but now I'm getting a different error. The following code (I've confirmed there is no for loop involved here) produces the what's below that. echo "<pre>"; print_r($k); echo "</pre>"; echo "Value0: " . gettype($k[0]); echo "<br />Value1: " . gettype($k[1]); echo "<br />Type (k): " . gettype($k); $values = join(",",$k); Array ( [0] => ( 2015-12-10,"Jack McCoy",10.00,0,4,1 ) [1] => ( 2015-12-10,"Jack McCoy",10.00,0,4,1 ) ) Value0: string Value1: string Type (k): array Warning: join(): Invalid arguments passed in /home/........./putRecords.inc.php on line 78
  17. It is eventually used to insert values into a MySQL db using the following code: $query = "INSERT INTO `" . $tableName. "` (" . join("," , $fields) . ") VALUES ( " . join("," , $values) ." )";
  18. I'm trying to create a multi-dimensional array but it is throwing an error that just doesn't make sense. Does anyone see anything that could be causing this? This code: echo "<br />cnt: " . $cnt . "; k: " . $k . "; v: " . $v . "\n<br />"; echo "Type (cnt): " . gettype($cnt) . "<br />"; echo "Type (k): " . gettype($k) . "<br />"; echo "Type (v): " . gettype($v) . "<br />"; $values[$cnt][$k]= $v; Produces this: cnt: 1; k: date; v: 2015-12-10 Type (cnt): integer Type (k): string Type (v): string Fatal error: Cannot use string offset as an array in /home/........../putRecords.inc.php on line 47
  19. I'm also on a shared hosting site with cPanel. My structure is : /home/username/ /public_html <-- this is the folder that apache serves my webpages from. /includes < -- I store files here (including my db credentials file) in this folder. Then my web pages INCLUDE these files.
  20. I'm working on an android app that will sync with the a MySQL server. The data will be stored locally in SQLite for offline use. I'm trying to figure out what engine would work best for this (isam etc). It's my understanding that "Transaction safe" engine is good for preventing data loss. Is that because of the way the data doesn't actually committed until the app sends the commit instruction? In the following scenario, what kind of data loss would I be open to? Does "transaction safe" prevent this kind of loss? My plan has been: If creating a new record while offline, do so but assign a field called "localID" (which would be an auto increment column) with a unique id number. This localID will not be stored on the server and will ONLY be used on that device. When creating a new record while online (or sending a new record that was previously created while offline), the data will be sent and as confirmation of receipt of the new data, the server will return an array (via json) of the id numbers of the newly created rows. I will be using Google Cloud Messaging so that the server data is updated, all subscribed devices will be told the id numbers for all the rows they need to process (along with info telling whether this is a NEW record, an UPDATE to an existing record or delete the record. When devices receive the GCM message, they'll will know what records to get and how to deal with them. Each row on the server will have a "version" id and every time one row is updated, the version number will be incremented. This way if the server sees an update to a record and the version number is lower than what's on the server, the sending device will be instructed to inform the user of this. The one thing I haven't figured out in that is how to deal with an edit from two different devices of the same row. There shouldn't be more than a few devices that could possibly change any record. So I'm thinking that one way might be that when the server sees the row being updated from two diff devices at the same time, it could send back a GCM message which would prompt the user to decide which is valid. But I'm not sure if I like that. This is all given as background in hopes of getting some informed advice but I'm open to suggestions for dealing with the duplicate data issue.
  21. No errors and same path/host. I thought it was somehow related to the php script (in the AJAX calls) being a separate call from the original file. I'll investigate further when I get home. Thanks.
  22. That's what I thought. My index.php file first checks to see if specific SESSION vars are set. If they aren't, then the first thing the page does is show the login page. The login page makes an AJAX call to a php file which if the login info matches the DB, sets the aforementioned SESSION vars and then returns json code telling AJAX to show the next page. The "next page" loads fine. But if I press F5, I'm presented with the login page again.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.