Tree Traversal - how sort results?
2
I have simple table form which I'm gettting data hierarchically using Tree traversal.
It works nice, but now I need to add a new function. I need to sort data in each level by value saved for each row in new column in databes [rank].
My table is:
+----+-----+-----+-------+--------+------------+------+
| ID | LFT | RGT | DEPTH | PARENT | NAME | RANK |
+----+-----+-----+-------+--------+------------+------+
| 1 | 1 | 22 | 0 | 0 | Category | 10 |
| 2 | 2 | 15 | 1 | 1 | Fruit | 8 |
| 3 | 3 | 8 | 2 | 2 | Apples | 8 |
| 4 | 4 | 5 | 3 | 3 | Red | 5 |
| 5 | 6 | 7 | 3 | 3 | Green | 5 |
| 6 | 9 | 14 | 2 | 2 | Bannana | 9 |
| 7 | 10 | 11 | 3 | 6 | Long | 1 |
| 8 | 12 | 13 | 3 | 6 | Short | 2 |
| 9 | 16 | 21 | 1 | 0 | Vegetables | 9 |
| 10 | 17 | 18 | 2 | 9 | Carrots | 5 |
| 11 | 19 | 20 | 2 | 9 | Potatoes | 6 |
| 12 | 8 | 9 | 3 | 3 | Yellow | 10 |
+----+-----+-----+-------+--------+------------+------+
For list the structure I'm using following script:
$lftrgt = mysql_fetch_assoc(mysql_query("SELECT name,lft,rgt,rank FROM myTable WHERE id='$id'"));
$result = mysql_query("SELECT * FROM myTable WHERE rgt < $lftrgt[rgt] AND lft > $lftrgt[lft] ORDER BY lft");
if(!$result) { echo mysql_error() . ' - ' . mysql_errno(); }
$depth = -1;
echo "<b><u>".$lftrgt[name] ." (rank: ". $lftrgt[rank] .")</u></b>";
while ($row = mysql_fetch_assoc($result)) {
if ($depth < $row["depth"]) {
echo "<ul>";
} else {
echo str_repeat("</li></ul>", $depth - $row["depth"]) . "</li>";
}
echo "<li>n" . htmlspecialchars($row["name"]) ." (rank: " . $row[rank] . ")";
$depth = $row["depth"];
}
echo str_repeat("</li></ul>", $depth + 1) . "n";
mysql_free_result($result);
For better idea, there is picture what I need:
How to sort results in each level in descending order? Thanks.
php sorting structure
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
add a comment |
2
I have simple table form which I'm gettting data hierarchically using Tree traversal.
It works nice, but now I need to add a new function. I need to sort data in each level by value saved for each row in new column in databes [rank].
My table is:
+----+-----+-----+-------+--------+------------+------+
| ID | LFT | RGT | DEPTH | PARENT | NAME | RANK |
+----+-----+-----+-------+--------+------------+------+
| 1 | 1 | 22 | 0 | 0 | Category | 10 |
| 2 | 2 | 15 | 1 | 1 | Fruit | 8 |
| 3 | 3 | 8 | 2 | 2 | Apples | 8 |
| 4 | 4 | 5 | 3 | 3 | Red | 5 |
| 5 | 6 | 7 | 3 | 3 | Green | 5 |
| 6 | 9 | 14 | 2 | 2 | Bannana | 9 |
| 7 | 10 | 11 | 3 | 6 | Long | 1 |
| 8 | 12 | 13 | 3 | 6 | Short | 2 |
| 9 | 16 | 21 | 1 | 0 | Vegetables | 9 |
| 10 | 17 | 18 | 2 | 9 | Carrots | 5 |
| 11 | 19 | 20 | 2 | 9 | Potatoes | 6 |
| 12 | 8 | 9 | 3 | 3 | Yellow | 10 |
+----+-----+-----+-------+--------+------------+------+
For list the structure I'm using following script:
$lftrgt = mysql_fetch_assoc(mysql_query("SELECT name,lft,rgt,rank FROM myTable WHERE id='$id'"));
$result = mysql_query("SELECT * FROM myTable WHERE rgt < $lftrgt[rgt] AND lft > $lftrgt[lft] ORDER BY lft");
if(!$result) { echo mysql_error() . ' - ' . mysql_errno(); }
$depth = -1;
echo "<b><u>".$lftrgt[name] ." (rank: ". $lftrgt[rank] .")</u></b>";
while ($row = mysql_fetch_assoc($result)) {
if ($depth < $row["depth"]) {
echo "<ul>";
} else {
echo str_repeat("</li></ul>", $depth - $row["depth"]) . "</li>";
}
echo "<li>n" . htmlspecialchars($row["name"]) ." (rank: " . $row[rank] . ")";
$depth = $row["depth"];
}
echo str_repeat("</li></ul>", $depth + 1) . "n";
mysql_free_result($result);
For better idea, there is picture what I need:
How to sort results in each level in descending order? Thanks.
php sorting structure
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
1
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
Why are you using the long-deprecatedmysql_code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to usingmysqliorPDOas soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.
– ADyson
Nov 15 '18 at 14:58
add a comment |
2
2
2
I have simple table form which I'm gettting data hierarchically using Tree traversal.
It works nice, but now I need to add a new function. I need to sort data in each level by value saved for each row in new column in databes [rank].
My table is:
+----+-----+-----+-------+--------+------------+------+
| ID | LFT | RGT | DEPTH | PARENT | NAME | RANK |
+----+-----+-----+-------+--------+------------+------+
| 1 | 1 | 22 | 0 | 0 | Category | 10 |
| 2 | 2 | 15 | 1 | 1 | Fruit | 8 |
| 3 | 3 | 8 | 2 | 2 | Apples | 8 |
| 4 | 4 | 5 | 3 | 3 | Red | 5 |
| 5 | 6 | 7 | 3 | 3 | Green | 5 |
| 6 | 9 | 14 | 2 | 2 | Bannana | 9 |
| 7 | 10 | 11 | 3 | 6 | Long | 1 |
| 8 | 12 | 13 | 3 | 6 | Short | 2 |
| 9 | 16 | 21 | 1 | 0 | Vegetables | 9 |
| 10 | 17 | 18 | 2 | 9 | Carrots | 5 |
| 11 | 19 | 20 | 2 | 9 | Potatoes | 6 |
| 12 | 8 | 9 | 3 | 3 | Yellow | 10 |
+----+-----+-----+-------+--------+------------+------+
For list the structure I'm using following script:
$lftrgt = mysql_fetch_assoc(mysql_query("SELECT name,lft,rgt,rank FROM myTable WHERE id='$id'"));
$result = mysql_query("SELECT * FROM myTable WHERE rgt < $lftrgt[rgt] AND lft > $lftrgt[lft] ORDER BY lft");
if(!$result) { echo mysql_error() . ' - ' . mysql_errno(); }
$depth = -1;
echo "<b><u>".$lftrgt[name] ." (rank: ". $lftrgt[rank] .")</u></b>";
while ($row = mysql_fetch_assoc($result)) {
if ($depth < $row["depth"]) {
echo "<ul>";
} else {
echo str_repeat("</li></ul>", $depth - $row["depth"]) . "</li>";
}
echo "<li>n" . htmlspecialchars($row["name"]) ." (rank: " . $row[rank] . ")";
$depth = $row["depth"];
}
echo str_repeat("</li></ul>", $depth + 1) . "n";
mysql_free_result($result);
For better idea, there is picture what I need:
How to sort results in each level in descending order? Thanks.
php sorting structure
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
I have simple table form which I'm gettting data hierarchically using Tree traversal.
It works nice, but now I need to add a new function. I need to sort data in each level by value saved for each row in new column in databes [rank].
My table is:
+----+-----+-----+-------+--------+------------+------+
| ID | LFT | RGT | DEPTH | PARENT | NAME | RANK |
+----+-----+-----+-------+--------+------------+------+
| 1 | 1 | 22 | 0 | 0 | Category | 10 |
| 2 | 2 | 15 | 1 | 1 | Fruit | 8 |
| 3 | 3 | 8 | 2 | 2 | Apples | 8 |
| 4 | 4 | 5 | 3 | 3 | Red | 5 |
| 5 | 6 | 7 | 3 | 3 | Green | 5 |
| 6 | 9 | 14 | 2 | 2 | Bannana | 9 |
| 7 | 10 | 11 | 3 | 6 | Long | 1 |
| 8 | 12 | 13 | 3 | 6 | Short | 2 |
| 9 | 16 | 21 | 1 | 0 | Vegetables | 9 |
| 10 | 17 | 18 | 2 | 9 | Carrots | 5 |
| 11 | 19 | 20 | 2 | 9 | Potatoes | 6 |
| 12 | 8 | 9 | 3 | 3 | Yellow | 10 |
+----+-----+-----+-------+--------+------------+------+
For list the structure I'm using following script:
$lftrgt = mysql_fetch_assoc(mysql_query("SELECT name,lft,rgt,rank FROM myTable WHERE id='$id'"));
$result = mysql_query("SELECT * FROM myTable WHERE rgt < $lftrgt[rgt] AND lft > $lftrgt[lft] ORDER BY lft");
if(!$result) { echo mysql_error() . ' - ' . mysql_errno(); }
$depth = -1;
echo "<b><u>".$lftrgt[name] ." (rank: ". $lftrgt[rank] .")</u></b>";
while ($row = mysql_fetch_assoc($result)) {
if ($depth < $row["depth"]) {
echo "<ul>";
} else {
echo str_repeat("</li></ul>", $depth - $row["depth"]) . "</li>";
}
echo "<li>n" . htmlspecialchars($row["name"]) ." (rank: " . $row[rank] . ")";
$depth = $row["depth"];
}
echo str_repeat("</li></ul>", $depth + 1) . "n";
mysql_free_result($result);
For better idea, there is picture what I need:
How to sort results in each level in descending order? Thanks.
php sorting structure
php sorting structure
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
edited Nov 15 '18 at 14:38
Nic3500
3,35081829
3,35081829
asked Nov 15 '18 at 14:05
Johann GJohann G
233
asked Nov 15 '18 at 14:05
Johann GJohann G
233
asked Nov 15 '18 at 14:05
Johann GJohann G
233
233
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
1
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
Why are you using the long-deprecatedmysql_code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to usingmysqliorPDOas soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.
– ADyson
Nov 15 '18 at 14:58
add a comment |
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
1
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
Why are you using the long-deprecatedmysql_code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to usingmysqliorPDOas soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.
– ADyson
Nov 15 '18 at 14:58
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
1
1
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
Why are you using the long-deprecated
mysql_ code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to using mysqli or PDO as soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.– ADyson
Nov 15 '18 at 14:58
Why are you using the long-deprecated
mysql_ code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to using mysqli or PDO as soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.– ADyson
Nov 15 '18 at 14:58
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321232%2ftree-traversal-how-sort-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321232%2ftree-traversal-how-sort-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You could read your data into an array and use php.net/manual/en/function.array-multisort.php to sort it.
– Nic3500
Nov 15 '18 at 14:39
1
As a first step, you should put the data into a data structure, which you then can operate on.
– Adder
Nov 15 '18 at 14:40
Why are you using the long-deprecated
mysql_code library? It was discontinued many years ago and removed entirely in PHP7. No new code should be written using this library. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to usingmysqliorPDOas soon as possible, and then learn how to write parameterised queries to protect your data from malicious input. See bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely.– ADyson
Nov 15 '18 at 14:58