drop table resIntru
--创建当前用户发展的所有子代
create table resIntru
(
resIntruId int identity(1,1) primary key,
userId int, --子代Id
stateId int --当前位置
)/**查询当前用户的所有子代用户存储过程*/
drop proc pro_resIntru
create proc pro_resIntru
@userMobile varchar(80)--当前用户
as
begin
declare
@userIdNow int,--当前用户Id
@intBeIdNow int,--推荐人Id
@stateId int,--当前用户在族谱中的位置
@baseStateId int,--以当前用户的根的最大位置
@beginState int--以当前用户的根的最大位置
--清空用子信息表
delete from resIntru
set @beginState=1
--当前用户所在族谱中的位置
select @stateId=resState from tb_reship where resMobile=@userMobile
--以当前用户的根的最大位置
select @baseStateId=max(resState) from tb_reship where resBaseUserId=(select resBaseUserId from tb_reship where resMobile=@userMobile)
--获取当前用户的直接子代userId,添加到resIntru表中
insert into resIntru (userId,stateId) select userId,@beginState from tb_intruduce where intBeId=(select userId from tb_user where userMobile=@userMobile)
while(@stateId<@baseStateId-1)--循环次数
begin
set @beginState=@beginState+1
--获取当前用户的间接子代userId,添加到resIntru表中
insert into resIntru(userId,stateId) select userId,@beginState from tb_intruduce where intBeId in (select userId from resIntru)
set @stateId=@stateId+1
end
--获取所有子代用户号码
select u.userMobile,r.stateId from tb_user u,resIntru r where u.userid=r.userid
end
GO
--执行存储过程
exec pro_resIntru '123123123'
如何用PHP调用这个存储过程呢?? 请大家给个详细的答案!
--创建当前用户发展的所有子代
create table resIntru
(
resIntruId int identity(1,1) primary key,
userId int, --子代Id
stateId int --当前位置
)/**查询当前用户的所有子代用户存储过程*/
drop proc pro_resIntru
create proc pro_resIntru
@userMobile varchar(80)--当前用户
as
begin
declare
@userIdNow int,--当前用户Id
@intBeIdNow int,--推荐人Id
@stateId int,--当前用户在族谱中的位置
@baseStateId int,--以当前用户的根的最大位置
@beginState int--以当前用户的根的最大位置
--清空用子信息表
delete from resIntru
set @beginState=1
--当前用户所在族谱中的位置
select @stateId=resState from tb_reship where resMobile=@userMobile
--以当前用户的根的最大位置
select @baseStateId=max(resState) from tb_reship where resBaseUserId=(select resBaseUserId from tb_reship where resMobile=@userMobile)
--获取当前用户的直接子代userId,添加到resIntru表中
insert into resIntru (userId,stateId) select userId,@beginState from tb_intruduce where intBeId=(select userId from tb_user where userMobile=@userMobile)
while(@stateId<@baseStateId-1)--循环次数
begin
set @beginState=@beginState+1
--获取当前用户的间接子代userId,添加到resIntru表中
insert into resIntru(userId,stateId) select userId,@beginState from tb_intruduce where intBeId in (select userId from resIntru)
set @stateId=@stateId+1
end
--获取所有子代用户号码
select u.userMobile,r.stateId from tb_user u,resIntru r where u.userid=r.userid
end
GO
--执行存储过程
exec pro_resIntru '123123123'
如何用PHP调用这个存储过程呢?? 请大家给个详细的答案!
$host="localhost";
$user="root";
$password="11212";
$db="samp_db";
$dblink=mysql_connect($host,$user,$password)
or die("can't connect to mysql");
mysql_select_db($db,$dblink)
or die("can't select samp_db");
$res=mysql_query("set @a=$password",$dblink);
$res=mysql_query("call aa(@a)",$dblink);
$res=mysql_query("select @a",$dblink);
$row=mysql_fetch_row($res);
echo $row[0];
$host="localhost";
$user="root";
$password="11212";
$db="samp_db";
$dblink=mysql_connect($host,$user,$password)
or die("can't connect to mysql");
mysql_select_db($db,$dblink)
or die("can't select samp_db");
$res=mysql_query("set @a=$password",$dblink);
$res=mysql_query("call aa(@a)",$dblink);
$res=mysql_query("select @a",$dblink);
$row=mysql_fetch_row($res);
echo $row[0];
define ("OLEDB_CONNECTION_STRING",
"Provider=SQLOLEDB; Data Source=zzb; Initial Catalog=Northwind; User ID=sa; Password=");
$dbc = new COM("ADODB.Connection");
$dbc->Open(OLEDB_CONNECTION_STRING);
$command = "pro_resIntru";
$rs = $dbc->Execute($command);只能在win服务器上运行