56,678
社区成员
发帖
与我相关
我的任务
分享
mysql_connect($this->host,$this->user,$this->pass,1,131072)or die("数据库连接失败:".mysql_error());//这样可以执行存储过程不报错,但是程序中不能执行创建临时表
mysql_connect($this->host,$this->user,$this->pass)or die("数据库连接失败:".mysql_error());//这样执行存储过程报错,程序能创建临时表
$ctable="CREATE temporary TABLE beyond(
`mbtype` varchar(30) NOT NULL,
`mstype` varchar(30) NOT NULL,
`mname` varchar(200) NOT NULL,
`mcolor` varchar(200) NOT NULL,
`mcode` varchar(30) NOT NULL,
`cust` varchar(30) NOT NULL,
`spdp` float NOT NULL,
`souput` float NOT NULL,
`sback` float NOT NULL,
`sbeyond` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312";
//echo $ctable;
$db->query($ctable);
请问是何故?
$ctable="CREATE temporary TABLE beyond(
`mbtype` varchar(30) NOT NULL,
`mstype` varchar(30) NOT NULL,
`mname` varchar(200) NOT NULL,
`mcolor` varchar(200) NOT NULL,
`mcode` varchar(30) NOT NULL,
`cust` varchar(30) NOT NULL,
`spdp` float NOT NULL,
`souput` float NOT NULL,
`sback` float NOT NULL,
`sbeyond` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312";
//echo $ctable;
$db->query($ctable);
$insert="insert into beyond select mbtype,mstype,mname,mcolor,mcode,cust,sum(PDP_num),sum(output_num),sum(back_num),((sum(output_num)-sum(back_num)-sum(PDP_num))/sum(PDP_num))*100 as beyond from superrate where left(sr_date,10) between '$fdate' and '$tdate' and (muser='办房做办' or back_num>'0') group by mcode";
//echo $insert;
$db->query($insert);
$sql="select mbtype,mstype,mname,mcolor,mcode,cust,spdp,souput,sback,sbeyond from beyond where (1=1)";
$rownum=$db->rownum($sql);//这里报SQL没有资源的错误,也就是说临时表里没数据
DELIMITER $$
USE `db_offsmplroom`$$
DROP PROCEDURE IF EXISTS `UpdateMcode`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateMcode`(
IN mcode VARCHAR(20),
IN btype VARCHAR(50),
IN stype VARCHAR(50),
IN mname VARCHAR(255),
IN mcolor VARCHAR(200),
IN ecode VARCHAR(50),
IN munit VARCHAR(50)
)
BEGIN
DECLARE back INT;
IF LENGTH(mcode)<=0 OR LENGTH(btype)<=0 OR LENGTH(stype)<=0 OR LENGTH(mname)<=0 OR LENGTH(stype)<=0 THEN
SET back=1;
SELECT back;
else
SET back=0;
SELECT back;
END IF;
END$$
DELIMITER ;
function connect(){
$conn=mysql_connect($this->host,$this->user,$this->pass,1,131072)or die("数据库连接失败:".mysql_error());
mysql_select_db($this->db,$conn)or die("The database is not connect!".mysql_error());
mysql_query("set names gb2312");
return $conn;
}
function query($sql){//这里就是我的query函数了,没有关闭connection,其他地方也没有。
return mysql_query($sql,$this->connect());
}
$ctable="CREATE temporary TABLE beyond(
`mbtype` varchar(30) NOT NULL,
`mstype` varchar(30) NOT NULL,
`mname` varchar(200) NOT NULL,
`mcolor` varchar(200) NOT NULL,
`mcode` varchar(30) NOT NULL,
`cust` varchar(30) NOT NULL,
`spdp` float NOT NULL,
`souput` float NOT NULL,
`sback` float NOT NULL,
`sbeyond` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312";
//echo $ctable;
$db->query($ctable);
$insert="insert into beyond select mbtype,mstype,mname,mcolor,mcode,cust,sum(PDP_num),sum(output_num),sum(back_num),((sum(output_num)-sum(back_num)-sum(PDP_num))/sum(PDP_num))*100 as beyond from superrate where left(sr_date,10) between '$fdate' and '$tdate' and (muser='办房做办' or back_num>'0') group by mcode";
//echo $insert;
$db->query($insert);
$sql="select mbtype,mstype,mname,mcolor,mcode,cust,spdp,souput,sback,sbeyond from beyond where (1=1)";
$rownum=$db->rownum($sql);//这里报SQL没有资源的错误,也就是说临时表里没数据