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...

Mike Miller · Accepted Answer · 2015-06-23 06:03:45Z

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

answered Jun 23, 2015 at 6:03

Mike Miller

3,1273 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andris Over a year ago

Do you mean to fetch from mysql all results. And then with php get necessary results? No other options? With mysql while not possible to do?

Mike Miller Over a year ago

There is always other options

Aaron Cicali · Accepted Answer · 2015-06-23 05:57:31Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related