频道导航

• ▸ PHP
• ▸ Java
• ▸ Java SE
• ▸ Python
• ▸ C#
• ▸ C&C++
• ▸ Ruby
• ▸ VB
• ▸ asp.Net
• ▸ Go
• ▸ Perl
• ▸ netty
• ▸ Django
• ▸ Delphi
• ▸ Jsp
• ▸ .NET Core
• ▸ Spring
• ▸ Flask
• ▸ Springboot
• ▸ SpringMVC
• ▸ Lua
• ▸ Laravel
• ▸ Mybatis
• ▸ Asp
• ▸ Groovy
• ▸ ThinkPHP
• ▸ Yii
• ▸ swoole
• ▸ HTML
• ▸ HTML5
• ▸ JavaScript
• ▸ CSS
• ▸ jQuery
• ▸ Bootstrap
• ▸ Angularjs
• ▸ TypeScript
• ▸ Vue
• ▸ Dojo
• ▸ Json
• ▸ Electron
• ▸ Node.js
• ▸ extjs
• ▸ Express
• ▸ XML
• ▸ ES6
• ▸ Ajax
• ▸ Flash
• ▸ Unity
• ▸ React
• ▸ Flex
• ▸ Ant Design
• ▸ Web前端
• ▸ 微信小程序
• ▸ 微信公众号
• ▸ iOS
• ▸ Android
• ▸ Swift
• ▸ Hybrid
• ▸ Cocos2d-x
• ▸ Flutter
• ▸ Xcode
• ▸ Silverlight
• ▸ cocoa
• ▸ Cordova

• 前端之家
• PHP
• php读取txt文件组成SQL并插入数据库的代码（原创自Zjmainstay）

<div class="codebody" id="code24665">
<?PHP
/*
$splitChar 字段分隔符
$file 数据文件文件名
$table 数据库表名
$conn 数据库连接
$fields 数据对应的列名
$insertType 插入操作类型，包括INSERT,REPLACE
/
function loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields=array(),$insertType='INSERT'){
if(empty($fields)) $head = "{$insertType} INTO {$table} VALUES('";
else $head = "{$insertType} INTO {$table}(".implode(',',$fields).") VALUES('"; //数据头
$end = "')";
$sqldata = trim(file_get_contents($file));
if(preg_replace('/\s/i','',$splitChar) == '') {
$splitChar = '/(\w+)(\s+)/i';
$replace = "$1','";
$specialFunc = 'preg_replace';
}else {
$splitChar = $splitChar;
$replace = "','";
$specialFunc = 'str_replace';
}
//处理数据体，二者顺序不可换，否则空格或Tab分隔符时出错
$sqldata = preg_replace('/(\s)(\n+)(\s)/i','\'),(\'',$sqldata); //替换换行
$sqldata = $specialFunc($splitChar,$replace,$sqldata); //替换分隔符
$query = $head.$sqldata.$end; //数据拼接
if(MysqL_query($query,$conn)) return array(true);
else {
return array(false,MysqL_error($conn),MysqL_errno($conn));
}
}
//调用示例1
require 'db.PHP';
$splitChar = '|'; //竖线
$file = 'sqldata1.txt';
$fields = array('id','parentid','name');
$table = 'cengji';
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/sqlda ta1.txt
|0|A
|1|B
|1|C
|2|D
-- cengji
CREATE TABLE cengji (
id int(11) NOT NULL AUTO_INCREMENT,
parentid int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY parentid_name_unique (parentid,name) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1602 DEFAULT CHARSET=utf8
/
//调用示例2
require 'db.PHP';
$splitChar = ' '; //空格
$file = 'sqldata2.txt';
$fields = array('id','make','model','year');
$table = 'cars';
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/ sqldata2.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
-- cars
CREATE TABLE cars (
id int(11) NOT NULL AUTO_INCREMENT,
make varchar(16) NOT NULL,
model varchar(16) DEFAULT NULL,
year varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
/
//调用示例3
require 'db.PHP';
$splitChar = ' '; //Tab
$file = 'sqldata3.txt';
$fields = array('id','year');
$table = 'cars';
$insertType = 'REPLACE';
$result = loadTxtDataIntoDatabase($splitChar,$fields,$insertType);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/ sqldata3.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
/
//调用示例3
require 'db.PHP';
$splitChar = ' '; //Tab
$file = 'sqldata3.txt';
$fields = array('id','value');
$table = 'notExist'; //不存在表
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
//附：db.PHP
/ //注释这一行可全部释放
?>
<?PHP
static $connect = null;
static $table = 'jilian';
if(!isset($connect)) {
$connect = MysqL_connect("localhost","root","");
if(!$connect) {
$connect = MysqL_connect("localhost","Zjmainstay","");
}
if(!$connect) {
die('Can not connect to database.Fatal error handle by /test/db.PHP');
}
MysqL_select_db("test",$connect);
MysqL_query("SET NAMES utf8",$connect);
$conn = &$connect;
$db = &$connect;
}
?>
//*/