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>
