PHP 与 MySQL 配合的小案例：使用数据接口填充自己的数据库
PHP 与 MySQL 配合的小案例
进行WEB后端开发，难免用到PHP与MySQL这对好基友。这几天一直在烦恼做一个什么样的示例来简单有效地说明这哥俩的配合方式，既然要用到MySQL，那么一个简单的数据库表是要有的。简单的数据库表很好设计，但数据从哪里来呢？一开始笔者想过不然写个表单，手动输入插入数据库？
数据数据...然后笔者就想到了干脆用数据接口获取数据存表，省去了自己手动输入数据的繁琐（笔者...笔者毕业设计就是这么干的）。说做就做，懒得找其他数据网站了，还是用之前做天气的那个网站：极速数据，翻了翻它的一些接口，看到 谜语 这个接口，感觉还是很有趣的嘛，可以做一个小型的猜谜语程序。很好，为自己的聪明机智点赞！
第一步：创建数据库
不对，第一步应该是搭建自己的本地环境：Apache + PHP + MySQL或者Nginx + PHP + MySQL。这里就不再赘述，网上也有各种教程。笔者自己的WEB环境为：
- Nginx 1.17.0
- PHP 7.3.6
- MySQL 8.0。16
配置好服务器的网站根目录和端口，确认服务器可以正常使用PHP；配置好MySQL的各项参数。
根据 谜语 这个接口的返回数据，这里笔者创建了一个名为test的数据库，并建立了一个名为riddle的数据库表，建表语句如下：
CREATE TABLE `riddle` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键：自增ID',
`content` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '谜语',
`anwser` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '谜底',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='谜语大全';
由于只是一个小案例，这里不过多追究数据库的设计问题。现在，我们开始使用PHP连接数据库！
注：本次服务器的地址为：localhost
第二步：连接数据库
为了方便配置，笔者将数据库连接的相关配置写在了/app/cfg/目录下，并将其命名为db_config.php，内容如下：
<?php
// 文件：/app/cfg/db_config.php
define('type', 'mysql');// 数据库类型：MySQL
define('host', 'localhost');// 服务器地址：本地为 localhost 或 127.0.0.1
define('port', 3306);// 数据库端口：一般默认为 3306
define('dbname', 'test');// 数据库名称：这里使用的是 test 数据库
define('username', 'root');// 用户名：具有相关权限的用户，这里为省事，使用了 root 用户
define('userpwd', 'mysql');// 数据库密码：这是在安装MySQL时就定义的，root 用户对应的密码
define('charset', 'utf8');// 客户端字符集：始终推荐使用 utf8 而不是 gbk
当连接数据库时，只需引入该文件并使用已定义的常量进行数据库连接。一个简单的测试数据库配置文件是否能正确连接数据库test：
<?php
// 文件：/test/connect_test.php
require_once '../app/cfg/db_config.php';
try {
$conn = new PDO(
type.':host='.host.';port='.port.';dbname='.dbname,
username,
userpwd,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$conn->query('SET NAMES '.charset);
echo '数据库连接成功！';
} catch (PDOException $e) {
echo self::ERROR_UNABLE;
error_log($e->getMessage());
return FALSE;
}
此时在浏览器中访问 http://localhost/test/connect_test.php ，查看结果。如果配置无误将输出数据库连接成功！。
多次操作数据库时，每次都连接一下容易造成资源浪费，也造成代码冗余。这里我们将数据库连接封装为一个单例模式（该类对象仅允许创建一次）的类，在/app/db/目录下创建文件Connection.php，代码如下：
<?php
namespace db;
use PDO;
use PDOException;
class Connection
{
const ERROR_UNABLE = '<br>数据库连接失败！<br>';
private static $_instance = NULL;
private $conn;
const DB_CONFIG = 'root_test.php';// 数据库配置文件
private function __construct($config)
{
// $config = require(realpath('../').'/app/cfg/'.$config);
$config = require(dirname(__DIR__).'/cfg/'.$config);
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
try {
$this->conn = new PDO(
type.':host='.host.';port='.port.';dbname='.dbname,
username,
userpwd,
$opt
);
$this->conn->query('SET NAMES '.charset);
// echo '数据库连接成功！';
} catch (PDOException $e) {
echo self::ERROR_UNABLE;
error_log($e->getMessage());
return FALSE;
}
return $this->conn;
}
private function __clone()
{
return $this;
}
static public function getInstance($config = self::DB_CONFIG)
{
if(!self::$_instance)
{
self::$_instance = new self($config);
}
return self::$_instance->conn;// 应该返回数据库连接，而不是连接实例，否则在外部无法使用PDO类方法
}
}
单例模式的特点为：构造方法（__construct()）和对象克隆方法（__clone()）被定义为private，禁止外部访问，同时公开一个静态方法（getInstance()）来获取类实例。
在这个类中，笔者也将数据库连接配置文件设置为默认参数，如果想使用别的配置文件，也可以将其存放在/app/cfg/目录下，并向Connection::getInstance()方法中传入该配置文件名。
现在，我们来尝试使用Connection类来连接数据库吧！
<?php
// 文件：/test/connect.php
require_once '../app/db/Connection.php';
use db\Connection;
$conn = Connection::getInstance();
if($conn) echo '数据库连接成功！';
第三步：使用谜语接口获取数据
连接数据库后，我们需要先获取谜语的数据来填充数据库。访问 https://www.jisuapi/com/ ，注册会员（如果已注册可直接跳过），找到谜语接口（在【娱乐购物】分类），仔细阅读其API接口的说明，了解其请求的参数，查看其示例代码。
可以看到PHP的示例代码中引入了一个curl.func.php文件，我们可以从旁边的查看代码按钮中看到该文件的下载，如图：
笔者将该文件的代码复制到/app/lib/目录下的curl.func.php文件中，不想下载的朋友可以直接在这里复制：
<?php
function curlOpen($url, $config = array())
{
$arr = array('post' => false,'referer' => $url,'cookie' => '', 'useragent' => 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.0.04506; customie8)', 'timeout' => 20, 'return' => true, 'proxy' => '', 'userpwd' => '', 'nobody' => false,'header'=>array(),'gzip'=>true,'ssl'=>false,'isupfile'=>false);
$arr = array_merge($arr, $config);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, $arr['return']);
curl_setopt($ch, CURLOPT_NOBODY, $arr['nobody']);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_USERAGENT, $arr['useragent']);
curl_setopt($ch, CURLOPT_REFERER, $arr['referer']);
curl_setopt($ch, CURLOPT_TIMEOUT, $arr['timeout']);
if($arr['gzip']) curl_setopt($ch, CURLOPT_ENCODING, 'gzip,deflate');
if($arr['ssl'])
{
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
}
if(!empty($arr['cookie']))
{
curl_setopt($ch, CURLOPT_COOKIEJAR, $arr['cookie']);
curl_setopt($ch, CURLOPT_COOKIEFILE, $arr['cookie']);
}
if(!empty($arr['proxy']))
{
curl_setopt ($ch, CURLOPT_PROXY, $arr['proxy']);
if(!empty($arr['userpwd']))
{
curl_setopt($ch,CURLOPT_PROXYUSERPWD,$arr['userpwd']);
}
}
//ip比较特殊，用键值表示
if(!empty($arr['header']['ip']))
{
array_push($arr['header'],'X-FORWARDED-FOR:'.$arr['header']['ip'],'CLIENT-IP:'.$arr['header']['ip']);
unset($arr['header']['ip']);
}
$arr['header'] = array_filter($arr['header']);
if(!empty($arr['header']))
{
curl_setopt($ch, CURLOPT_HTTPHEADER, $arr['header']);
}
if ($arr['post'] != false)
{
curl_setopt($ch, CURLOPT_POST, true);
if(is_array($arr['post']) && $arr['isupfile'] === false)
{
$post = http_build_query($arr['post']);
}
else
{
$post = $arr['post'];
}
curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
}
$result = curl_exec($ch);
curl_close($ch);
return $result;
}
这个文件很详尽地写明了curl的用法，可以好好看看。再查看这个接口还需要什么数据，噢对了，APPKEY。我们可以直接在登录之后的“会员中心”中“申请API”，申请后可免费试用100次，对于一个小项目100次足够了。每个接口的使用都需要APPKEY，这个值可以在“会员中心”的“基本资料”中看到，如图：
这里笔者的APPKEY做了模糊处理，请不要轻易暴露自己的APPKEY，以免数据被盗刷。
准备工作已完成，接下来是获取数据了。
在/app/api/目录下，新建文件riddle.php，代码如下（其实只是改了一下接口的示例）：
<?php
require_once '../lib/curl.func.php';
require_once '../db/Connection.php';
use db\Connection;
$appkey = '51c0adda6dsdsb9d4f3';
$keyword = '酒';
$pagesize = 1;// 每页最大数据为2，这里干脆设为 1
$url = "https://api.jisuapi.com/miyu/search?appkey=$appkey&keyword=$keyword&pagesize=$pagesize&pagenum=&classid=";
$result = curlOpen($url, ['ssl'=>true]);
$jsonarr = json_decode($result, true);
if($jsonarr['status'] != 0) {
echo '该关键字下没有相关谜语！';
}
$arr = $jsonarr['result']['list'];
// // 避免出错时浪费API次数，可先使用测试数据检查行插入是否成功
// $arr = [
// 'content'=>1,
// 'anwser'=>2
// ];
// 连接数据库
$conn = Connection::getInstance();
$sql = 'INSERT INTO `riddle`(`content`,`anwser`) VALUES (:content, :anwser)';
$sth = $conn->prepare($sql);// 使用预处理语句，防止SQL注入（虽然这里没有多大必要
$sth->bindParam(':content', $arr['content']);
$sth->bindParam(':anwser', $arr['anwser']);
$res = $sth->execute();// 执行预处理语句，成功则返回TRUE
if($res) {
echo '插入数据成功！';
}
在浏览器中访问[http://localhost/app/api/riddle.php]，看到插入数据成功！并在数据库中看到该记录，则说明数据获取成功！
如何一次插入多条记录？
上面的代码只能靠关键字获取一条数据，这就有点慢了。再去看该接口说明，可以发现这个接口有11个谜语分类，那么我们可以靠循环传递参数来获取多条记录，修改后的代码如下：
<?php
require_once '../lib/curl.func.php';
require_once '../db/Connection.php';
use db\Connection;
$appkey = '51c0adda6dsdsb9d4f3';
$keyword = '酒';
$pagesize = 1; // 每页最大数据为2，这里设为 1
$arr = [];
for ($classid = 1; $classid < 12; $classid++) {
$url = "https://api.jisuapi.com/miyu/search?appkey=$appkey&keyword=$keyword&pagesize=$pagesize&pagenum=&classid=$classid";
$result = curlOpen($url, ['ssl' => true]);
$jsonarr = json_decode($result, true);
if ($jsonarr['status'] != 0) {
break;// 没有结果则跳到下一个分类
}
$arr[] = $jsonarr['result']['list'];
}
// var_dump($arr);exit;// 检查结果是否符合预期
// 连接数据库
$conn = Connection::getInstance();
$sql = 'INSERT INTO `riddle`(`content`,`anwser`) VALUES (:content, :anwser)';
$sth = $conn->prepare($sql); // 使用预处理语句，防止SQL注入（虽然这里没有多大必要
// 使用循环来插入数据
foreach ($arr as $key => $value) {
$sth->bindParam(':content', $value['content']);
$sth->bindParam(':anwser', $value['anwser']);
$res = $sth->execute(); // 执行预处理语句，成功则返回TRUE
}
在浏览器中访问[http://localhost/app/api/riddle.php]，并查看数据库riddle表，可以看到数据成功插入！
