要在tree table 中找根,
CREATE TABLE `tree` (
`id` vARCHAR(20) default NULL,
`parentid` vARCHAR(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
tree 表格内容显示:
'id' 'parentid'
one ROOT
two one
three two
four two
five three我想通过存储过程找“five”的对上两个根,mysql存储过程tree_root如下:CREATE DEFINER = 'jhss_db_admin'@'%' PROCEDURE `tree_root`(IN Up_1 vARCHAR(20), OUT Up_2 vARCHAR(20), OUT Up_3 vARCHAR(20))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF(Up_1 <> "ROOT")THEN
SELECT parent_id as Up_2 FROM tree WHERE id = Up_1;
IF(Up_2 <> "ROOT")THEN
SELECT parent_id as Up_3 FROM tree WHERE id = Up_2;
ELSE
SET Up_3 = "NONE";
END IF;
ELSE
SET Up_2 = "NONE";
SET Up_3 = "NONE";
END IF;
END;为什么输出不了,说我格式错的?
另外,我想通过php调用此存储过程:// php begin :->
$mysqli = new mysqli("localhost", "username", "password", "db_tree");
$mysqli->query("SET NAMES 'GBK'");$query = $mysqli->query("set @leaf = 'five'");
$query = $mysqli->query("set @Up_2= ' '");
$query = $mysqli->query("set @Up_3= ' '");
$query = $mysqli->query("call tree_root(@leaf, @Up_2, @Up_3)");
$query = $mysqli->query("select @Up_2, @Up_3");$result = mysqli_fetch_array($query);
echo "$result[Up_2]<br>";
echo "$result[Up_3]<br>";// php end <-:页面调用也不行,请高手帮帮忙看是哪里错了,谢谢!
CREATE TABLE `tree` (
`id` vARCHAR(20) default NULL,
`parentid` vARCHAR(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
tree 表格内容显示:
'id' 'parentid'
one ROOT
two one
three two
four two
five three我想通过存储过程找“five”的对上两个根,mysql存储过程tree_root如下:CREATE DEFINER = 'jhss_db_admin'@'%' PROCEDURE `tree_root`(IN Up_1 vARCHAR(20), OUT Up_2 vARCHAR(20), OUT Up_3 vARCHAR(20))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF(Up_1 <> "ROOT")THEN
SELECT parent_id as Up_2 FROM tree WHERE id = Up_1;
IF(Up_2 <> "ROOT")THEN
SELECT parent_id as Up_3 FROM tree WHERE id = Up_2;
ELSE
SET Up_3 = "NONE";
END IF;
ELSE
SET Up_2 = "NONE";
SET Up_3 = "NONE";
END IF;
END;为什么输出不了,说我格式错的?
另外,我想通过php调用此存储过程:// php begin :->
$mysqli = new mysqli("localhost", "username", "password", "db_tree");
$mysqli->query("SET NAMES 'GBK'");$query = $mysqli->query("set @leaf = 'five'");
$query = $mysqli->query("set @Up_2= ' '");
$query = $mysqli->query("set @Up_3= ' '");
$query = $mysqli->query("call tree_root(@leaf, @Up_2, @Up_3)");
$query = $mysqli->query("select @Up_2, @Up_3");$result = mysqli_fetch_array($query);
echo "$result[Up_2]<br>";
echo "$result[Up_3]<br>";// php end <-:页面调用也不行,请高手帮帮忙看是哪里错了,谢谢!
解决方案 »
- 如何打印mysql_num_rows的返回值
- 触发器里中文串的处理问题请教
- 关于Mysql的Out of memory 与 Mysql内存的释放,版主、大拿进!高分伺候着!
- 一个64位的linux操作系统,mysql是5.1,query_cache_size大约设置多少合适,设置标准是什么。
- 关于MySQL Merge引擎的疑问?
- mysql 全角半角转换 请帮忙检测 修改 (求助) 在线等待
- mysqldump导出文件不能导出到含有and字符的文件件内
- 为什么我连不到mysql?
- mySQL两个表查询结果集组合
- 求助:一个sql查询问题,求大佬指教(本人是自学小白)
- 命令行下的mysql问题
- Delphi+ADO通过ODBC连接MYSQL ,查询UNSIGNED INTEGER型数据时发生溢出
然后procedure最后还要 select Up_2, Up_3;然后php调用时
$query = $mysqli->query("call tree_root('five', @Up_2, @Up_3)");
$result = mysqli_fetch_array($query); echo "$result[Up_2] <br>";
echo "$result[Up_3] <br>"; ^_^
mysqli_multi_query("call sp('',@e);select @e;")