|
|
1 <html>
2 <body>
3 <?php
4 #数据库连接
5 $dbtype = 'mysql';
6 $host = 'localhost';
7 $db = 'test';
8 $user = 'root';
9 $psw = 'china0913';
10
11 $dsn = $dbtype . ':host=' . $host . ';' . 'dbname=' . $db;
12
13 try {
14 $dbh = new PDO($dsn, $user, $psw, array(PDO::ATTR_PERSISTENT=>true));
15 echo '连接成功<br>';
16 } catch(Exception $e) {
17 die('Connect Failed Message: ' . $e->getMessage());
18 }
19
20 #使用query函数查询
21 $sql = 'SELECT * FROM user';
22 $query = $dbh->query($sql);
23 $query->setFetchMode(PDO::FETCH_ASSOC); //设置结果集返回格式,此处为关联数组,即不包含index下标
24 $rs = $query->fetchAll();
25 var_dump($rs);
26
27 #使用exec函数进行INSERT,UPDATE,DELETE,结果返回受影响的行数
28 $sql = 'INSERT INTO user (`userName`, `userPassword`, `userAge`) SELECT (MAX(userId) + 1), \'123456\', 18 FROM user'; //插入一行用户数据,其中userName使用userId最大值+1
29 // $rs = $dbh->exec($sql);
30 // var_dump($rs) . '<br>';
31
32 #使用prepareStatement进行CURD
33 $sql = 'SELECT * FROM user WHERE userId = ?';
34 $stmt = $dbh->prepare($sql);
35 $stmt->bindParam(1, $userId); //绑定第一个参数值
36 $userId = 1;
37
38 $stmt->execute();
39 $stmt->setFetchMode(PDO::FETCH_ASSOC);
40 $rs = $stmt->fetchAll();
41 var_dump($rs);
42
43 #使用事务
44 try {
45 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置错误模式,发生错误时抛出异常
46 $dbh->beginTransaction();
47 $sql1 = 'SELECT bookNum FROM book WHERE bookId = ? FOR UPDATE'; //此处加上行锁,可以对bookNum做一些判断,bookNum>1,才做下一步更新操作
48 $sql2 = 'UPDATE book SET bookNum=bookNum-1 WHERE bookId = ?'; //加上行锁后,如果user1在买书,并且user1的买书过程没有结束,user2就不能执行SELECT查询书籍数量的操作,这样就保证了不会出现只有1本书,却两个人同时买的状况
49 $stmt1 = $dbh->prepare($sql1);
50 $stmt2 = $dbh->prepare($sql2);
51 $stmt1->bindParam(1, $userId);
52 $stmt2->bindParam(1, $userId);
53 $userId = 1;
54 $stmt1->execute();
55 $stmt2->execute();
56 $dbh->commit();
57 } catch(Exception $e) {
58 $dbh->rollBack();
59 die('Transaction Error Message: ' . $e->getMessage());
60 }
61 ?>
62 </body>
63 </html> |
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2015-8-28 11:21:45
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡