[PHP] - mysql 数据库操作
使用PHP操作数据库有两种方式
- 使用mysql_XXXX()方法
- 使用这种方式，需要先把php.ini里的extension=php_mysql.dll去掉注释
- 使用PDO
- 使用这种试，需要把php.ini里的extension=php_pdo_mysql.dll去掉注释
下面演示使用第一种方式：
<!doctype html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<?php
$conn = mysql_connect("localhost", "root", "XXXXXX");
if(!$conn) {
die("Could not connect:" . mysql_error());
}
mysql_select_db("test", $conn);
//mysql_query("SET NAMES utf8");
$result = mysql_query("INSERT INTO mytable(headline, create_time) VALUES('中国', '" . date("Y-m-d h:i:s") . "');");
if( $result < 1) {
echo "insert error!";
}
$query = mysql_query("SELECT * FROM mytable LIMIT 100 OFFSET 0;");
while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
echo "<p>", $row["id"], " - " , $row["headline"], " - ", $row["create_time"], "</p>";
}
mysql_close();
?>
</body>
</html>
下面是使用PDO方式：
参数引用：
http://php.ncong.com/mysql/pdo/pdo_huoqu.html
<!doctype html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "XXXXXX");
//设置错误使用异常的模式
$pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//关闭自动提交
//$pdo-> setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
} catch (PDOException $e) {
echo sprintf("Exception message=%s", $e->getMessage());
exit();
}
/**
* 防SQL注入方式条件查询
*/
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE id = :id');
$stmt->execute(array(":id"=>1));
foreach ($stmt as $row) {
echo $row["headline"];
}
/**
* 插入数据
*/
$result = $pdo->exec("INSERT INTO mytable(headline, create_time) VALUES('中国', '" . date("Y-m-d h:i:s") . "');");
if($result) {
$str = sprintf("add data completed, lastupdateid=%s", $pdo->lastInsertId());
echo $str;
}
/**
* 查询
*/
echo "<hr/>查询";
$rs = $pdo->query("SELECT * FROM mytable");
while ($row = $rs->fetch()) {
echo "<p>", $row["id"], " - " , $row["headline"], " - ", $row["create_time"], "</p>";
}
/**
* 字段映射方式查询
*/
echo "<hr/>字段映射方式查询";
$q = $pdo->query("SELECT id, headline, create_time FROM mytable");
while (list($id, $headline, $createTime) = $q->fetch(PDO::FETCH_NUM)) {
echo "<p>", $id, " - " , $headline, " - ", $createTime, "</p>";
}
/**
* 一次性查询方式
*/
echo "<hr/>一次性查询方式";
$query = $pdo->prepare("SELECT * FROM mytable");
$query->execute();
$rows = $query->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo "<p>", $row["id"], " - " , $row["headline"], " - ", $row["create_time"], "</p>";
}
/**
* 字段绑定方式
*/
echo "<hr/>字段绑定方式";
$stm = $pdo->prepare("SELECT id, headline, create_time FROM mytable");
$stm->execute();
$stm->bindColumn(1, $id);
$stm->bindColumn("headline", $headline);
$stm->bindColumn(3, $createTime);
while ($stm->fetch(PDO::FETCH_BOUND)) {
echo "<p>", $id, " - " , $headline, " - ", $createTime, "</p>";
}
//$pdo-> setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
?>
</body>
</html>
建议使用PDO方式，这样可以减少SQL注入安全性问题。（php5以上建议使用PDO方式做数据库操作）
