PHP上传数据到SQLServer,以下运用sqlsrv函数实现
1.首先确认是否已经打开sqlsrv拓展(phpinfo)
2.话不多说,直接上代码
<?php
namespace app\spb\controller;
use think\Controller;
use think\Db;
class Uploadtest
{
public function index()
{
$data = Db::name('remote_detection')
->where(['second_synchronise' => '2'])
->order('id asc')
->limit('10')
->select();
$ip = "192.168.1.21";
$conninfo = array("Database" => "lnyk", "UID" => "sa", "PWD" => "care-2014");
$conn = sqlsrv_connect($ip, $conninfo);
if ($data) {
if ($conn != false) {
$i = 0;
$j = 0;
$fail = [];
$success = [];
foreach ($data as $row) {
$i++;
$row['car_number'] = iconv('UTF-8', 'GBK', $row['car_number']);
$row['item_name'] = iconv('UTF-8', 'GBK', $row['item_name']);
$row['receiving_company_name'] = iconv('UTF-8', 'GBK', $row['receiving_company_name']);
$row['send_company_name'] = iconv('UTF-8', 'GBK', $row['send_company_name']);
$pre_data['id'] = $row['id'];
$pre_data['car_number'] = $row['car_number'];
$pre_data['item_name'] = $row['item_name'];
$pre_data['send_company_name'] = $row['send_company_name'];
$pre_data['receiving_company_name'] = $row['receiving_company_name'];
$pre_data['first_check_weight'] = $row['first_check_weight'];
$pre_data['second_check_weight'] = $row['second_check_weight'];
$pre_data['first_check_time'] = date('Y-m-d H:i:s', $row['first_check_time']);
$pre_data['second_check_time'] = date('Y-m-d H:i:s', $row['second_check_time']);
$insert_data = "INSERT INTO [lnyk].[dbo].[gcz] (
[bdxh],
[ch],
[hm],
[ssq],
[ssdw],
[mz],
[pz],
[mztime],
[pztime]
)
VALUES (
'" . $pre_data['id'] . "',
'" . $pre_data['car_number'] . "',
'" . $pre_data['item_name'] . "',
'" . $pre_data['send_company_name'] . "',
'" . $pre_data['receiving_company_name'] . "',
'" . $pre_data['first_check_weight'] . "',
'" . $pre_data['second_check_weight'] . "',
'" . $pre_data['first_check_time'] . "',
'" . $pre_data['second_check_time'] . "'
)";
$result = sqlsrv_query($conn, $insert_data);
if ($result!=false) {
Db::name('remote_detection')->where(['id' => $row['id']])->update(['second_synchronise' => '1']);
$j++;
$success[] = $row['id'];
} else {
$fail[] = $row['id'];
}
}
if ($i == $j) {
$this->json_return(['code' => '0', 'msg' => '上传成功', 'data' => $success]);
} else {
$this->json_return(['code' => '101', 'msg' => '上传发生异常', 'data' => $fail]);
}
} else {
$this->json_return(['code' => '102', 'msg' => '数据库连接失败', 'data' => '']);
}
} else {
$this->json_return(['code' => '103', 'msg' => '没有新的数据', 'data' => '']);
}
}
public function json_return($result, $type = 'JSON')
{
if (strtoupper($type) == 'JSON') {
header('Content-Type:application/json; charset=utf-8');
exit(json_encode($result));
} elseif (strtoupper($type) == 'XML') {
header('Content-Type:text/xml; charset=utf-8');
exit(xml_encode($result));
} elseif (strtoupper($type) == 'EVAL') {
header('Content-Type:text/html; charset=utf-8');
exit($result);
} else {
exit($result);
}
}
}