存储过程无限分类

最新推荐文章于 2024-11-02 16:28:26 发布
weixin_34088583
最新推荐文章于 2024-11-02 16:28:26 发布
阅读量78
点赞数
文章标签： python php 数据库
原文链接：https://my.oschina.net/tianranqi/blog/181069
版权
2019独角兽企业重金招聘Python工程师标准>>>
<?php
define('CLIENT_MULTI_RESULTS', 131072);
$host='127.0.0.1';
$dbname = 'test';
$dbuser = 'root';
$dbpswd = '123456';
$conn = mysql_connect($host,$dbuser,$dbpswd,1,CLIENT_MULTI_RESULTS) or die('数据库链接失败：'.mysql_error());
mysql_select_db($dbname);
mysql_query("SET NAMES UTF8");    
// mysql_query("DROP TABLE IF EXISTS `category`;",$conn);
// $create_tables_sql = "CREATE TABLE IF NOT EXISTS `category`(
//                       `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
//                       `name` char(10) NOT NULL COMMENT '分类名称',
//                       `parentid` tinyint(4) NOT NULL COMMENT '父级ID',
//                       `lft` tinyint(4) NOT NULL COMMENT '左',
//                       `rgt` tinyint(4) NOT NULL COMMENT '右',
//                       `level` tinyint(3) NOT NULL DEFAULT '0' COMMENT '层级',
//                       PRIMARY KEY (`id`),
//                       KEY `parentid` (`parentid`,`lft`,`rft`)
//                       ) ENGINE = Innodb DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;";

// $insert_sql = "INSERT INTO `category` (`name`,`parentid`,`lft`,`rft`,`level`) VALUES('顶级',0,1,2,0);";
// mysql_query($create_tables_sql,$conn) or die("invalid query:".mysql_error());
// mysql_query($insert_sql,$conn) or die("invalid query1:".mysql_error());
// $row = mysql_affected_rows();
// echo $row;

//添加节点
//mysql_query("DROP PROCEDURE IF EXISTS `addCategory`;");
$creatre_pro_sql = "CREATE PROCEDURE `addCategory`(in pid INT,in name varchar(20))
                     BEGIN
                        DECLARE rightval INT;/*右值*/
                        DECLARE levelval INT;/*层级*/
                        DECLARE n INT;/*计数器*/
                        DECLARE i INT DEFAULT 0;
                        SET @result = null;
                        SELECT `rgt`,`level` INTO rightval,levelval FROM `category` WHERE id = pid;
                        IF rightval THEN
                          START TRANSACTION;
                          UPDATE `category` SET `lft`=`lft`+2 WHERE `lft` > rightval;
                          SELECT ROW_COUNT() INTO n;
                          SET i = n + i;
                          UPDATE `category` SET `rgt`=`rgt`+ 2 WHERE `rgt` >= rightval;
                          SELECT ROW_COUNT() INTO n;
                          SET i = n + i;
                          INSERT INTO `category` (`name`,`parentid`,`lft`,`rgt`,`level`) VALUES(name,pid,rightval,rightval+1,levelval+1);
                          SELECT ROW_COUNT() INTO n;
                          SET i = n + i;
		                    IF i >= 2 THEN
		                     COMMIT;
		                       SET @result = 1000;
		                       SELECT 1000 AS result;
		                    ELSE
		                     ROLLBACK;
		                     SET @result = 1002;
		                     SELECT 1002 AS result;
                            END IF;
                        ELSE
                        SET @result = 1001;
                        SELECT 1001 AS result;
                        END IF;
                        END;";
// mysql_query($creatre_pro_sql,$conn) or die("invalid query:".mysql_error());
// mysql_query("SET @pid = 4",$conn);
// mysql_query("SET @name = '呵呵'",$conn);
// $result = mysql_query("CALL addCategory(@pid,@name)",$conn) or die("INVALID QUERY:".mysql_error());
// $row = mysql_fetch_row($result);
// echo $row[0];


//删除节点
//mysql_query("DROP PROCEDURE IF EXISTS `delCategory`;");
$del_pro_sql = "CREATE PROCEDURE `delCategory`(in pid INT)
                 BEGIN
                   DECLARE dlft INT;/*要删除的分类左值*/
                   DECLARE have_children INT;/*子级标志位*/
                   DECLARE n INT;
                   DECLARE i INT DEFAULT 0;
                   SET @result = null;
                   SET @parentid = null;
                   SET @name = null;
                   SELECT a.`lft`,IFNULL(COUNT(b.`id`),0),a.parentid,a.`name` INTO  dlft,have_children,@parentid,@name FROM `category`
                   AS a LEFT JOIN `category` AS b ON a.`id` = b.`parentid` WHERE a.`id` = pid GROUP BY b.`parentid`;
                   IF dlft&&!have_children THEN
                      IF dlft!=1 THEN
                         START TRANSACTION;
                         UPDATE `category` SET `lft` = `lft` - 2 WHERE `lft`>dlft;
                         SELECT ROW_COUNT() INTO n;
                         SET i = n + i;
                         UPDATE `category` SET `rgt` = `rgt` - 2 WHERE `rgt`>dlft;
                         SELECT ROW_COUNT() INTO n;
                         SET i = n + i;
                         DELETE FROM `category` WHERE `id`= pid;
                         SELECT ROW_COUNT() INTO n;
                         SET i = n + i;
                           IF i>=2 THEN
                              COMMIT;
                              SET @result = 1000;
                              SELECT 1000 AS result;
                           ELSE
                              ROLLBACK;
                              SET @result = 1002;
                              SELECT 1002 AS result;
                           END IF;
                        ELSE
                          SET @result = 1004;
                          SELECT 1004 AS result;
                        END IF;
                     ELSEIF dlft&&have_children THEN
                        SET @result = 1003;
                        SELECT 1003 AS result;
                     ELSE
                        SET @result = 1002;
                        SELECT 1002 AS result;
                     END IF;
                END";
// mysql_query($del_pro_sql,$conn) or die("invalid query:".mysql_error());
// mysql_query("SET @pid = 2",$conn);
// $result = mysql_query("CALL delCategory(@pid)",$conn) or die("INVALID QUERY:".mysql_error());
// $row = mysql_fetch_row($result);
// echo $row[0];


//移动节点
mysql_query("DROP PROCEDURE IF EXISTS `moveCategory`;");
$move_pro_sql = "CREATE PROCEDURE `moveCategory`(pid INT,tid INT)
                   BEGIN
                     IF pid = 1 THEN
                        SELECT 1004 AS result;
                     ELSE
                        IF pid != tid THEN
                           CALL delCategory(pid);
                           IF @result = 1000 THEN
                              CALL addCategory(tid,@name);
                              IF @result THEN
                                 SELECT 1000 AS result;
                              ELSE
                                 CALL addCategory(@parentid,@name);
                                 SELECT @result AS result;
                              END IF;
                            ELSE
                               SELECT @result AS result;
                            END IF;
                        ELSE
                            SELECT 1000 AS result;
                        END IF;
                      END IF;
                   END;"; 
// mysql_query($move_pro_sql,$conn) or die("invalid query:".mysql_error());
// mysql_query("SET @pid = 10",$conn);
// mysql_query("SET @tid = 4",$conn);
// $result = mysql_query("CALL moveCategory(@pid,@tid)",$conn) or die("INVALID QUERY:".mysql_error());
// $row = mysql_fetch_row($result);
// echo $row[0]; 


$query = mysql_query("SELECT `id`,`name`,concat(repeat('&nbsp;&nbsp;',`level`)) AS tag FROM `category`;");
echo '<ul>';
while ($row = mysql_fetch_array($query)) {
	echo '<li>'.$row['tag'].$row['name'].'</li>';
}
echo '</ul>';
print_r($rows);

mysql_close($conn);

?>
转载于:https://my.oschina.net/tianranqi/blog/181069