I have the following table which creates my links for a hierarchical menu.
mysql> SELECT page_id, page_parent, page_ordernumber FROM tablePages ORDER BY page_parent, page_ordernumber;
+---------+-------------+------------------+
| page_id | page_parent | page_ordernumber |
+---------+-------------+------------------+
| 1 | 0 | NULL |
| 2 | 1 | 1 |
| 7 | 1 | 2 |
| 11 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 8 | 7 | 1 |
| 9 | 7 | 2 |
| 10 | 7 | 3 |
| 12 | 4 | 1 |
+---------+-------------+------------------+
I am trying to figure out how to code for this now. For example, if I select result from page_id #12, then my menu will show:
As first selection: page link to page_id #4 because it is the parent of #12
As second selection: page link to page_id #2 because it is the parent of #4.
As third selection: page link to page_id #1 because it is the parent of #2
I am have great difficulty in trying to figure out the PHP code for this. I believe that in order to build out this menu, it should require 1 query and 1 loop, however I just can’t figure it out. The only way I can think of (which I know is wrong in EVERY possible way) is this to cycle through 3 separate queries (since I don’t plan to have more than 3 parent/child associations):
// First Check
if (isset($user_selection)) {
$sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_selection]);
$result = $stmt->fetch();
$option1 = $result['page_parent'];
}
// Second Check
if (isset($option1) && $option1 != 1) {
$sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$option1]);
$result = $stmt->fetch();
$option2 = $result['page_parent'];
}
// Third Check
if (isset($option2) && $option2 != 1) {
$sql="SELECT page_id, page_parent, page_ordernumber FROM tablePages WHERE page_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$option2]);
$result = $stmt->fetch();
$option3 = $result['page_parent'];
}
Any help or guidance anyone can provide on this would be greatly appreciated.
(Note: I know the page_ordernumber column is going to annoy seasoned coders. Right now, I want it to work with this column. I am learning in stages. Once I can get this working, then I will focus on better ways to manage the nodes as mentioned here (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/). I only have surface level understanding of the methods described there, and it’s looking like this part may be beyond my mental capabilities.)