Good day. There is a base with structure

CREATE TABLE `catalog_city` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(8) NOT NULL DEFAULT '0', `name` varchar(250) NOT NULL DEFAULT '', `level` tinyint(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; INSERT INTO `catalog_city` VALUES ('1', '0', 'Россия', '0'); INSERT INTO `catalog_city` VALUES ('2', '0', 'Беларусь', '0'); INSERT INTO `catalog_city` VALUES ('3', '2', 'Минск2', '0'); INSERT INTO `catalog_city` VALUES ('4', '1', 'Минск2', '0');

In fact, the number of entries is measured by a thousand, and the level of attachment is not known. It is required to affix the level of attachment to the level column (from 0 to the last nesting). How to do it in a loop?

    2 answers 2

    I see such an algorithm:

    1. Level = 0
    2. Parent array = [0]
    3. Assign level to Level column for parent_id IN Array of parents
    4. Get all id values ​​for parent_id IN Array of parents
    5. Level + 1
    6. Parent array = id array of 4 items
    7. If the Parent Array is not empty go to point 3

       function update_level($id) { global $db, $tabl_prefix, $city_i_down; if($id > 0) { $wh = "id = '{$id}'"; } else { $db->query("UPDATE `" . $tabl_prefix . "_city` SET level = '1'"); $wh = "parent_id > '0'"; } $result = $db->query ( "SELECT level, id, parent_id FROM `" . $tabl_prefix . "_city` WHERE {$wh}" ); while ( $row = $db->get_row ( $result ) ) { $suffix = 1; $parent_id = $row['parent_id']; if ( $parent_id > 0 ) { do { $suffix++; $parent_id = $city_i_down[$parent_id] ['parent_id']; } while ( $parent_id > 0 ); } $db->query("UPDATE `" . $tabl_prefix . "_city` SET level = '{$suffix}' WHERE id = '{$row['id']}'"); } }

      Here is the finished function.

      • $id - input ID (if specified)
      • $db - class for query execution (mysqli class)
      • $tabl_prefix is just a prefix to the table. I see.
      • $city_i_down is a multidimensional array containing data from the entire table. It is formed like this.

       $city_i_down = array (); $result = $db->query ( "SELECT * FROM `" . $tabl_prefix . "_city`); while ( $row = $db->get_row ( $result ) ) { $city_i_down [$row ['id']] = array (); foreach ( $row as $key => $value ) $city_i_down [$row ['id']] [$key] = $value; }

      • Without specifics on global variables, this is not a finished function. Well, answering a footcloth code without comment is a bad practice. - Dmitriy Simushev
      • He made explanations in response - Pavel Zhukovsky