把附件（WORD、EXCEL、PDF）或图片存贮到数据库的完整范例程序。（MYSQL、SQL SERVER、ORACLE）三个版本

最新推荐文章于 2021-04-10 07:57:54 发布

z33

最新推荐文章于 2021-04-10 07:57:54 发布

阅读量1.3k

点赞数

文章标签： sql server mysql 数据库 oracle excel query

常有人问起关于把附件（WORD、EXCEL、PDF）或图片存贮到数据库的问题，本人也曾深受其扰。特将范例程序整理出来,包括sql server、oracle、mysql三个版本,供参考。

1、FOR SQL SERVER (存放文件的字段的类型为TEXT,一定要把PHP.INI中mssql.textlimit与mssql.textsize前面的分号去掉，并且值设置大一点.此问题是由PHPTEAMK兄解决的)
保存入库程序：
if($upfile!=''){
$PSize = filesize($upfile);
$fp=fopen($upfile, "rb");
$mssqldoc = base64_encode(fread($fp, $PSize));
$upfiletype = strtolower(substr( strrchr( $upfile_name, "." ), 1 ) );
fclose($fp);

mssql_connect("localhost", "sa", "mypassword") or
die("Unable to connect to SQL server");
@mssql_select_db("db") or die("Unable to select database");
$sql1="select * from document where do_code=$ar_slavecode";
$result=mssql_query($sql1);
if ($row=mssql_fetch_array($result))
{
           $sql="update document set do_file= ('$mssqldoc'),do_timeadded=getdate(),do_type='$upfiletype' ";
           $sql.=" where do_code=$ar_slavecode ";
}
else
{
$sql="insert into document (do_code,do_file,do_timeadded,do_type) values (";
$sql.="$ar_slavecode,('$mssqldoc'),getdate(),'$upfiletype')";
}

mssql_query($sql);
从数据库调出显示程序：
$sql="select do_file,do_type from document where do_code='$id'"; $result = mssql_query($sql);
$row = mssql_fetch_array($result);
$data=base64_decode($row[do_file]);
$type=strtoupper($row[do_type]);
if ($type=="DOC")
            header("Content-type:application/msword");
else if($type=="XLS")
            header("Content-type:application/x-msexcel");
else if($type=="PDF")
           header("Content-type:application/pdf");
else
           header("Content-Disposition:attachment");

echo $data;

2、FOR MSSQL
字段类型为BLOB。假定文件上传域的名称为Picture。
保存图片：
<? If($Picture != "none") { $PSize = filesize($Picture); $mysqlPicture = addslashes
(fread(fopen($Picture, "r"), $PSize)); mysql_connect($host,$username,$password) or
die("Unable to connect to SQL server"); @mysql_select_db($db) or die("Unable to
select database"); mysql_query("INSERT INTO Images (Image)
VALUES '($mysqlPicture')") or die("Can't Perform Query"); } else { echo"You did not
upload any picture"; } ?>
首先，我们通过“If($Picture != "none")”检
查是否有文件被上传。然后，使用addslashes()函数避免出现数据格式错误。最后，连接MySQL，
选择数据库并插入图片。

显示图片 :
<HTML> <BODY> <? mysql_connect($host,$username,$password) or die("Unable to
connect to SQL server"); @mysql_select_db($db) or die("Unable to select database");
$result=mysql_query("SELECT * FROM Images") or die("Can't Perform Query"); While
($row=mysql_fetch_object($result)) { echo "<IMG SRC=/"Second.php3? PicNum=$row-
>PicNum/">"; } ?> </BODY> </HTML>
Second.php3文件如下：
<? $result=mysql_query("SELECT * FROM Images WHERE PicNum=$PicNum") or die("Can't
perform Query"); $row=mysql_fetch_object($result); Header( "Content-type:
image/gif"); echo $row->Image; ?>

3、FOR ORACLE
字段类型为BLOB。
保存程序：
$conn=OCILogon("db", "password", "database");
If($upfile!='none')
{
            $upfiletype = strtolower(substr( strrchr( $upfile_name, "." ), 1 ) );
            $sql="insert into commondocument (cd_file,cd_timeadded,cd_condition,cd_version,cd_type) values (";
$sql.="EMPTY_BLOB(),sysdate,'$code','$cd_version','$upfiletype') returning cd_file into :cd_file";
                       //echo $sql;
                       $blob_body = OCINewDescriptor($conn, OCI_D_LOB);
$stmt = OCIparse($conn, $sql);
OCIBindByName($stmt, ":cd_file", &$blob_body, -1, OCI_B_BLOB);
OCIexecute($stmt,OCI_DEFAULT);
                       $fp = fopen($upfile, "r" );
            $blob_body->save(fread($fp, filesize($upfile ) ) );
OCIcommit($conn);
}

显示程序：
.....
echo "<a href='test2.php?id=$slavecode'>测试文件</a>";
......
-----test2.php----

$conn=OCILogon("oa", "select", "orcl");
$sql="select cd_file,cd_type from commondocument where cd_code='$id' and cd_condition='$condition'";
$stmt = OCIParse($conn,$sql);
OCIExecute($stmt);
ocicommit($conn);
OCIFetchinto($stmt, $row, OCI_RETURN_LOBS);
OCIFreeStatement($stmt);
OCILogoff($conn);
$ty
---------------------------------------------------------------