Instead of php while loop and multiple connections to mysql want to connect only once

Question

Example of mysql is here http://sqlfiddle.com/#!9/68653/2

Mysql table (named topics)

TopicId | TopicName         | ClosestUpperLevelId
--------------------------------------------
  1     | Books             | 0
  2     | BooksAboutCss     | 1
  3     | BooksAboutHtml    | 1
  4     | BooksAboutCss1    | 2
  5     | BooksAboutCss2    | 2
  6     | BooksAboutHtml1   | 3
  7     | BooksAboutHtml2   | 3
  8     | E-Books           | 0
  9     | Magazines         | 0

For top level topics ClosestUpperLevelId is 0. For subtopics ClosestUpperLevelId is TopicId of closest upper level topic

      (TopicId - ClosestUpperLevelId) 
Books (1-0) 
  BooksAboutCss (2-1)
    BooksAboutCss1 (4-2)
    BooksAboutCss2 (5-2)
  BooksAboutHtml (3-1)        
    BooksAboutHtml1 (6-3)
    BooksAboutHtml2 (7-3)
E-Books (8-0) 
Magazines (9-0) 

For example, i have created one page and location of the page is domain.com/Books/BooksAboutCss/BooksAboutCss2

Now i decided to edit the page. For example i want to edit location (topic or category) of the page and set it to domain.com/Magazines. So i need to fetch all topics, related with existing (saved) page. Then will create select boxes for each group (level) of topics.

At the moment tried to use php while and multiple times to connect to mysql and get data. Like below code. How can i get the same result without php while? How connect to mysql only once and fetch all necessary data? Do i need to use mysql while https://dev.mysql.com/doc/refman/5.1/en/while.html?

$topic_names_1[0]['UpperLevelNumberRenamed'] = 5;//just set some value to start to fetch

while ( trim($topic_names_1[0]['ClosestUpperLevelId']) != 0 ){

try {

$stmt_1 = $db->prepare('
SELECT `TopicId`, `TopicName`, `ClosestUpperLevelId` 
FROM `topics` 

WHERE `ClosestUpperLevelId` = 
(
SELECT `ClosestUpperLevelId` 
FROM `topics` 
WHERE `TopicId` = ?
) 

;');

$stmt_1->execute( array( trim($topic_names_1[0]['UpperLevelNumberRenamed']) ) );

$topic_names_1 = $stmt_1->fetchAll(PDO::FETCH_ASSOC);
echo '<pre>', print_r($topic_names_1), ' topic_names_1 __</pre>';

}//try {
catch (PDOException $e){
echo "<br> stmt_1 DataBase Error: " .htmlspecialchars( $e->getMessage() , ENT_QUOTES, "UTF-8").'<br>';
}
catch (Exception $e) {
echo " stmt_1 General Error: ".htmlspecialchars( $e->getMessage() ).'<br>';
}

}//while ( trim($topic_names[0]['UpperLevelNumberRenamed']) != 0 )

As result get arrays like this

Array
(
    [0] => Array
        (
            [TopicId] => 4
            [TopicName] => BooksAboutCss1
            [ClosestUpperLevelId] => 2
         )
    [1] => Array
        (
            [TopicId] => 5
            [TopicName] => BooksAboutCss2
            [ClosestUpperLevelId] => 2
         )
)
1 topic_names_1 __

Array
(
    [0] => Array
        (
            [TopicId] => 2
            [TopicName] => BooksAboutCss
            [ClosestUpperLevelId] => 1
         )
    [1] => Array
        (
            [TopicId] => 3
            [TopicName] => BooksAboutHtml
            [ClosestUpperLevelId] => 1
         )
)
1 topic_names_1 __


Array
(
    [0] => Array
        (
            [TopicId] => 1
            [TopicName] => Books
            [ClosestUpperLevelId] => 0
         )
    [1] => Array
        (
            [TopicId] => 8
            [TopicName] => E-Books
            [ClosestUpperLevelId] => 0
         )
    [2] => Array
        (
            [TopicId] => 9
            [TopicName] => Magazines
            [ClosestUpperLevelId] => 0
         )           
)
1 topic_names_1 __

Update

Found one example with mysql while. Trying to create own code

Created this

SET `ClosestUpperLevelId` := 2;
WHILE `ClosestUpperLevelId` > 0 
DO
SELECT `TopicId`, `TopicName`, `ClosestUpperLevelId`;
END WHILE;

and this (http://sqlfiddle.com/#!9/68653/7)

CREATE PROCEDURE dowhile()
BEGIN
DECLARE `ClosestUpperLevelId` INT DEFAULT 2;
WHILE `ClosestUpperLevelId` > 0 DO
SELECT `TopicId`, `TopicName`, `ClosestUpperLevelId`;
END WHILE;
END;

Got error...

Answer 1

You could just move your select statement before the while loop and take out the topic_id from the where clause. Then in your loop retrieve from the complete record set using an array search function or similar. By running same query once your dB will be able to cache results

Answer 2

Relational databases like MySQL aren't naturally good at storing hierarchical data, but there are ways of doing it. The two ways I know of are "adjacency lists" and "nested sets".

For an adjacency list, you'd simply store a "parent_id" field for each row. Root rows (that have no parent) can just have NULL for their parent_id field. Adjacency lists are easy to manage, but not very flexible and require recursive queries to find a path from root to leaf (unless you're only going 2 levels deep, then you can simply JOIN).

Here's an example:

id | parent_id      | name
0    NULL             grandfather
1    0                father
2    1                grandson
3    1                granddaughter

This query would help assemble the data:

SELECT * FROM
    `people` AS p1
    JOIN `people` p2
        ON p1.id = p2.parent_id

Any more than those two levels and you need recursion. Alternatively, you could just query the entire table and assemble it in code.

---

Nested sets are a little more complicated, but allow you to easily query all the way up the tree for a given leaf node. It's much easier to understand nested sets at first by seeing a visual, check this out:

https://en.wikipedia.org/wiki/Nested_set_model

And here's what your schema would look like:

left | right | name
0      7       grandfather
1      6       father
2      3       grandson
4      5       granddaughter

And here's an example to fetch father and children:

SELECT *
    FROM `people`
    WHERE `left` >= 1 AND `right` <= 6

Nested sets have the downside that the entire table's left and right values need to be updated when the hierarchy changes.

Google "managing hierarchical data in mysql" for more information. I hope this helps.