要在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数据库设计的问题
- 请教一个触发器的问题。
- MySql数据备份脚本
- 入成功,没有报错误。但是数据错误
- 关于MY-SQL管理员密码的问题,100分急求!!!在线等待!!!
- 紧急求救....关于数据库中的数据自动删除的问题
- 刚在本地装上MySQL-Front,联接数据库时提示:Client does not support authentication ~~~
- MySQL Connector/J 3.0 支持 MySQL4.0.12吗?
- mysql精确查找怎么做的????
- 命令行下的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;")