Re: [PHP] Thinking out loud - a continuation...
[snip] function getTiersJson( $company ) { $tiers = getTiers( $company ); $json = JSON_encode( $tiers ); } $tiersJson = getTiersJson( 1 ); ? This will output JSON with the following structure: [/snip] OK, now I know I am being dense - but don't I have to add return $json; to getTiersJson() ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Mon, Apr 2, 2012 at 10:36 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: [snip] function getTiersJson( $company ) { $tiers = getTiers( $company ); $json = JSON_encode( $tiers ); } $tiersJson = getTiersJson( 1 ); ? This will output JSON with the following structure: [/snip] OK, now I know I am being dense - but don't I have to add return $json; to getTiersJson() ? Of course ;) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-04-02 04:36 PM, Jay Blanchard wrote: [snip] function getTiersJson( $company ) { $tiers = getTiers( $company ); $json = JSON_encode( $tiers ); } $tiersJson = getTiersJson( 1 ); ? This will output JSON with the following structure: [/snip] OK, now I know I am being dense - but don't I have to add return $json; to getTiersJson() ? yeah, *lol* in my testing I had a print_r() in the getTiersJson() so didn't notice I wasn't returning since I didn't do anything with the captured value (null without a proper return). Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] On 3/30/2012 1:14 PM, Robert Cummings wrote: On 12-03-27 11:11 AM, Jay Blanchard wrote: [snip]On 3/27/2012 12:21 AM, Robert Cummings wrote: [-- SNIP --] Essentially, entries at the root and entries for the children are just auto indexed array items but the actual entries in those arrays retain the associative index structure for retrieval of the specific information. let me know and I can probably whip you up something. Robert that looks correct. Here is an example of the JSON that the guy provided for me - var json = { id: node02, name: 0.2, data: {}, children: [{ id: node13, name: 1.3, data: {}, children: [{ id: node24, name: 2.4, data: {}, children: [{ id: node35, name: 3.5, data: {}, children: [{ id: node46, name: 4.6, data: {}, children: [] }] }, { id: node37, name: 3.7, data: {}, children: [{ id: node48, name: 4.8, data: {}, children: [] }, { id: node49, name: 4.9, data: {}, children: [] }, { id: node410, name: 4.10, data: {}, children: [] }, { id: node411, name: 4.11, data: {}, children: [] }] }, Of course he properly closes up the JSON. I inserted id's (just an auto-incrementing number) and the data portion where needed. The name: is the part that has been the result of what you did before. Here's the code... I did a bit of shuffling and actually tested against a test db table: ?mysql :) DROP TABLE IF EXISTS tiers; CREATE TABLE tiers ( company INT NOT NULL, tier1 VARCHAR( 32 ) NOT NULLDEFAULT '', tier2 VARCHAR( 32 ) NOT NULLDEFAULT '', tier3 VARCHAR( 32 ) NOT NULLDEFAULT '', tier4 VARCHAR( 32 ) NOT NULLDEFAULT '', tier5 VARCHAR( 32 ) NOT NULLDEFAULT '', tier6 VARCHAR( 32 ) NOT NULLDEFAULT '', tier7 VARCHAR( 32 ) NOT NULLDEFAULT '', tier8 VARCHAR( 32 ) NOT NULLDEFAULT '', tier9 VARCHAR( 32 ) NOT NULLDEFAULT '', tier10 VARCHAR( 32 ) NOT NULLDEFAULT '', tier11 VARCHAR( 32 ) NOT NULLDEFAULT '', tier12 VARCHAR( 32 ) NOT NULLDEFAULT '', tier13 VARCHAR( 32 ) NOT NULLDEFAULT '', tier14 VARCHAR( 32 ) NOT NULLDEFAULT '' ); INSERT INTO tiers (company, tier1, tier2, tier3) VALUES (1, 'exec-001','sub-exec-011','sub-sub-exec-111'), (1, 'exec-001','sub-exec-011','sub-sub-exec-112'), (1, 'exec-001','sub-exec-012','sub-sub-exec-121'), (1, 'exec-001','sub-exec-012','sub-sub-exec-122'), (1, 'exec-002','sub-exec-021','sub-sub-exec-211'), (1, 'exec-002','sub-exec-021','sub-sub-exec-212'), (1, 'exec-002','sub-exec-022','sub-sub-exec-221'), (1, 'exec-002','sub-exec-022','sub-sub-exec-222'); ? And here's the code: ?php function getTiers( $company ) { // // Establish the root. // $sDb = ijinn_getServiceRef( 'dbManager' ); $db = $sDb-getConnectionRef(); $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = trim( $row['tier'.$i] ); if( $name === '' ) { break; } if( !isset( $focus[$name] ) ) { $focus[$name] = array ( 'name' = $name, 'children' = array(), ); } $focus = $focus[$name]['children']; } } } $wrapper = array ( 'children' = $root ); postProcessTiers( $wrapper ); return $root; } function postProcessTiers( $root ) { $root['children'] = array_values(
Re: [PHP] Thinking out loud - a continuation...
On 12-03-27 11:11 AM, Jay Blanchard wrote: [snip]On 3/27/2012 12:21 AM, Robert Cummings wrote: [-- SNIP --] Essentially, entries at the root and entries for the children are just auto indexed array items but the actual entries in those arrays retain the associative index structure for retrieval of the specific information. let me know and I can probably whip you up something. Robert that looks correct. Here is an example of the JSON that the guy provided for me - var json = { id: node02, name: 0.2, data: {}, children: [{ id: node13, name: 1.3, data: {}, children: [{ id: node24, name: 2.4, data: {}, children: [{ id: node35, name: 3.5, data: {}, children: [{ id: node46, name: 4.6, data: {}, children: [] }] }, { id: node37, name: 3.7, data: {}, children: [{ id: node48, name: 4.8, data: {}, children: [] }, { id: node49, name: 4.9, data: {}, children: [] }, { id: node410, name: 4.10, data: {}, children: [] }, { id: node411, name: 4.11, data: {}, children: [] }] }, Of course he properly closes up the JSON. I inserted id's (just an auto-incrementing number) and the data portion where needed. The name: is the part that has been the result of what you did before. Here's the code... I did a bit of shuffling and actually tested against a test db table: ?mysql :) DROP TABLE IF EXISTS tiers; CREATE TABLE tiers ( company INT NOT NULL, tier1 VARCHAR( 32 ) NOT NULLDEFAULT '', tier2 VARCHAR( 32 ) NOT NULLDEFAULT '', tier3 VARCHAR( 32 ) NOT NULLDEFAULT '', tier4 VARCHAR( 32 ) NOT NULLDEFAULT '', tier5 VARCHAR( 32 ) NOT NULLDEFAULT '', tier6 VARCHAR( 32 ) NOT NULLDEFAULT '', tier7 VARCHAR( 32 ) NOT NULLDEFAULT '', tier8 VARCHAR( 32 ) NOT NULLDEFAULT '', tier9 VARCHAR( 32 ) NOT NULLDEFAULT '', tier10 VARCHAR( 32 ) NOT NULLDEFAULT '', tier11 VARCHAR( 32 ) NOT NULLDEFAULT '', tier12 VARCHAR( 32 ) NOT NULLDEFAULT '', tier13 VARCHAR( 32 ) NOT NULLDEFAULT '', tier14 VARCHAR( 32 ) NOT NULLDEFAULT '' ); INSERT INTO tiers (company, tier1, tier2, tier3) VALUES (1, 'exec-001','sub-exec-011','sub-sub-exec-111'), (1, 'exec-001','sub-exec-011','sub-sub-exec-112'), (1, 'exec-001','sub-exec-012','sub-sub-exec-121'), (1, 'exec-001','sub-exec-012','sub-sub-exec-122'), (1, 'exec-002','sub-exec-021','sub-sub-exec-211'), (1, 'exec-002','sub-exec-021','sub-sub-exec-212'), (1, 'exec-002','sub-exec-022','sub-sub-exec-221'), (1, 'exec-002','sub-exec-022','sub-sub-exec-222'); ? And here's the code: ?php function getTiers( $company ) { // // Establish the root. // $sDb = ijinn_getServiceRef( 'dbManager' ); $db = $sDb-getConnectionRef(); $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = trim( $row['tier'.$i] ); if( $name === '' ) { break; } if( !isset( $focus[$name] ) ) { $focus[$name] = array ( 'name' = $name, 'children' = array(), ); } $focus = $focus[$name]['children']; } } } $wrapper = array ( 'children' = $root ); postProcessTiers( $wrapper ); return $root; } function postProcessTiers( $root ) { $root['children'] = array_values( $root['children'] ); foreach( array_keys(
Re: [PHP] Thinking out loud - a continuation...
[snip]On 3/27/2012 12:21 AM, Robert Cummings wrote: I think you need two things... the recursive post processor that removes the string indexes for the children. And then a function that creates a JavaScript array expression from an object or array. The question I have for you... is given the following array structure that might be generated from my previous code: ?php array ( 'exec-001' = array ( 'name' = 'exec-001', 'children' = array ( 'sub-exec-011' = array ( 'name' = 'sub-exec-011', 'children' = array ( 'sub-sub-exec-111' = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 'sub-sub-exec-112' = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 'sub-exec-012' = array ( 'name' = 'sub-exec-012', 'children' = array ( 'sub-sub-exec-121' = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 'sub-sub-exec-122' = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 'exec-002' = array ( 'name' = 'exec-002', 'children' = array ( 'sub-exec-021' = array ( 'name' = 'sub-exec-021', 'children' = array ( 'sub-sub-exec-211' = array ( 'name' = 'sub-sub-exec-211', 'children' = array() ), 'sub-sub-exec-212' = array ( 'name' = 'sub-sub-exec-212', 'children' = array() ) ) ), 'sub-exec-022' = array ( 'name' = 'sub-exec-022', 'children' = array ( 'sub-sub-exec-221' = array ( 'name' = 'sub-sub-exec-221', 'children' = array() ), 'sub-sub-exec-222' = array ( 'name' = 'sub-sub-exec-222', 'children' = array() ) ) ) ) ) ); ? On first blush, I think you want the following structure (from your recent posts): ?php array ( 0 = array ( 'name' = 'exec-001', 'children' = array ( 0 = array ( 'name' = 'sub-exec-011', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 1 = array ( 'name' = 'sub-exec-012', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 1 = array ( 'name' = 'exec-002', 'children' = array ( 0 = array ( 'name' = 'sub-exec-021', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-211', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-212', 'children' = array() ) ) ), 1 = array ( 'name' = 'sub-exec-022', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-221', 'children' = array() ), 1 = array
Re: [PHP] Thinking out loud - a continuation...
[snip]On 3/27/2012 12:21 AM, Robert Cummings wrote: I think you need two things... the recursive post processor that removes the string indexes for the children. And then a function that creates a JavaScript array expression from an object or array. The question I have for you... is given the following array structure that might be generated from my previous code: ?php array ( 'exec-001' = array ( 'name' = 'exec-001', 'children' = array ( 'sub-exec-011' = array ( 'name' = 'sub-exec-011', 'children' = array ( 'sub-sub-exec-111' = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 'sub-sub-exec-112' = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 'sub-exec-012' = array ( 'name' = 'sub-exec-012', 'children' = array ( 'sub-sub-exec-121' = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 'sub-sub-exec-122' = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 'exec-002' = array ( 'name' = 'exec-002', 'children' = array ( 'sub-exec-021' = array ( 'name' = 'sub-exec-021', 'children' = array ( 'sub-sub-exec-211' = array ( 'name' = 'sub-sub-exec-211', 'children' = array() ), 'sub-sub-exec-212' = array ( 'name' = 'sub-sub-exec-212', 'children' = array() ) ) ), 'sub-exec-022' = array ( 'name' = 'sub-exec-022', 'children' = array ( 'sub-sub-exec-221' = array ( 'name' = 'sub-sub-exec-221', 'children' = array() ), 'sub-sub-exec-222' = array ( 'name' = 'sub-sub-exec-222', 'children' = array() ) ) ) ) ) ); ? On first blush, I think you want the following structure (from your recent posts): ?php array ( 0 = array ( 'name' = 'exec-001', 'children' = array ( 0 = array ( 'name' = 'sub-exec-011', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 1 = array ( 'name' = 'sub-exec-012', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 1 = array ( 'name' = 'exec-002', 'children' = array ( 0 = array ( 'name' = 'sub-exec-021', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-211', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-212', 'children' = array() ) ) ), 1 = array ( 'name' = 'sub-exec-022', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-221', 'children' = array() ), 1 = array
Re: [PHP] Thinking out loud - a continuation...
[snip] This is one of those projects. It is apparently going to be trying every step of the way. [/snip] I was proven right this morning after all of Robert's good work and what I had added to make this work. It turns out that the one service who was anxious to consume the JSON output expects that the JSON be a certain format. When I run their format through jslint it does not validate unless I add quotes around the name portion of the name:value pairs. In addition they use (perfectly valid) square brackets around the children groups that the output from json_encode() does not contain. I am ready to take a loss on this one but I really didn't lose - Robert gave me a great way to retrieve the data with one query and create valid JSON from it. Thanks again Robert! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 26 Mar 2012, at 19:12, Jay Blanchard wrote: [snip] This is one of those projects. It is apparently going to be trying every step of the way. [/snip] I was proven right this morning after all of Robert's good work and what I had added to make this work. It turns out that the one service who was anxious to consume the JSON output expects that the JSON be a certain format. When I run their format through jslint it does not validate unless I add quotes around the name portion of the name:value pairs. In addition they use (perfectly valid) square brackets around the children groups that the output from json_encode() does not contain. I am ready to take a loss on this one but I really didn't lose - Robert gave me a great way to retrieve the data with one query and create valid JSON from it. Thanks again Robert! Square brackets in JSON represent arrays. Take their JSON, run it through json_decode, and assuming it decodes correctly compare the structure to what you already have. You should then be able to modify what you have so it generates JSON in the format they are expecting. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Square brackets in JSON represent arrays. Take their JSON, run it through json_decode, and assuming it decodes correctly compare the structure to what you already have. You should then be able to modify what you have so it generates JSON in the format they are expecting. [/snip] Done. I knew about the square brackets. In the code being used the array, if blank, gets square brackets. For some reason an array containing actual data does not. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-26 02:12 PM, Jay Blanchard wrote: [snip] This is one of those projects. It is apparently going to be trying every step of the way. [/snip] I was proven right this morning after all of Robert's good work and what I had added to make this work. It turns out that the one service who was anxious to consume the JSON output expects that the JSON be a certain format. When I run their format through jslint it does not validate unless I add quotes around the name portion of the name:value pairs. In addition they use (perfectly valid) square brackets around the children groups that the output from json_encode() does not contain. I am ready to take a loss on this one but I really didn't lose - Robert gave me a great way to retrieve the data with one query and create valid JSON from it. Thanks again Robert! *lol* No worries... it's all about solving problems :) Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] *lol* No worries... it's all about solving problems :) [/snip] the other folks who needed to consume the JSON have all done so successfully today - just this one. The guy who runs it was plenty arrogant when I discussed with him. He is the one who wanted me to remove the extra array name. I cooked up some regex to do that but then all of the opening/closing curlies were out of whack. If I had kept going it would have been maddening. I told him he needed to fix his JSON parsing. He said I needed to add the square brackets. Programmer stand-off. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-26 05:14 PM, Jay Blanchard wrote: [snip] *lol* No worries... it's all about solving problems :) [/snip] the other folks who needed to consume the JSON have all done so successfully today - just this one. The guy who runs it was plenty arrogant when I discussed with him. He is the one who wanted me to remove the extra array name. I cooked up some regex to do that but then all of the opening/closing curlies were out of whack. If I had kept going it would have been maddening. I told him he needed to fix his JSON parsing. He said I needed to add the square brackets. Programmer stand-off. Did you end up with a satisfactory output? It's not overly difficult to generate an array instead of an object. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Did you end up with a satisfactory output? It's not overly difficult to generate an array instead of an object. [/snip] I did for all but this one instance. Are you saying that it would be easy to make of the children arrays? I thought they were already - am I missing something? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-26 06:52 PM, Jay Blanchard wrote: [snip] Did you end up with a satisfactory output? It's not overly difficult to generate an array instead of an object. [/snip] I did for all but this one instance. Are you saying that it would be easy to make of the children arrays? I thought they were already - am I missing something? They are arrays... but JSON_encode is creating objects. You can create arrays by traversing the array structure recursively and outputing your own JavaScript code to build a JavaScript array. I don't know if that would serve the purpose, but you would end up with an array. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] On Mar 26, 2012, at 5:58 PM, Robert Cummings wrote: On 12-03-26 06:52 PM, Jay Blanchard wrote: [snip] Did you end up with a satisfactory output? It's not overly difficult to generate an array instead of an object. [/snip] I did for all but this one instance. Are you saying that it would be easy to make of the children arrays? I thought they were already - am I missing something? They are arrays... but JSON_encode is creating objects. You can create arrays by traversing the array structure recursively and outputing your own JavaScript code to build a JavaScript array. I don't know if that would serve the purpose, but you would end up with an array. [/snip] I'm listening - so could this be added to the code that you just wrote? Or do I need to recurse the output from json_encode()? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-26 07:05 PM, Jay Blanchard wrote: [snip] On Mar 26, 2012, at 5:58 PM, Robert Cummings wrote: On 12-03-26 06:52 PM, Jay Blanchard wrote: [snip] Did you end up with a satisfactory output? It's not overly difficult to generate an array instead of an object. [/snip] I did for all but this one instance. Are you saying that it would be easy to make of the children arrays? I thought they were already - am I missing something? They are arrays... but JSON_encode is creating objects. You can create arrays by traversing the array structure recursively and outputing your own JavaScript code to build a JavaScript array. I don't know if that would serve the purpose, but you would end up with an array. [/snip] I'm listening - so could this be added to the code that you just wrote? Or do I need to recurse the output from json_encode()? I think you need two things... the recursive post processor that removes the string indexes for the children. And then a function that creates a JavaScript array expression from an object or array. The question I have for you... is given the following array structure that might be generated from my previous code: ?php array ( 'exec-001' = array ( 'name' = 'exec-001', 'children' = array ( 'sub-exec-011' = array ( 'name' = 'sub-exec-011', 'children' = array ( 'sub-sub-exec-111' = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 'sub-sub-exec-112' = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 'sub-exec-012' = array ( 'name' = 'sub-exec-012', 'children' = array ( 'sub-sub-exec-121' = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 'sub-sub-exec-122' = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 'exec-002' = array ( 'name' = 'exec-002', 'children' = array ( 'sub-exec-021' = array ( 'name' = 'sub-exec-021', 'children' = array ( 'sub-sub-exec-211' = array ( 'name' = 'sub-sub-exec-211', 'children' = array() ), 'sub-sub-exec-212' = array ( 'name' = 'sub-sub-exec-212', 'children' = array() ) ) ), 'sub-exec-022' = array ( 'name' = 'sub-exec-022', 'children' = array ( 'sub-sub-exec-221' = array ( 'name' = 'sub-sub-exec-221', 'children' = array() ), 'sub-sub-exec-222' = array ( 'name' = 'sub-sub-exec-222', 'children' = array() ) ) ) ) ) ); ? On first blush, I think you want the following structure (from your recent posts): ?php array ( 0 = array ( 'name' = 'exec-001', 'children' = array ( 0 = array ( 'name' = 'sub-exec-011', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-111', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-112', 'children' = array() ) ) ), 1 = array ( 'name' = 'sub-exec-012', 'children' = array ( 0 = array ( 'name' = 'sub-sub-exec-121', 'children' = array() ), 1 = array ( 'name' = 'sub-sub-exec-122', 'children' = array() ) ) ) ) ), 1 = array ( 'name' = 'exec-002', 'children' = array ( 0 = array (
Re: [PHP] Thinking out loud - a continuation...
[snip] a necessary part of building the structure. It can be removed but only as a post process. Why does it have to be removed? You can loop through the structure in JavaScript without paying heed to the key's value. If it absolutely must go... you need to recurse through the final structure replacing each children entry with the results of passing it through array_values(). [/snip] Unfortunately the user of the JSON will not make a change to their app. I have looked through the JSON to confirm that this has a particular pattern so I think I can just do a little regex and get it squared away. This is one of those projects. It is apparently going to be trying every step of the way. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Did you send me a sample dump for your table :) [/snip] I'll do that today. I got side-tracked last night. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Mar 23, 2012, at 11:24 PM, Robert Cummings wrote: On 12-03-23 05:41 PM, Jay Blanchard wrote: [-- DELETED GARBAGE --] :) I just realized... I've been stuck in a thinking rut. I latched onto one solution that works well in some case but didn't fully examine the nuances of your own scenario. Given the way you are creating your hierarchy you will ultimately retrieve all rows. As such the following simple solution will do what you need: ?php $company = 1; $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = $row['tier'.$i]; if( !isset( $focus[$name] ) ) { $focus[$name] = array(); } $focus = $focus[$name]; } } } $json = JSON_encode( $root ); ? Cheers, Rob. -- At first blush I'm not sure how this would work - but I haven't had any coffee yet either. I'll give this a shot in a little while. Seems almost too easy. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Sat, Mar 24, 2012 at 7:41 AM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: On Mar 23, 2012, at 11:24 PM, Robert Cummings wrote: On 12-03-23 05:41 PM, Jay Blanchard wrote: [-- DELETED GARBAGE --] :) I just realized... I've been stuck in a thinking rut. I latched onto one solution that works well in some case but didn't fully examine the nuances of your own scenario. Given the way you are creating your hierarchy you will ultimately retrieve all rows. As such the following simple solution will do what you need: ?php $company = 1; $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = $row['tier'.$i]; if( !isset( $focus[$name] ) ) { $focus[$name] = array(); } $focus = $focus[$name]; } } } $json = JSON_encode( $root ); ? Cheers, Rob. -- At first blush I'm not sure how this would work - but I haven't had any coffee yet either. I'll give this a shot in a little while. Seems almost too easy. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This has been fascinating to read. I hope you get it figured out, Jay! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-24 08:41 AM, Jay Blanchard wrote: On Mar 23, 2012, at 11:24 PM, Robert Cummings wrote: On 12-03-23 05:41 PM, Jay Blanchard wrote: [-- DELETED GARBAGE --] :) I just realized... I've been stuck in a thinking rut. I latched onto one solution that works well in some case but didn't fully examine the nuances of your own scenario. Given the way you are creating your hierarchy you will ultimately retrieve all rows. As such the following simple solution will do what you need: ?php $company = 1; $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus =$root; for( $i = 1; $i= 14; $i++ ) { $name = $row['tier'.$i]; if( !isset( $focus[$name] ) ) { $focus[$name] = array(); } $focus =$focus[$name]; } } } $json = JSON_encode( $root ); ? The crux of it is: $focus = $focus[$name]; because it facilitates moving deeper and deeper into the array. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip]The crux of it is: $focus = $focus[$name]; [/snip] It works as I expect so far. All I have to do is figure out how to make the element a name: element in the JSON. for instance an element would look like this { name: Bob, children: [] } So the PHP array would have to look like this - $json = array ( name = Bob, children = array ( name = Angie ) ) and so on. This is for one of the services that will consume the JSON.
Re: [PHP] Thinking out loud - a continuation...
On 12-03-24 01:09 PM, Jay Blanchard wrote: [snip]The crux of it is: $focus =$focus[$name]; [/snip] It works as I expect so far. All I have to do is figure out how to make the element a name: element in the JSON. for instance an element would look like this { name: Bob, children: [] } So the PHP array would have to look like this - $json = array ( name = Bob, children = array ( name = Angie ) ) and so on. This is for one of the services that will consume the JSON. A little tweak here... a little tweak there: ?php // // Establish the root. // $company = 1; $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = $row['tier'.$i]; if( !isset( $focus[$name] ) ) { $focus[$name] = array ( 'name' = $name, 'children' = array(), ); } $focus = $focus[$name]['children']; } } } $json = JSON_encode( $root ); ? -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] A little tweak here... a little tweak there: [/snip] if( !isset( $focus[$name] ) ) { $focus[$name] = array ( 'name' = $name, 'children' = array(), ); } $focus = $focus[$name]['children']; } } } [/snip] Bingo! We have a winner! I had already started on the $focus[$name] array as you have shown, I just hadn't figured out the $focus[$name]['children'] part. Thank you so much Robert - you're a real asset to the PHP community! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] A little tweak here... a little tweak there: [/snip] One more little tweak may be required. The JSON looks like this {Executives and Management:{name:Executives and Management,children:{ The first part {Executives and Management: needs to be removed. I think I know what to do. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] One more little tweak may be required. The JSON looks like this {Executives and Management:{name:Executives and Management,children:{ The first part {Executives and Management: needs to be removed. I think I know what to do. [/snip] It has become painfully obvious that I do not know what to do. Ready to call it a day. Besides I just burned my finger setting up the smoker for the ribs. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-24 04:11 PM, Jay Blanchard wrote: [snip] One more little tweak may be required. The JSON looks like this {Executives and Management:{name:Executives and Management,children:{ The first part {Executives and Management: needs to be removed. I think I know what to do. [/snip] It has become painfully obvious that I do not know what to do. Ready to call it a day. Besides I just burned my finger setting up the smoker for the ribs. It's a necessary part of building the structure. It can be removed but only as a post process. Why does it have to be removed? You can loop through the structure in JavaScript without paying heed to the key's value. If it absolutely must go... you need to recurse through the final structure replacing each children entry with the results of passing it through array_values(). Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Your data structure doesn't appear to be very ummm normalized... Nonetheless, the following should do it: [/snip] You're absolutely correct. Unfortunately I am not the designer and cannot really do anything about it. I just have to work with what I have. Thank you very much for this - I will test it out this afternoon and let you know how it all goes. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 02:08 PM, Jay Blanchard wrote: [snip] Your data structure doesn't appear to be very ummm normalized... Nonetheless, the following should do it: [/snip] You're absolutely correct. Unfortunately I am not the designer and cannot really do anything about it. I just have to work with what I have. Thank you very much for this - I will test it out this afternoon and let you know how it all goes. I figured it was something you had been given... just thought I'd point out the obvious :D Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents = $children) ){ break; }
Re: [PHP] Thinking out loud - a continuation...
[snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents = $children) ){ break; } It would appear that both arrays are empty on the next cycle through so the break occurs. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 03:17 PM, Jay Blanchard wrote: [snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents =$children) ){ break; } I didn't actually test it... if you have trouble figuring out the problem feel free to send me a copy of your table (in private) and I'll debug :) Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 03:22 PM, Jay Blanchard wrote: [snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents =$children) ){ break; } It would appear that both arrays are empty on the next cycle through so the break occurs. Did you get any results form the database on the second run through the query loop? Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Mar 23, 2012, at 2:25 PM, Robert Cummings wrote: On 12-03-23 03:17 PM, Jay Blanchard wrote: [snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents =$children) ){ break; } I didn't actually test it... if you have trouble figuring out the problem feel free to send me a copy of your table (in private) and I'll debug :) I had it backwards. Both arrays are empty and the break should not occur because they are equal to each other. Let me send you a portion of the table Robert. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 03:28 PM, Jay Blanchard wrote: On Mar 23, 2012, at 2:25 PM, Robert Cummings wrote: On 12-03-23 03:17 PM, Jay Blanchard wrote: [snip] $json = JSON_encode( $root ); [/snip] Update on my test. This works perfectly Robert - thank you very much! But there is one small problem that I am trouble-shooting: it only goes one layer and doesn't progress any further. I suspect it is on this section of code that I am going to add some stuff to to see what is happening. if( !($parents =$children) ){ break; } I didn't actually test it... if you have trouble figuring out the problem feel free to send me a copy of your table (in private) and I'll debug :) I had it backwards. Both arrays are empty and the break should not occur because they are equal to each other. Let me send you a portion of the table Robert. No, I'm performing assignment... intentionally. Parent's becomes the previous children to move down a level. The following: if( !($parents = $children) ) performs assignment and an empty array check in one statement. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Did you get any results form the database on the second run through the query loop? [/snip] Actually, no. I just ran the raw query - SELECT DISTINCT `TIER3DATA` AS id, `TIER2DATA` AS parentId FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER2DATA` IN ('Executives and Management','Professionals','Technicians','Craft Workers-Skilled','Operatives','Contractor','Sales Workers','Laborers and Helpers','Admin Support') and it is empty. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] No, I'm performing assignment... intentionally. Parent's becomes the previous children to move down a level. The following: if( !($parents = $children) ) performs assignment and an empty array check in one statement. [/snip] Gotcha'. So all I am ever getting back right now is the result of the first query. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] SELECT DISTINCT `TIER3DATA` AS id, `TIER2DATA` AS parentId FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER2DATA` IN ('Executives and Management','Professionals','Technicians','Craft Workers-Skilled','Operatives','Contractor','Sales Workers','Laborers and Helpers','Admin Support') and it is empty. [/snip] I figured out part of the problem - the for loop starts at tier2 instead of tier1 Once I made that change I get the following error: Fatal error: Cannot use string offset as an array in /home/orcadept/public_html/poschart/json_chart.php on line 139 Line 139 is $item['children'][$id] = $child;
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 03:52 PM, Jay Blanchard wrote: [snip] SELECT DISTINCT `TIER3DATA` AS id, `TIER2DATA` AS parentId FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER2DATA` IN ('Executives and Management','Professionals','Technicians','Craft Workers-Skilled','Operatives','Contractor','Sales Workers','Laborers and Helpers','Admin Support') and it is empty. [/snip] I figured out part of the problem - the for loop starts at tier2 instead of tier1 It's meant to since outside the loop we address tier 1 when we generate the root and first set of parents (These have no parent ID so they are a special case). Once I made that change I get the following error: Fatal error: Cannot use string offset as an array in /home/orcadept/public_html/poschart/json_chart.php on line 139 Line 139 is $item['children'][$id] =$child; $item['children'] should be an array, somehow a string has been assigned :/ Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] $item['children'] should be an array, somehow a string has been assigned :/ [/snip] Yep. I am trying to figure that out now. I'm sure it is something really small. BTW, after making the change to the for loop there are results returned as we expected. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] $item['children'] should be an array, somehow a string has been assigned :/ [/snip] Yep. I am trying to figure that out now. I'm sure it is something really small. [/snip] I have been hammering away at it for a while now and still cannot find the issue. I'll push away for a while and come back to it. Robert I owe you so many thinks for getting me this far and opening me up to making this more efficient. I just have to push on through and get to the point where the JSON can be created and consumed. If any light bulb goes on over your head would you let me know. I have tried everything that I know works to keep this from being a string - I am just missing something. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] …stuff…. [/snip] For those interested here is where the problem seems to occur - $child = array ( 'id' = $id, 'parentId' = $pid, 'children' = array() ); $children[$id][] = $child; foreach( $parents[$pid] as $items ){ foreach( $items as $item ){ $item['children'][$id] = $child; // error: Cannot use string offset as an array } } Maybe someone will see this and know what's going on. Before the foreach $children is an array.
Re: [PHP] Thinking out loud - a continuation...
On 12-03-23 05:41 PM, Jay Blanchard wrote: [-- DELETED GARBAGE --] :) I just realized... I've been stuck in a thinking rut. I latched onto one solution that works well in some case but didn't fully examine the nuances of your own scenario. Given the way you are creating your hierarchy you will ultimately retrieve all rows. As such the following simple solution will do what you need: ?php $company = 1; $query = SELECT DISTINCT . * .FROM . tiers .WHERE . company = {$company} ; $root = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $focus = $root; for( $i = 1; $i = 14; $i++ ) { $name = $row['tier'.$i]; if( !isset( $focus[$name] ) ) { $focus[$name] = array(); } $focus = $focus[$name]; } } } $json = JSON_encode( $root ); ? Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 2012-03-21, at 2:39 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: This is a continuation of the nested query thing I posted to the list a while back. I was finally able to output a nested unordered array that worked out well, but scope-creep has come in the door and I have to change gears. I have a project where I have multiple queries and each query uses the results from the previous query to get it's results. I need to do one of two things, either out put a multidimensional array that I can use json_encode() on or I have to format the output from the queries as a JSON string. The resulting JSON will be used by a JavaScript widget and must be formed correctly. I created the following array by hand: $userList = array(John = array( email = j...@demo.com, website = www.john.com, age = 22, password = pass, description = array( hair = blonde, eyes = blue, build = medium )), Anna = array( email = a...@demo.com, website = www.anna.com, age = 24, password = pass, description = array( hair = brunette, eyes = hazel, build = petite ) )); I ran it through json_encode() and got the following output {John:{email:j...@demo.com,website:www.john.com,age:22,password:pass,description:{hair:blonde,eyes:blue,build:medium}},Anna:{email:a...@demo.com,website:www.anna.com,age:24,password:pass,description:{hair:brunette,eyes:hazel,build:petite}}} jslint.com verifies this as good JSON (although I thought there had to be square brackets around child arrays). If you were me would you just generate the JSON? If not what is he best way to output an array that will nest properly for each subsequent query? Thanks for any insight! Would it not be easier to get the data from a view which has the tables joined? Then it would be one query and it's a simple matter to format the results into the multi dimensional array then json? Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Would it not be easier to get the data from a view which has the tables joined? Then it would be one query and it's a simple matter to format the results into the multi dimensional array then json? [/snip] The data is all from one table. I'll write up a more thorough explanation in a little while. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Mar 21, 2012, at 3:45 PM, Daniel Brown wrote: I would, yes, but that's not the point. Is Anna single? I'm ready to trade Debs in for a newer model. -- /Daniel P. Brown Ah... to be young again. But, on the other hand, they have so much to learn. :-) Cheers, tedd _ tedd.sperl...@gmail.com http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] ...stuff... [/snip] Here is the explanation for what I have done and what I am trying to do - (based on the customer's request). A week or so ago I took a set of queries from one table and made them into an unordered list. This will be pseudo-code so that you get idea. SELECT DISTINCT column1 FROM table WHERE company = '1' while($column1 = mysql_fetch_array($query1results)){ SELECT DISTINCT column2 FROM table WHERE company = '1' AND column1 = $column1[0] while($column2 = mysql_fetch_array($query2results)){ SELECT DISTINCT column3 FROM table WHERE company = '1' AND column2 = $column2[0] } } This continues for up to 14 columns of data. I'm not worried about the recursive data retrieval, I have that part and like I said - I can output a nested unordered list from it quite handily. Now the customer wants JSON as the output. The JSON must reflect the children properly. So I have two choices, a multidimensional array that I can use json_encode() on or output a string that ultimately forms the JSON. We have all agreed that doing an array would be the best thing but I cannot wrap my head around it. If you have more questions fire away - I'd love to get this solved and off of my plate. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 11:28 AM, Jay Blanchard wrote: [snip] ...stuff... [/snip] Here is the explanation for what I have done and what I am trying to do - (based on the customer's request). A week or so ago I took a set of queries from one table and made them into an unordered list. This will be pseudo-code so that you get idea. SELECT DISTINCT column1 FROM table WHERE company = '1' while($column1 = mysql_fetch_array($query1results)){ SELECT DISTINCT column2 FROM table WHERE company = '1' AND column1 = $column1[0] while($column2 = mysql_fetch_array($query2results)){ SELECT DISTINCT column3 FROM table WHERE company = '1' AND column2 = $column2[0] } } This continues for up to 14 columns of data. I'm not worried about the recursive data retrieval, I have that part and like I said - I can output a nested unordered list from it quite handily. Now the customer wants JSON as the output. The JSON must reflect the children properly. So I have two choices, a multidimensional array that I can use json_encode() on or output a string that ultimately forms the JSON. We have all agreed that doing an array would be the best thing but I cannot wrap my head around it. If you have more questions fire away - I'd love to get this solved and off of my plate. Fix this code... I've come across codebases that did this specific type of nested querying and it resulted in 1 queries to the database on every page. Instead, create a layered approach: 1. Select your root elements. 2. Loop over in PHP and create an array of child IDs. 3. Select the children from the database. 4. Go to step 2. This way you will only every perform depth number of queries. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Fix this code... I've come across codebases that did this specific type of nested querying and it resulted in 1 queries to the database on every page. Instead, create a layered approach: 1. Select your root elements. 2. Loop over in PHP and create an array of child IDs. 3. Select the children from the database. 4. Go to step 2. This way you will only every perform depth number of queries. [/snip] I see what you're saying but I don't know that this reduces the number of queries - it just handles them in a different order. How do I get to the output that I need? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 11:58 AM, Jay Blanchard wrote: [snip] Fix this code... I've come across codebases that did this specific type of nested querying and it resulted in 1 queries to the database on every page. Instead, create a layered approach: 1. Select your root elements. 2. Loop over in PHP and create an array of child IDs. 3. Select the children from the database. 4. Go to step 2. This way you will only every perform depth number of queries. [/snip] I see what you're saying but I don't know that this reduces the number of queries - it just handles them in a different order. How do I get to the output that I need? It definitely reduces the queries... your current method gets all the first level nodes in one query, then performs a query for every single parent node. Mine only performs a query for each level in the tree. If you have 5 nodes at the first level you will perform 6 queries. Mine will perform 2. To generate the nesting structure at each level you track the level members. Something like the following (untested pseudoish): ?php $parents = query_for_first_level(); $root = $parents; while( $parents ) { $parentIds = array(); foreach( $parents as $parent ) { $parentIds[$parent['id']] = $parent['id']; } $children = query_for_children( $parentIds ); foreach( $children as $child ) { $parents[$child['parentId']]['children'][] = $child; } $parents = $children; } $jsonData = JSON_encode( $root ); ? Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 11:58 AM, Jay Blanchard wrote: [snip] Fix this code... I've come across codebases that did this specific type of nested querying and it resulted in 1 queries to the database on every page. Instead, create a layered approach: 1. Select your root elements. 2. Loop over in PHP and create an array of parent IDs. 3. Select the children from the database. 4. Go to step 2. This way you will only every perform depth number of queries. [/snip] I see what you're saying but I don't know that this reduces the number of queries - it just handles them in a different order. How do I get to the output that I need? Sorry, I just realized I didn't make the optimization explicitly obvious... when I say Select the children I mean to select them using an IN( id1, id2, id3 ) clause instead of a query for each. This is why we build the array of parent IDs (also I wrote build an array of child IDs, it should have read parent IDs and has been fixed above :). Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] On 3/22/2012 11:17 AM, Robert Cummings wrote: On 12-03-22 11:58 AM, Jay Blanchard wrote: [snip] Fix this code... I've come across codebases that did this specific type of nested querying and it resulted in 1 queries to the database on every page. Instead, create a layered approach: 1. Select your root elements. 2. Loop over in PHP and create an array of child IDs. 3. Select the children from the database. 4. Go to step 2. This way you will only every perform depth number of queries. [/snip] I see what you're saying but I don't know that this reduces the number of queries - it just handles them in a different order. How do I get to the output that I need? It definitely reduces the queries... your current method gets all the first level nodes in one query, then performs a query for every single parent node. Mine only performs a query for each level in the tree. If you have 5 nodes at the first level you will perform 6 queries. Mine will perform 2. [/snip] How so? A query must be performed for each parent node to get its children. parent | childchildchildchild | grandchild grandchild [snip] To generate the nesting structure at each level you track the level members. Something like the following (untested pseudoish): ?php $parents = query_for_first_level(); $root = $parents; while( $parents ) { $parentIds = array(); foreach( $parents as $parent ) { $parentIds[$parent['id']] = $parent['id']; } $children = query_for_children( $parentIds ); foreach( $children as $child ) { $parents[$child['parentId']]['children'][] = $child; } $parents = $children; } $jsonData = JSON_encode( $root ); ? [/snip] I'll try to apply this and see what I run into. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Sorry, I just realized I didn't make the optimization explicitly obvious... when I say Select the children I mean to select them using an IN( id1, id2, id3 ) clause instead of a query for each. This is why we build the array of parent IDs (also I wrote build an array of child IDs, it should have read parent IDs and has been fixed above :). [/snip] SELECT DISTINCT children FROM table WHERE column1 IN(id1, id2, id3) ? I am sure I am not following you now. Maybe I didn't explain clearly? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 12:34 PM, Jay Blanchard wrote: [snip] Sorry, I just realized I didn't make the optimization explicitly obvious... when I say Select the children I mean to select them using an IN( id1, id2, id3 ) clause instead of a query for each. This is why we build the array of parent IDs (also I wrote build an array of child IDs, it should have read parent IDs and has been fixed above :). [/snip] SELECT DISTINCT children FROM table WHERE column1 IN(id1, id2, id3) ? I am sure I am not following you now. Maybe I didn't explain clearly? What's the field for which you are selecting data? I've written this up as a parent/child relationship but it works for data/sub-data relationships also. SELECT itemId, otherData FROM table WHERE some condition; SELECT itemId, subData FROM otherTable WHERE itemId IN (id1, id2, ...); Then just link up the sub-data to the primary data in a loop and finally generate your JSON. Does that clarify? Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 3/22/2012 11:40 AM, Robert Cummings wrote: On 12-03-22 12:34 PM, Jay Blanchard wrote: [snip] Sorry, I just realized I didn't make the optimization explicitly obvious... when I say Select the children I mean to select them using an IN( id1, id2, id3 ) clause instead of a query for each. This is why we build the array of parent IDs (also I wrote build an array of child IDs, it should have read parent IDs and has been fixed above :). [/snip] SELECT DISTINCT children FROM table WHERE column1 IN(id1, id2, id3) ? I am sure I am not following you now. Maybe I didn't explain clearly? What's the field for which you are selecting data? I've written this up as a parent/child relationship but it works for data/sub-data relationships also. SELECT itemId, otherData FROM table WHERE some condition; SELECT itemId, subData FROM otherTable WHERE itemId IN (id1, id2, ...); Then just link up the sub-data to the primary data in a loop and finally generate your JSON. Does that clarify? [/snip] I must confess that the raging sinus headache and my general confusion makes this really unclear for me today. Maybe I should just set it aside for a day or so. I am super dense today. For each level I am selecting for each parent in the level above. Let's say that level 2 contains 8 people. Level 3 contains 14 people. Only some of the 14 belong to the 8 and must be associated properly. So how can I, with one query, associate level 3' 8th, 9th and 10th people with level 2's 6th person keeping in mind that the 9th person might also belong to level 2's 4th person. Just link up the sub-data? Place this array into a child array of the parent array? Again I apologize - maybe I should push away and let the customer know that it'll be a couple of more days. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 01:06 PM, Jay Blanchard wrote: On 3/22/2012 11:40 AM, Robert Cummings wrote: What's the field for which you are selecting data? I've written this up as a parent/child relationship but it works for data/sub-data relationships also. SELECT itemId, otherData FROM table WHERE some condition; SELECT itemId, subData FROM otherTable WHERE itemId IN (id1, id2, ...); Then just link up the sub-data to the primary data in a loop and finally generate your JSON. Does that clarify? [/snip] I must confess that the raging sinus headache and my general confusion makes this really unclear for me today. Maybe I should just set it aside for a day or so. I am super dense today. Rest might do you well on a number of levels :) For each level I am selecting for each parent in the level above. Let's say that level 2 contains 8 people. Level 3 contains 14 people. Only some of the 14 belong to the 8 and must be associated properly. So how can I, with one query, associate level 3' 8th, 9th and 10th people with level 2's 6th person keeping in mind that the 9th person might also belong to level 2's 4th person. At one point you indicated all the data was coming from one table. Can you send me the table fields and indicate which fields are used to determine parent child relationship? Also 2 sample rows of data which have a relationship would be helpful. Just link up the sub-data? Place this array into a child array of the parent array? Again I apologize - maybe I should push away and let the customer know that it'll be a couple of more days. Yeah... child array... 'children' :) Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] At one point you indicated all the data was coming from one table. Can you send me the table fields and indicate which fields are used to determine parent child relationship? Also 2 sample rows of data which have a relationship would be helpful. [/snip] Columns - tier1, tier2, tier3, tier4 etc. (ends with tier14) Children of tier1 are tier2 - select distinct tier2 from table where tier1 = foo and company = 1 select distinct tier2 from table where tier1 = bar and company = 1 etc. Children of tier2 are tier3, etc. tier1 tier2 tier3 1, executive, ceo,ceo 1, executive, vp-ops, vp-ops 1, executive, vp-admin, vp-admin mgr 1, executive, vp-admin, vp-admin ops mgr 1, executive, vp-admin, vp-admin mgr 1, executive, vp-admin, vp-admin clerk 1, professionalpro-mgr pro-admin 1, professionalpro-IT pro-dev 1, professionalpro-IT pro-infra 1, professionalpro-IT pro-dev 1, technician tech-admin tech-admin mgr 1, technician tech-opstech-ops mgr Thanks for all of your help. I know I am being a PITA. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-22 03:54 PM, Jay Blanchard wrote: [snip] At one point you indicated all the data was coming from one table. Can you send me the table fields and indicate which fields are used to determine parent child relationship? Also 2 sample rows of data which have a relationship would be helpful. [/snip] Columns - tier1, tier2, tier3, tier4 etc. (ends with tier14) Children of tier1 are tier2 - select distinct tier2 from table where tier1 = foo and company = 1 select distinct tier2 from table where tier1 = bar and company = 1 etc. Children of tier2 are tier3, etc. tier1 tier2 tier3 1, executive, ceo,ceo 1, executive, vp-ops, vp-ops 1, executive, vp-admin, vp-admin mgr 1, executive, vp-admin, vp-admin ops mgr 1, executive, vp-admin, vp-admin mgr 1, executive, vp-admin, vp-admin clerk 1, professionalpro-mgr pro-admin 1, professionalpro-IT pro-dev 1, professionalpro-IT pro-infra 1, professionalpro-IT pro-dev 1, technician tech-admin tech-admin mgr 1, technician tech-opstech-ops mgr Thanks for all of your help. I know I am being a PITA. Your data structure doesn't appear to be very ummm normalized... Nonetheless, the following should do it: ?php // // Establish the root. // $company = 1; $query = SELECT DISTINCT . tier1 AS id .FROM . tiers .WHERE . company = {$company} ; $root = array(); $children = array(); if( $db-query( $query ) ) { while( ($row = $db-fetchRow()) ) { $id = $row['id']; unset( $child ); $child = array ( 'id' = $id, 'parentId' = false, 'children' = array(); ); $root[$id] = $child; $children[$id][] = $child; } } // // Establish the nested levels. // for( $tier = 2; $tier = 14; $tier++ ) { if( !($parents = $children) ) { break; } $parentTier = $tier - 1; $parentIds = array(); foreach( array_keys( $parents ) as $parentId ) { $parentIds[$parentId] = $db-quote( $parentId ); } $query = SELECT DISTINCT . tier{$tier} AS id, . tier{$parentTier} AS parentId .FROM . tiers .WHERE . company = {$company} . AND . tier{$parentTier} IN (.implode( ',', $parentIds ).) ; if( $db-query( $query ) ) { unset( $children ); $children = array(); while( ($row = $db-fetchRow()) ) { $id = $row['id']; $pid = $row['parentId']; unset( $child ); $child = array ( 'id' = $id, 'parentId' = $pid, 'children' = array(); ); $children[$id][] = $child; foreach( $parents[$pid] as $items ) { foreach( $items as $item ) { $item['children'][$id] = $child; } } } } } $json = JSON_encode( $root ); ? Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Thinking out loud - a continuation...
This is a continuation of the nested query thing I posted to the list a while back. I was finally able to output a nested unordered array that worked out well, but scope-creep has come in the door and I have to change gears. I have a project where I have multiple queries and each query uses the results from the previous query to get it's results. I need to do one of two things, either out put a multidimensional array that I can use json_encode() on or I have to format the output from the queries as a JSON string. The resulting JSON will be used by a JavaScript widget and must be formed correctly. I created the following array by hand: $userList = array(John = array( email = j...@demo.com, website = www.john.com, age = 22, password = pass, description = array( hair = blonde, eyes = blue, build = medium )), Anna = array( email = a...@demo.com, website = www.anna.com, age = 24, password = pass, description = array( hair = brunette, eyes = hazel, build = petite ) )); I ran it through json_encode() and got the following output {John:{email:j...@demo.com,website:www.john.com,age:22,password:pass,description:{hair:blonde,eyes:blue,build:medium}},Anna:{email:a...@demo.com,website:www.anna.com,age:24,password:pass,description:{hair:brunette,eyes:hazel,build:petite}}} jslint.com verifies this as good JSON (although I thought there had to be square brackets around child arrays). If you were me would you just generate the JSON? If not what is he best way to output an array that will nest properly for each subsequent query? Thanks for any insight!
Re: [PHP] Thinking out loud - a continuation...
On Wed, Mar 21, 2012 at 2:39 PM, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: ... I have a project where I have multiple queries and each query uses the results from the previous query to get it's results. I need to do one of two things, either out put a multidimensional array that I can use json_encode() on or I have to format the output from the queries as a JSON string. The resulting JSON will be used by a JavaScript widget and must be formed correctly. I created the following array by hand: $userList = array(John = array( email = j...@demo.com, website = www.john.com, age = 22, password = pass, description = array( hair = blonde, eyes = blue, build = medium )), Anna = array( email = a...@demo.com, website = www.anna.com, age = 24, password = pass, description = array( hair = brunette, eyes = hazel, build = petite ) )); I ran it through json_encode() and got the following output {John:{email:j...@demo.com,website:www.john.com,age:22,password:pass,description:{hair:blonde,eyes:blue,build:medium}},Anna:{email:a...@demo.com,website:www.anna.com,age:24,password:pass,description:{hair:brunette,eyes:hazel,build:petite}}} jslint.com verifies this as good JSON (although I thought there had to be square brackets around child arrays). Speaking to your belief that arrays had to have square brackets, json_encode examines the PHP array and only encodes sequential numbers JSON arrays. Others (as in your case) are encoded as object literals: http://php.net/manual/en/function.json-encode.php That said, you can still access Javascript Object properties with array access if you prefer in the client code: http://www.quirksmode.org/js/associative.html If you were me would you just generate the JSON? If not what is he best way to output an array that will nest properly for each subsequent query? Because of the options json_encode provides and the flexibility it affords while in PHP, I would generate PHP and then always use json_encode to generate the JSON as needed. Adam -- Nephtali: A simple, flexible, fast, and security-focused PHP framework http://nephtaliproject.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On Wed, Mar 21, 2012 at 14:39, Jay Blanchard jay.blanch...@sigmaphinothing.org wrote: [snip!] I ran it through json_encode() and got the following output {John:{email:j...@demo.com,website:www.john.com,age:22,password:pass,description:{hair:blonde,eyes:blue,build:medium}},Anna:{email:a...@demo.com,website:www.anna.com,age:24,password:pass,description:{hair:brunette,eyes:hazel,build:petite}}} jslint.com verifies this as good JSON (although I thought there had to be square brackets around child arrays). If you were me would you just generate the JSON? If not what is he best way to output an array that will nest properly for each subsequent query? I would, yes, but that's not the point. Is Anna single? I'm ready to trade Debs in for a newer model. -- /Daniel P. Brown Network Infrastructure Manager http://www.php.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] I would, yes, but that's not the point. Is Anna single? I'm ready to trade Debs in for a newer model. [/snip] I'm thinking that Debs would upset your array if you traded her in. Anyhow, I have spent the last hour trying to output valid JSON but the whole thing is making me barking mad. I may try create a multidimensional array here in a little bit. After I go for a walk. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-21 03:52 PM, Jay Blanchard wrote: [snip] I would, yes, but that's not the point. Is Anna single? I'm ready to trade Debs in for a newer model. [/snip] I'm thinking that Debs would upset your array if you traded her in. Anyhow, I have spent the last hour trying to output valid JSON but the whole thing is making me barking mad. I may try create a multidimensional array here in a little bit. After I go for a walk. Hi Jay, Why are you trying to create the JSON structure in parts? When I have nesting like this i build the full nested structure as PHP, then export to JSON. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
[snip] Why are you trying to create the JSON structure in parts? When I have nesting like this i build the full nested structure as PHP, then export to JSON. [/snip] As PHP? An array? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 03/21/2012 11:39 AM, Jay Blanchard wrote: If you were me would you just generate the JSON? If not what is he best way to output an array that will nest properly for each subsequent query? Depends on where the data is coming from and how you are retrieving it from. Can you provide examples of the code that retrieves the data and some of the actual output data? Then provide a structure that you want the data to look like when done. -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Thinking out loud - a continuation...
On 12-03-21 04:42 PM, Jay Blanchard wrote: [snip] Why are you trying to create the JSON structure in parts? When I have nesting like this i build the full nested structure as PHP, then export to JSON. [/snip] As PHP? An array? Yeah sorry... you know what I meant ;) Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php