php-general Digest 2 Mar 2012 05:24:20 -0000 Issue 7709
Topics (messages 316822 through 316833):
Re: Nested database loops and completing an unordered list....
316822 by: FeIn
316823 by: Tommy Pham
316824 by: Jay Blanchard
316825 by: Jay Blanchard
316827 by: Jay Blanchard
316828 by: Jay Blanchard
316829 by: Jim Lucas
316830 by: Tommy Pham
316831 by: Jay Blanchard
316832 by: Jay Blanchard
316833 by: Bastien
PHP 5.4.0 released!
316826 by: David Soria Parra
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
And see also this, which focuses only on the database part of the problem:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
On Thu, Mar 1, 2012 at 5:08 PM, FeIn <aci...@gmail.com> wrote:
> I don't how how you keep your data in your database but there is no need
> to issues that many queries to retrieve your data. From what I understand
> the data you want to display is hierarchical. Here's an article that will
> hopefully point you to a solution (there are more out there, some better
> than other, google mysql hierchical data or database hierarchical data).
> The article I was talking about is here:
> http://www.sitepoint.com/hierarchical-data-database/
>
>
>
>
> On Thu, Mar 1, 2012 at 4:29 PM, Jay Blanchard <
> jay.blanch...@sigmaphinothing.org> wrote:
>
>> Good morning PHP groupies!
>>
>> I am working on this tool that will ultimately display a collapsible org
>> chart. The org chart is based on a nested unordered list and that is the
>> heart of my question.
>>
>> The NUL(nested unordered list) is based on a set of database queries -
>> sometimes as many as 14 queries. Each query relies on data returned by all
>> of the the queries before it. So what I am doing right now is this -
>>
>> query generates a list item
>> while this list item get the next level dependent upon this item
>> query generates this list item
>> while this list item get the next level dependent on each
>> list item above
>>
>> ...and so on. (I have written about this before and thought I had it
>> solved, but alas, that is not the case.) The result needs to be something
>> like this:
>>
>> <ul>
>> <li>level a
>> <ul>
>> <li>level b</li> // has no children
>> <li>level b
>> <ul>
>> <li>level c</li>
>> </ul>
>> </li>
>> </ul>
>> </li>
>> </ul>
>>
>> This is a semantically and syntacticallycorrect UL. Keep in mind that
>> this can go many levels deeper. The hardest part, and the part that I am
>> looking to accomplish, is closing the list items properly regardless of how
>> deep the tree is. If properly handled this could even be made into JSON
>> with the proper syntax, but I am not worried about that now. I was hoping
>> that a fresh set of eyes would point me to a solution that I obviously
>> cannot see at the moment.
>>
>> Thanks!
>>
>> Jay
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
--- End Message ---
--- Begin Message ---
On Thu, Mar 1, 2012 at 6:29 AM, Jay Blanchard
<jay.blanch...@sigmaphinothing.org> wrote:
> Good morning PHP groupies!
>
> I am working on this tool that will ultimately display a collapsible org
> chart. The org chart is based on a nested unordered list and that is the
> heart of my question.
>
> The NUL(nested unordered list) is based on a set of database queries -
> sometimes as many as 14 queries. Each query relies on data returned by all
> of the the queries before it. So what I am doing right now is this -
>
> query generates a list item
> while this list item get the next level dependent upon this item
> query generates this list item
> while this list item get the next level dependent on each
> list item above
>
> ...and so on. (I have written about this before and thought I had it solved,
> but alas, that is not the case.) The result needs to be something like this:
>
> <ul>
> <li>level a
> <ul>
> <li>level b</li> // has no children
> <li>level b
> <ul>
> <li>level c</li>
> </ul>
> </li>
> </ul>
> </li>
> </ul>
>
> This is a semantically and syntacticallycorrect UL. Keep in mind that this
> can go many levels deeper. The hardest part, and the part that I am looking
> to accomplish, is closing the list items properly regardless of how deep the
> tree is. If properly handled this could even be made into JSON with the
> proper syntax, but I am not worried about that now. I was hoping that a
> fresh set of eyes would point me to a solution that I obviously cannot see
> at the moment.
>
> Thanks!
>
> Jay
>
Your situation sounds like list of categories for a e-commerce site.
Each category (like a person) is unique and may have a parent category
(boss) like that of a CEO.
** table structure:
CREATE TABLE IF NOT EXIST employees (
employeeID INT NOT NULL PRIMARY KEY,
bossID INT NOT NULL DEFAULT '0',
firstName VARCHAR(50) NOT NULL,
middle VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL
)
** SQL query:
SELECT * FROM employees ORDER BY bossID;
** PHP pseudo code:
// $employees = array(0 => array());
// CEO doesn't have a boss thus you get his/her info via $bigBossID =
$employees[0]['subordinates'][0];
// connect to DB
// execute query
// iterate results
while ($row = $result->fetch_assoc()) {
$employees[$row['employeeID']] = $row;
if (!empty($row['bossID'])
$employees[$row['bossID']]['subordinates'] = $row['employeeID'];
}
// free result
// close connection if need be
You can get any person's info via $employees[$employeeID] and get the
manager/director/VP/etc subordinates:
if (isset($employees[$employeeID]['subordinates']) &&
!empty($employees[$employeeID]['subordinates']))
foreach ($employees[$employeeID]['subordinates'] => $subordinateID) {
// access subordinate's info via $employees[$subordinateID]
}
else
echo "{$employees[$employeeID]['firstName']} does not have subordinates.";
If there's any organizational change, the code still works.
HTH,
Tommy
Disclaimer: the above syntax is from memory. I haven't done SQL
manipulation or PHP coding in over a year ;) so check and adjust
accordingly to your RDBMS and application's design.
--- End Message ---
--- Begin Message ---
Thanks FeIn, I'll give these articles and methods a look this afternoon.
I'm sure that it will lead to more questions, so I'll be back.
On 3/1/2012 9:16 AM, FeIn wrote:
And see also this, which focuses only on the database part of the
problem:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
On Thu, Mar 1, 2012 at 5:08 PM, FeIn <aci...@gmail.com
<mailto:aci...@gmail.com>> wrote:
I don't how how you keep your data in your database but there is
no need to issues that many queries to retrieve your data. From
what I understand the data you want to display is hierarchical.
Here's an article that will hopefully point you to a solution
(there are more out there, some better than other, google mysql
hierchical data or database hierarchical data). The article I was
talking about is here:
http://www.sitepoint.com/hierarchical-data-database/
--- End Message ---
--- Begin Message ---
On 3/1/2012 9:59 AM, Jay Blanchard wrote:
Thanks FeIn, I'll give these articles and methods a look this
afternoon. I'm sure that it will lead to more questions, so I'll be back.
I also forgot to say that I cannot modify the database structure - the
client is very strict about that. But it does look like the adjacency
list model - each records list each of its tiers from 1 to 14.
--- End Message ---
--- Begin Message ---
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to walk
away for a bit.
I have an array of tiers….
Array
(
[0] => TIER1DATA
[1] => TIER2DATA
[2] => TIER3DATA
[3] => BUSTIER1DATA
[4] => BUSTIER2DATA
[5] => BUSTIER3DATA
[6] => BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA is
the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i."<br />";
/* retrieve children of parent */
$get = "SELECT DISTINCT `". $tierArray[$i] ."` FROM `POSITION_SETUP` ";
$get .= "WHERE `COMPANY_ID` = '3' ";
if($i > 0) {
$get .= "AND `" . $tierArray[$i - 1] . "` = '" . $child . "' ";
}
echo $get."<br />";
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . "\t" . mysql_error() . "\n";
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat(' ',$i).$row[$tierArray[$i]]."<br />\n";
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I cannot
get it to go deeper. I have just been looking at it for too long. TVMIA!
--- End Message ---
--- Begin Message ---
On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
> [snip]…stuff…[/snip]
>
> I am getting close, but I am also getting frustrated. I probably need to walk
> away for a bit.
>
> I have an array of tiers….
>
> Array
> (
> [0] => TIER1DATA
> [1] => TIER2DATA
> [2] => TIER3DATA
> [3] => BUSTIER1DATA
> [4] => BUSTIER2DATA
> [5] => BUSTIER3DATA
> [6] => BUSTIER4DATA
> )
> Each of the tiers which represent parents in children in pairs. TIER1DATA is
> the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
> so far - any insight would be valuable and extremely appreciated by me.
>
> function display_children($i, $child) {
> global $dbc;
> global $tierArray;
> echo $i."<br />";
> /* retrieve children of parent */
> $get = "SELECT DISTINCT `". $tierArray[$i] ."` FROM `POSITION_SETUP` ";
> $get .= "WHERE `COMPANY_ID` = '3' ";
> if($i > 0) {
> $get .= "AND `" . $tierArray[$i - 1] . "` = '" . $child . "' ";
> }
> echo $get."<br />";
> if(!($result = mysql_query($get, $dbc))) {
> echo mysql_errno() . "\t" . mysql_error() . "\n";
> exit();
> }
>
> while($row = mysql_fetch_array($result)) {
>
> /* indent and display the title of this child */
> echo str_repeat(' ',$i).$row[$tierArray[$i]]."<br />\n";
>
> /* get the children's children */
> display_children($i + 1, $row[$tierArray[$i]]);
> }
> }
>
> It does produce the right query for only the first child in each case, I
> cannot get it to go deeper. I have just been looking at it for too long.
> TVMIA!
Forgot to say that if I isolate any one level without recursion I get all of
the results for that level.
--- End Message ---
--- Begin Message ---
On 03/01/2012 04:39 PM, Jay Blanchard wrote:
On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
[snip]…stuff…[/snip]
I am getting close, but I am also getting frustrated. I probably need to walk
away for a bit.
I have an array of tiers….
Array
(
[0] => TIER1DATA
[1] => TIER2DATA
[2] => TIER3DATA
[3] => BUSTIER1DATA
[4] => BUSTIER2DATA
[5] => BUSTIER3DATA
[6] => BUSTIER4DATA
)
Each of the tiers which represent parents in children in pairs. TIER1DATA is
the parent of TIER2DATA, etc. representing 7 columns of data. Here is my code
so far - any insight would be valuable and extremely appreciated by me.
function display_children($i, $child) {
global $dbc;
global $tierArray;
echo $i."<br />";
/* retrieve children of parent */
$get = "SELECT DISTINCT `". $tierArray[$i] ."` FROM `POSITION_SETUP` ";
$get .= "WHERE `COMPANY_ID` = '3' ";
if($i> 0) {
$get .= "AND `" . $tierArray[$i - 1] . "` = '" . $child . "' ";
}
echo $get."<br />";
if(!($result = mysql_query($get, $dbc))) {
echo mysql_errno() . "\t" . mysql_error() . "\n";
exit();
}
while($row = mysql_fetch_array($result)) {
/* indent and display the title of this child */
echo str_repeat(' ',$i).$row[$tierArray[$i]]."<br />\n";
/* get the children's children */
display_children($i + 1, $row[$tierArray[$i]]);
}
}
It does produce the right query for only the first child in each case, I cannot
get it to go deeper. I have just been looking at it for too long. TVMIA!
Forgot to say that if I isolate any one level without recursion I get all of
the results for that level.
How are you calling this? What are your initial arguments?
What does your DB schema look like?
Can you show a brief example of the data in the table?
Dump: SELECT * FROM position_setup WHERE company_id='3'
Can you show the output of the function above?
--
Jim Lucas
http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/
--- End Message ---
--- Begin Message ---
On Thu, Mar 1, 2012 at 4:36 PM, Jay Blanchard
<jay.blanch...@sigmaphinothing.org> wrote:
> [snip]…stuff…[/snip]
>
> I am getting close, but I am also getting frustrated. I probably need to walk
> away for a bit.
>
> I have an array of tiers….
>
> Array
> (
> [0] => TIER1DATA
> [1] => TIER2DATA
> [2] => TIER3DATA
> [3] => BUSTIER1DATA
> [4] => BUSTIER2DATA
> [5] => BUSTIER3DATA
> [6] => BUSTIER4DATA
> )
> Each of the tiers which represent parents in children in pairs. TIER1DATA is
> the parent of TIER2DATA, etc. representing 7 columns of data.
>
<snip/>
How are those tiers assembled? Do you know what the table structure
is? Can you disclose it in an obfuscate fashion? You're asking for
help on the best way to retrieve and manipulate the data in PHP but
you haven't disclose how it's represented at the source. That's like
asking us how to get water without telling the location of the water:
ocean, lake, underground, etc...... You get the idea.
Best regards,
Tommy
--- End Message ---
--- Begin Message ---
On Mar 1, 2012, at 7:45 PM, Jim Lucas wrote:
> On 03/01/2012 04:39 PM, Jay Blanchard wrote:
>>
>> On Mar 1, 2012, at 6:36 PM, Jay Blanchard wrote:
>>
>>> [snip]…stuff…[/snip]
>>>
>>> I am getting close, but I am also getting frustrated. I probably need to
>>> walk away for a bit.
>>>
>>> I have an array of tiers….
>>>
>>> Array
>>> (
>>> [0] => TIER1DATA
>>> [1] => TIER2DATA
>>> [2] => TIER3DATA
>>> [3] => BUSTIER1DATA
>>> [4] => BUSTIER2DATA
>>> [5] => BUSTIER3DATA
>>> [6] => BUSTIER4DATA
>>> )
>>> Each of the tiers which represent parents in children in pairs. TIER1DATA
>>> is the parent of TIER2DATA, etc. representing 7 columns of data. Here is my
>>> code so far - any insight would be valuable and extremely appreciated by me.
>>>
>>> function display_children($i, $child) {
>>> global $dbc;
>>> global $tierArray;
>>> echo $i."<br />";
>>> /* retrieve children of parent */
>>> $get = "SELECT DISTINCT `". $tierArray[$i] ."` FROM `POSITION_SETUP` ";
>>> $get .= "WHERE `COMPANY_ID` = '3' ";
>>> if($i> 0) {
>>> $get .= "AND `" . $tierArray[$i - 1] . "` = '" . $child . "' ";
>>> }
>>> echo $get."<br />";
>>> if(!($result = mysql_query($get, $dbc))) {
>>> echo mysql_errno() . "\t" . mysql_error() . "\n";
>>> exit();
>>> }
>>>
>>> while($row = mysql_fetch_array($result)) {
>>>
>>> /* indent and display the title of this child */
>>> echo str_repeat(' ',$i).$row[$tierArray[$i]]."<br />\n";
>>>
>>> /* get the children's children */
>>> display_children($i + 1, $row[$tierArray[$i]]);
>>> }
>>> }
>>>
>>> It does produce the right query for only the first child in each case, I
>>> cannot get it to go deeper. I have just been looking at it for too long.
>>> TVMIA!
>>
>> Forgot to say that if I isolate any one level without recursion I get all of
>> the results for that level.
>>
>>
>
> How are you calling this? What are your initial arguments?
>
> What does your DB schema look like?
>
> Can you show a brief example of the data in the table?
> Dump: SELECT * FROM position_setup WHERE company_id='3'
>
> Can you show the output of the function above?
I initialize it by calling the function like this:
display_children(0, 0)
Each of the tiers is located in adjacent columns in a single table
tier1data, tier2data, tier 3 data……bustier1data, bustier2data…..
The array retrieves only the relevant tiers for this company and that is the
output of that array at the top of my example.
--- End Message ---
--- Begin Message ---
> [snip]
> Can you show the output of the function above?
> [/snip]
0
SELECT DISTINCT `TIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
Executives and Management
Normally this query alone returns 9 rows of data. Each of these rows should be
included in the next query where TIER1DATA = each of the nine in succession
1
SELECT DISTINCT `TIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND
`TIER1DATA` = 'Executives and Management'
Executives and ManagementLeadership
2
SELECT DISTINCT `TIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND
`TIER2DATA` = 'Executives and ManagementLeadership'
Executives and ManagementLeadershipManager
3
SELECT DISTINCT `BUSTIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
AND `TIER3DATA` = 'Executives and ManagementLeadershipManager'
Knee
4
SELECT DISTINCT `BUSTIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
AND `BUSTIER1DATA` = 'Knee'
KneeDIV01
5
SELECT DISTINCT `BUSTIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
AND `BUSTIER2DATA` = 'KneeDIV01'
KneeDIV01DEPT02
6
SELECT DISTINCT `BUSTIER4DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
AND `BUSTIER3DATA` = 'KneeDIV01DEPT02'
KneeDIV01DEPT02GRP04
7
SELECT DISTINCT `` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND
`BUSTIER4DATA` = 'KneeDIV01DEPT02GRP04'
1054 Unknown column '' in 'field list'
--- End Message ---
--- Begin Message ---
On 2012-03-01, at 9:20 PM, Jay Blanchard <jay.blanch...@sigmaphinothing.org>
wrote:
>> [snip]
>> Can you show the output of the function above?
>> [/snip]
>
> 0
> SELECT DISTINCT `TIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> Executives and Management
>
> Normally this query alone returns 9 rows of data. Each of these rows should
> be included in the next query where TIER1DATA = each of the nine in succession
>
> 1
> SELECT DISTINCT `TIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `TIER1DATA` = 'Executives and Management'
> Executives and ManagementLeadership
> 2
> SELECT DISTINCT `TIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `TIER2DATA` = 'Executives and ManagementLeadership'
> Executives and ManagementLeadershipManager
> 3
> SELECT DISTINCT `BUSTIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `TIER3DATA` = 'Executives and ManagementLeadershipManager'
> Knee
> 4
> SELECT DISTINCT `BUSTIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `BUSTIER1DATA` = 'Knee'
> KneeDIV01
> 5
> SELECT DISTINCT `BUSTIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `BUSTIER2DATA` = 'KneeDIV01'
> KneeDIV01DEPT02
> 6
> SELECT DISTINCT `BUSTIER4DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3'
> AND `BUSTIER3DATA` = 'KneeDIV01DEPT02'
> KneeDIV01DEPT02GRP04
> 7
> SELECT DISTINCT `` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND
> `BUSTIER4DATA` = 'KneeDIV01DEPT02GRP04'
> 1054 Unknown column '' in 'field list'
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
My usual approach to a problem like this to to includes a parent column in the
table
ID (int pk)
Parent ( default null ) // no parent
Item
Itemtype
[etc]
Parent will then hold either a null if a top level item, or a structured path (
1/10/24 ) that notes the parents of the item all the way up to the parent. That
way, a single query will get you all items in that parent's lineage to whatever
depth is needed by using the child's value
Select * from table where parent = '1/10'
Would retrieve all items that are children of a top level of 1 and a second
level of 10
Hth
Bastien
--- End Message ---
--- Begin Message ---
Hello!
The PHP Development Team would like to announce the immediate
availability of PHP 5.4.0. This release is a major leap forward in the
5.x series, and includes a large number of new features and bug fixes.
Release Announcement: http://www.php.net/releases/5_4_0.php
Downloads: http://php.net/downloads.php#v5.4.0
ChangeLog: http://www.php.net/ChangeLog-5.php#5.4.0
regards,
Stas and David
--- End Message ---
