mysql 存储过程 无限分类,存储过程无限分类 - 我叫喵二狗的个人页面 - OSCHINA - 中文开源技术交流社区...

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('  ',`level`)) AS tag FROM `category`;");

echo '

• ';

while ($row = mysql_fetch_array($query)) {

echo '

'.$row['tag'].$row['name'].'';

}

echo '

';

print_r($rows);

mysql_close($conn);

?>