The example in this article describes the method of realizing unlimited classification in php mysql. Share it with everyone for your reference. The specific analysis is as follows:
1. The database performs unique indexing by setting the parent class ID, and then uses recursive calls of functions to achieve unlimited classification;
2. The database design is arranged in a specific format, and then uses mysql to query the key function: concat. The program implementation is relatively simple. First, we assume that there is such a three-level classification, News→PHP News→PHP6.0 is out.
If we want to find the news "PHP6.0 is out", we can click on the news first, and then click on the PHP news to find out. In other words, we can go down level by level through the grandfather class. In turn, as long as we know the parent class of a subclass, we can find it. In this way, when designing the database, we can design an additional field for the parent class id to achieve unlimited classification.
The database code is as follows:
Here we create a table "class"
The code is as follows:
`id` int(11) NOT NULL auto_increment COMMENT 'category id',
`f_id` int(11) NOT NULL COMMENT 'parent id',
`name` varchar(25) collate gbk_bin NOT NULL COMMENT 'Category name',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk COLLATE=gbk_bin AUTO_INCREMENT=1 ;
First, we insert the "News" category into the database. Because "News" is the largest category and there is no parent category on it, so I set its f_id to 0.
The code is as follows:
Then we insert the category 'PHP News' into the database. The id of its parent category 'News' is 1, so its f_id is set to 1.
The code is as follows:
Then we insert the category 'PHP 6.0 is out' into the database. The id of its parent category 'PHP News' is 2, so its f_id is set to 2.
The code is as follows:
Similarly, we can insert categories all the way down, thus reaching infinite categories.
We can find that the key to inserting a category is to find the id of the parent category of this category, and then use it as the value of the f_id field of this category.
Suppose you want to insert the category 'Technology' at the same level as 'News', that is to say it is also the largest category and there is no parent category above it, then its f_id is also set to 0;
Copy the code The code is as follows:
There is another category 'PHP Technology' under 'Technology', so how do we insert it? First find the id of the parent class 'Technology' of 'PHP Technology', and then use it as the value of its own f_id field.
The code is as follows:
Seeing this, everyone should understand how to insert each category into the database, so I won’t give examples. We already know how to insert each category into the database, so how do we list each category?
The code is as follows:
$db=new mysqli("localhost","root","","news_php100"); //Instantiate a database connection. Before using this, make sure that the mysqli class library has been loaded, or connect using mysql_connect.
if(mysqli_connect_errno()){
echo "Link failed:".mysqli_connect_error();
exit(); }
$db->query("set names utf8");
$result=$db->query("select name from class where f_id=0"); //Find the category with f_id=0, that is, find each major category.
while($row=$result->fetch_assoc()){
echo $row['name']."
"; //This will loop out each major category.
}
//Similarly we can loop out the subcategories of news.
$result=$db->query("select * from class where f_id=1"); //Find the category of f_id=1, that is, find the subcategory of 'News'.
while($row=$result->fetch_assoc()){
echo $row['name']."
"; //This loops out the subclasses of 'News'. Note: only subclasses, excluding grandchild classes.
}
//Writing here, we will find a problem. If this classification is a 10-level classification, do we have to write 10 loops to cycle out each of its subcategories? If there are more levels of classification, it is obviously unrealistic to write like this.
//Then what is the solution? We can write a recursive function, pass in f_id as a parameter, and continuously loop through the value of each f_id, that is to say, loop out the subclass of each f_id value.
//First we save the values of each category in a two-dimensional array, which is useful in the following recursive function.
$result=$db->query("select * from class");
while($row=$result->fetch_assoc()){
$arr[]=array($row[id],$row[f_id],$row[name]); //Each row saves the information of a category's id, f_id, and name.
}
function fenlei($f_id=0){ //$f_id is initialized to 0, that is, the cycle starts from the maximum classification.
global $arr; //Declare $arr as a global variable before it can be referenced in the function.
for($i=0;$i
echo $arr[$i][2]."
"; //$arr[$i][1] represents the value of the name of the $i-th category.
fenlei($arr[$i][0]); //$arr[$i][1] represents the value of the id of the $i-th category. Perform recursion, that is, use your own id as the f_id parameter to recycle your own subclasses.
}
}
}
?>
The three fields id, parentid, name, the algorithm is also very simple and recursive. In the past, it was very stupid when using recursion, I should say extremely stupid, because in recursion, all the subclasses were obtained by querying the data table. Recently, I got the idea and thought of a The method that everyone on earth can think of, the following is the code, a class, the code is as follows:
Copy the code The code is as follows:
/**
* All classification information queried from the database
* @var array
*/
var $arr;
/**
* The following format
* var $arr = array(
1 => array('id'=>'1','parentid'=>0,'name'=>'First-level column one'),
2 => array('id'=>'2','parentid'=>0,'name'=>'First-level column two'),
3 => array('id'=>'3','parentid'=>1,'name'=>'Second-level column one'),
);*/
/**
* Output structure
* @var array
*/
var $tree = array();
/**
* Depth of tree recursion
* @var int
*/
var $deep = 1;
/**
* Generate tree-shaped modification symbols
* @var array
*/
var $icon = array('│','├','└');
/**
* Generate a subordinate tree structure of the specified id
* @param int $rootid To get the id of the tree structure
* @param string $add prefix used in recursion
* @param bool $parent_end identifies whether the parent category is the last one
*/
function getTree($rootid = 0,$add = ”,$parent_end =true){
$is_top = 1;
$child_arr = $this->getChild($rootid);
if(is_array($child_arr)){
$cnt = count($child_arr);
foreach($child_arr as $key => $child){
$cid = $child['id'];
$child_child = $this->getChild($cid);
if($this->deep >1){
if($is_top == 1 && $this->deep > 1){
$space = $this->icon[1];
if(!$parent_end)
$add .= $this->icon[0];
else $add .= ' ';
}
if($is_top == $cnt){
$space = $this->icon[2];
$parent_end = true;
}else {
$space = $this->icon[1];
$parent_end = false;
}
}
$this->tree[] = array('spacer'=>$add.$k.$space,
'name'=>$child['name'],
'id'=>$cid
);
$is_top ;
$this->deep ;
if($this->getChild($cid))
$this->getTree($cid,$add,$parent_end);
$this->deep–;
}
}
return $this->tree;
}
/**
* Get the lower-level classification array
* @param int $root
*/
function getChild($root = 0){
$a = $child = array();
foreach($this->arr as $id=>$a){
if($a['parentid'] == $root){
$child[$a['id']] = $a;
}
}
return $child?$child:false;
}
/**
* Set source array
* @param $arr
*/
function setArr($arr = array()){
$this->arr = $arr;
}
}
?>
通过一次查询把结构保存进一个数组,再数组进行递归运算,无疑极大的提高了程序运行效率,使用代码很简单.
希望本文所述对大家的php程序设计有所帮助。
