记录一下PHP连接MySQL的两种方式。
先mock一下数据，可以执行一下sql。
<span>/*</span><span>创建数据库</span><span>*/</span>
<span>CREATE</span> <span>DATABASE</span> <span>IF</span> <span>NOT</span> <span>EXISTS</span><span> `test`;
</span><span>/*</span><span>选择数据库</span><span>*/</span>
<span>USE</span><span> `test`;
</span><span>/*</span><span>创建表</span><span>*/</span>
<span>CREATE</span> <span>TABLE</span> <span>IF</span> <span>NOT</span> <span>EXISTS</span> `<span>user</span><span>` (
name </span><span>varchar</span>(<span>50</span><span>),
age </span><span>int</span><span>
);
</span><span>/*</span><span>插入测试数据</span><span>*/</span>
<span>INSERT</span> <span>INTO</span> `<span>user</span>` (name, age) <span>VALUES</span>(<span>'</span><span>harry</span><span>'</span>, <span>20</span>), (<span>'</span><span>tony</span><span>'</span>, <span>23</span>), (<span>'</span><span>harry</span><span>'</span>, <span>24</span>);第一种是使用PHP原生的方式去连接数据库。代码如下：
<?<span>php
</span><span>$host</span> = 'localhost'<span>;
</span><span>$database</span> = 'test'<span>;
</span><span>$username</span> = 'root'<span>;
</span><span>$password</span> = 'root'<span>;
</span><span>$selectName</span> = 'harry';<span>//</span><span>要查找的用户名，一般是用户输入的信息</span>
<span>$connection</span> = <span>mysql_connect</span>(<span>$host</span>, <span>$username</span>, <span>$password</span>);<span>//</span><span>连接到数据库</span>
<span>mysql_query</span>("set names 'utf8'");<span>//</span><span>编码转化</span>
<span>if</span> (!<span>$connection</span><span>) {
</span><span>die</span>("could not connect to the database.\n" . <span>mysql_error</span>());<span>//</span><span>诊断连接错误</span>
<span>}
</span><span>$selectedDb</span> = <span>mysql_select_db</span>(<span>$database</span>);<span>//</span><span>选择数据库</span>
<span>if</span> (!<span>$selectedDb</span><span>) {
</span><span>die</span>("could not to the database\n" . <span>mysql_error</span><span>());
}
</span><span>$selectName</span> = <span>mysql_real_escape_string</span>(<span>$selectName</span>);<span>//</span><span>防止SQL注入</span>
<span>$query</span> = "select * from user where name = '<span>$selectName</span>'";<span>//</span><span>构建查询语句</span>
<span>$result</span> = <span>mysql_query</span>(<span>$query</span>);<span>//</span><span>执行查询</span>
<span>if</span> (!<span>$result</span><span>) {
</span><span>die</span>("could not to the database\n" . <span>mysql_error</span><span>());
}
</span><span>while</span> (<span>$row</span> = <span>mysql_fetch_row</span>(<span>$result</span><span>)) {
</span><span>//</span><span>取出结果并显示</span>
<span>$name</span> = <span>$row</span>[0<span>];
</span><span>$age</span> = <span>$row</span>[1<span>];
</span><span>echo</span> "Name: <span>$name</span> "<span>;
</span><span>echo</span> "Age: <span>$age</span> "<span>;
</span><span>echo</span> "\n"<span>;
}</span>其运行结构如下：
Name: harry Age: 20<span> Name</span>: tony Age: 23
第二种是使用PDO的方式去连接数据库，代码如下：
<?<span>php
</span><span>$host</span> = 'localhost'<span>;
</span><span>$database</span> = 'test'<span>;
</span><span>$username</span> = 'root'<span>;
</span><span>$password</span> = 'root'<span>;
</span><span>$selectName</span> = 'harry';<span>//</span><span>要查找的用户名，一般是用户输入的信息</span>
<span>$pdo</span> = <span>new</span> PDO("mysql:host=<span>$host</span>;dbname=<span>$database</span>", <span>$username</span>, <span>$password</span>);<span>//</span><span>创建一个pdo对象</span>
<span>$pdo</span>-><span>exec</span>("set names 'utf8'"<span>);
</span><span>$sql</span> = "select * from user where name = ?"<span>;
</span><span>$stmt</span> = <span>$pdo</span>->prepare(<span>$sql</span><span>);
</span><span>$rs</span> = <span>$stmt</span>->execute(<span>array</span>(<span>$selectName</span><span>));
</span><span>if</span> (<span>$rs</span><span>) {
</span><span>//</span><span> PDO::FETCH_ASSOC 关联数组形式
// PDO::FETCH_NUM 数字索引数组形式</span>
<span>while</span> (<span>$row</span> = <span>$stmt</span>->fetch(PDO::<span>FETCH_ASSOC)) {
</span><span>$name</span> = <span>$row</span>['name'<span>];
</span><span>$age</span> = <span>$row</span>['age'<span>];
</span><span>echo</span> "Name: <span>$name</span> "<span>;
</span><span>echo</span> "Age: <span>$age</span> "<span>;
</span><span>echo</span> "\n"<span>;
}
}
</span><span>$pdo</span> = <span>null</span>;<span>//</span><span>关闭连接</span>其结果与第一种相同。
