一 :php连接mysql
代码:
<?php
$servername = "127.0.0.1";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=mysql", $username, $password);
echo "Connected successfully";
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
二 :insert,update,delete的语法类似,仅仅以insert为例,将以下代码扔到try块中就ok.
代码:
$sql="insert into test values(1,'a')";
$num=$conn->exec($sql);
echo "影响行数 $num";
三: select的语法略微复杂.
代码:
$sql="select * from test";
$res=$conn->query($sql);
foreach($res as $r){
echo $r["id"] . "
" //$r是一个array,我们仅仅输出了id列,可以print_r($r)输出
}
四 : 还可以使用预处理获取数据(略懂)
PDO::FETCH_ASSOC:返回一个索引为结果集列名的数组
PDO::FETCH_BOTH(默认):返回一个索引为结果集列名和以0开始的列号的数组
DOStatement::fetch :从结果集中获取下一行
方法一
$stmt=$conn->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute())) {
while ($row=$stmt->fetch()) { //默认是 fetch(PDO::FETCH_BOTH)print_r($row);
print "
"; }
}
?>
//输出 Array ( [id] => 2 [0] => 2 [name]=>"abc" [1]=>"abc")
Array ( [id] => 3 [0] => 3 [name]=>"def" [1]=>"def" )
方法二
$stmt=$conn->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute())) {
while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {print_r($row);
print "
";}
}
?>
//输出 Array ( [id] => 2 [name]=>"abc")
Array ( [id] => 3 [name]=>"def" )
五:事务处理(例子摘自php手册)
$conn->beginTransaction()/commit()/rollBack() 分别是开启/提交/回滚一个事务
代码:
try {$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//设置错误模式为异常$conn->beginTransaction();$conn->exec("insert into staff (id, first, last)
values (23, 'Joe', 'Bloggs')");$conn>exec("insert into salary(id, amount, changedate)
values (23, 50000, NOW())");
$dbh->commit();
} catch (Exception $e) {$conn->rollBack();
echo"Failed: ".$e->getMessage();
}
六 : insert的预处理(两种方法类似)
方法一:
$stmt = $conn->prepare("insert into test values(:id,:name)");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':name', $name);
// 插入行
$id=1;
$name = "Doe";
$stmt->execute();
// 插入其他行
$id=1;
$name = "Mary";
$stmt->execute();
方法二:
$stmt = $conn->prepare("insert into test values(?,?)");
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $name);
// 插入行
$id=1;
$name = "Doe";
$stmt->execute();
// 插入其他行
$id=1;
$name = "Mary";
$stmt->execute();