|
|
Posted by "Richard Lynch" on 11/26/00 11:16
On Wed, May 18, 2005 5:04 pm, Charles Kline said:
> I have an organization table in mySQL. Pretty standard. My first
> function getDepts() is working as I intend and returning the tree
> just as I like it. The new piece I added in there that is not working
> now is the call to getPositions() within the first function. What I
> am trying to do is once I get a Department, I want to loop through
> the Positions table and get the positions that are under that
> Department. This code goes haywire and loops for ever printing a huge
> list to the screen. Eventually I need to return this data not to the
> screen but into an array. Anyone see what I might have wrong in my
> logic?
What is in the huge list?...
Actually...
If your organization and/or its budget positions is at all large, this is
an incredibly inefficient way to do it.
You're hammering your database with query after query, and you are using
PHP to do all the iteration and ordering.
SQL was *designed* to handle large amounts of data efficiently.
You would be better served with something not unlike:
$query = "select boOrgId, boOrgName, bpPositionID ";
$query .= " from BudgetedOrganization, BudgetedPosition ";
$query .= " where BudgetedOrganization.boOrgID =
BudgetedPosition.bp_boOrgID ";
$query .= " order by boSuperiorOrgID ";
$rs = $this->retrieveData($query);
if ($rs){
while ($row = mysql_fetch_array($rs)){
//Store $row in your array or whatever you want to do with it.
}
}
> I have a class and it contains these two functions.
>
> function getDepts ( $parent = 0, $level = 1 ) {
> $sql = 'SELECT BudgetedOrganization.* ';
> $sql .= 'FROM BudgetedOrganization ';
> $sql .= 'WHERE BudgetedOrganization.boSuperiorOrgID = ' .
> $parent;
>
> $rs = $this->retrieveData($sql);
> if ($rs)
> {
> while($row = mysql_fetch_array($rs)){
> $num = 1;
>
> while ($num <= $level) {
> $this->str .= "\t";
> $num++;
> }
> $this->str .= $row['boOrgID'] . ", " . $row
> ['boOrgName'] . "\n";
>
> // just added this and it ain't working
> $this->str .= $this->getPositions($row['boOrgID']);
> $this->getDepts($row['boOrgID'],$level+1);
> }
> }
> return($this->str);
> }
>
> function getPositions ( $org = 0 ) {
> $sql = 'SELECT BudgetedPosition.* ';
> $sql .= 'FROM BudgetedPosition ';
> $sql .= 'WHERE BudgetedPosition.bp_boOrgID = ' . $org;
> //echo $sql;
> $rs = $this->retrieveData($sql);
> if ($rs)
> {
> while($row = mysql_fetch_array($rs)){
> $this->str .= " - " . $row['bpPositionID'] . "\n";
> }
> }
> return($this->str);
> }
>
>
> Later....
>
> $depts = $org->getDepts();
> echo "<pre>" . $depts . "</pre>";
>
>
> Thanks,
> Charles
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|