////////////// ///// Database design and example of unlimited classification ////////////// Mysql> create database db_kind; Query OK, 1 row affected Mysql> use db_kind; Database changed Mysql> create table tb_kind ( -> Id int not null auto_increment primary key, -> Pid int, -> Path varchar (200) -> ); Query OK, 0 rows affected Mysql> insert into tb_kind values (null, "news", 0, 0 ); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "video", 0, 0 ); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "image", 0, 0 ); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "blog", 0, 0 ); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "Sports News", 1, "0-1 "); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "Entertainment News", 1, "0-1 "); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "Financial News", 1, "0-1 "); Query OK, 1 row affected Mysql> select * from db_kind; ERROR 1146: Table 'DB _ kind. db_kind 'doesnot exist Mysql> select * from tb _ Kind; + ---- + ---------- + ----- + ------ + | Id | pname | pid | path | + ---- + ---------- + ----- + ------ + | 1 | news | 0 | 0 | | 2 | video | 0 | 0 | | 3 | Image | 0 | 0 | | 4 | blog | 0 | 0 | | 5 | sports news | 1 | 0-1 | | 6 | entertainment news | 1 | 0-1 | | 7 | financial news | 1 | 0-1 | + ---- + ---------- + ----- + ------ + 7 rows in set Mysql> insert into tb_kind values (null, "Basketball News", 5, "0-1-5 "); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "Football News", 5, "0-1-5 "); Query OK, 1 row affected Mysql> select * from tb_kind; + ---- + ---------- + ----- + ------- + | Id | pname | pid | path | + ---- + ---------- + ----- + ------- + | 1 | news | 0 | 0 | | 2 | video | 0 | 0 | | 3 | Image | 0 | 0 | | 4 | blog | 0 | 0 | | 5 | sports news | 1 | 0-1 | | 6 | entertainment news | 1 | 0-1 | | 7 | financial news | 1 | 0-1 | | 8 | basketball news | 5 | 0-1-5 | | 9 | Football News | 5 | 0-1-5 | + ---- + ---------- + ----- + ------- + 9 rows in set Mysql> insert into tb_kind values (null, "NBA", 8, "0-1-5-8 "); Query OK, 1 row affected Mysql> insert into tb_kind values (null, "CBA", 8, "0-1-5-8 "); Query OK, 1 row affected Mysql> select * from tb_kind; + ---- + ---------- + ----- + --------- + | Id | pname | pid | path | + ---- + ---------- + ----- + --------- + | 1 | news | 0 | 0 | | 2 | video | 0 | 0 | | 3 | Image | 0 | 0 | | 4 | blog | 0 | 0 | | 5 | sports news | 1 | 0-1 | | 6 | entertainment news | 1 | 0-1 | | 7 | financial news | 1 | 0-1 | | 8 | basketball news | 5 | 0-1-5 | | 9 | Football News | 5 | 0-1-5 | | 10 | NBA | 8 | 0-1-5-8 | | 11 | CBA | 8 | 0-1-5-8 | + ---- + ---------- + ----- + --------- + 11 rows in set Mysql> select concat (path, "-", id) from tb_kind; + --------------------- + | Concat (path, "-", id) | + --------------------- + | 0-1 | | 0-2 | | 0-3 | | 0-4 | | 0-1-5 | | 0-1-6 | | 0-1-7 | | 0-1-5-8 | | 0-1-5-9 | | 0-1-5-8-10 | | 0-1-5-8-11 | + --------------------- + 11 rows in set Mysql> select concat (path, "-", id) from tb_kind; + --------------------- + | Concat (path, "-", id) | + --------------------- + | 0-1 | | 0-2 | | 0-3 | | 0-4 | | 0-1-5 | | 0-1-6 | | 0-1-7 | | 0-1-5-8 | | 0-1-5-9 | | 0-1-5-8-10 | | 0-1-5-8-11 | + --------------------- + 11 rows in set Mysql> select concat (path, "-", id) as abs from tb_kind order by abs. path; ERROR 1054: Unknown column 'abs. path' in 'Order clause' Mysql> select concat (path, "-", id) as abs from tb_kind order by abs + ------------ + | Abs | + ------------ + | 0-1 | | 0-1-5 | | 0-1-5-8 | | 0-1-5-8-10 | | 0-1-5-8-11 | | 0-1-5-9 | | 0-1-6 | | 0-1-7 | | 0-2 | | 0-3 | | 0-4 | + ------------ + 11 rows in set Mysql> select concat (path, "-", id) as, id, name, path abs from tb_kind order by abs; ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id, name, path abs from tb_kind order by abs 'at line 1 Mysql> select concat (path, "-", id) as abs, Id, pname, path abs from tb_kind order by abs; + ------------ + ---- + ---------- + --------- + | Abs | id | pname | abs | + ------------ + ---- + ---------- + --------- + | 0-1 | 1 | news | 0 | | 0-1-5 | 5 | sports news | 0-1 | | 0-1-5-8 | 8 | basketball news | 0-1-5 | | 0-1-5-8-10 | 10 | NBA | 0-1-5-8 | | 0-1-5-8-11 | 11 | CBA | 0-1-5-8 | | 0-1-5-9 | 9 | Football News | 0-1-5 | | 0-1-6 | 6 | entertainment news | 0-1 | | 0-1-7 | 7 | financial news | 0-1 | | 0-2 | 2 | video | 0 | | 0-3 | 3 | Image | 0 | | 0-4 | 4 | blog | 0 | + ------------ + ---- + ---------- + --------- + 11 rows in set Mysql> Php classification source code Untitled Document $ Conn = mysql_connect ("localhost", "root", "root "); Mysql_select_db ("db_kind "); Mysql_query ("set names utf8 "); $ SQL = "select concat (path, '-', id) as abspath, id, pname, path from tb_kind order by abspath "; $ Rs = mysql_query ($ SQL ); While ($ result = mysql_fetch_assoc ($ rs )){ $ Num = count (explode ("-", $ result [path])-1; $ New_str = str_repeat ("---", $ num ); Echo $ new_str. $ result [pname]; Echo" "; } $ Str = str_repeat ("=", 10 ); Echo $ str; $ Num = count (explode ("-", "0-1-5-8")-1; Echo $ num; ?>
|