mysql常用: http://blog.csdn.net/yjj1s/archive/2011/05/23/6440345.aspx
1 建立连接
$dbh = new PDO('mysql:host=localhost; port=3306; dbname=test', $user, $pass, array(
PDO::ATTR_PERSISTENT => true
));
持久性链接 PDO::ATTR_PERSISTENT => true
2. 捕捉错误
try {
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec("SET CHARACTER SET utf8");
$dbh = null; //断开连接
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
3. 事务的
try {
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();//开启事务
$dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
$dbh->exec("insert into salarychange (id, amount, changedate)
values (23, 50000, NOW())");
$dbh->commit();//提交事务
} catch (Exception $e) {
$dbh->rollBack();//错误回滚
echo "Failed: " . $e->getMessage();
}
4. 错误处理
a. 静默模式(默认模式)
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); //不显示错误
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//显示警告错误,并继续执行
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//产生致命错误,PDOException
==========================
1. 使用 query()
$dbh->query($sql); 当$sql 中变量可以用 $dbh->quote($params); //转义字符串的数据
2. 使用 prepare, bindParam 和 execute [建议用,同时可以用添加、修改、删除]
$dbh->prepare($sql); 产生了个 PDOStatement 对象
PDOStatement->bindParam()
PDOStatement->execute();//可以在这里放绑定的相应变量
3. 事物
PDO常用方法:
PDO::query() 主要用于有记录结果返回的操作(PDOStatement),特别是select操作。
PDO::exec()主要是针对没有结果集合返回的操作。如insert,update等操作。返回影响行数。
PDO::lastInsertId()返回上次插入操作最后一条ID,但要注意:如果用insert into tb(col1,col2) values(v1,v2),(v11,v22)..的方式一次插入多条记录,lastinsertid()返回的只是第一条(v1,v2)插入时的ID,而不是最后一条记录插入的记录ID。
PDOStatement::fetch()是用来获取一条记录。配合while来遍历。
PDOStatement::fetchAll()是获取所有记录集到一个中。
PDOStatement::fetchcolumn([int column_indexnum]) 用于直接访问列,参数column_indexnum是该列在行中的从0开始索引值,但是,这个方法一次只能取得同一行的一列,只要执行一次,就跳到下一行。因此,用于直接访问某一列时较好用,但要遍历多列就用不上。
PDOStatement::rowcount()适用于当用query("select ...")方法时,获取记录的条数。也可以用于预处理中。$stmt->rowcount();
PDOStatement::columncount()适用于当用query("select ...")方法时,获取记录的列数。
注解:
1、选fetch还是fetchall?
小记录集时,用fetchall效率高,减少从数据库检索次数,但对于大结果集,用fetchall则给系统带来很大负担。数据库要向WEB前端传输量太大反而效率低。
2、fetch()或fetchall()有几个参数:
mixed pdostatement::fetch([int fetch_style [,int cursor_orientation [,int cursor_offset]]])
array pdostatement::fetchAll(int fetch_style)
fetch_style参数:
■$row=$rs->fetchAll(PDO::FETCH_BOTH); FETCH_BOTH是默认的,可省,返回关联和索引。
■$row=$rs->fetchAll(PDO::FETCH_ASSOC); FETCH_ASSOC参数决定返回的只有关联数组。
■$row=$rs->fetchAll(PDO::FETCH_NUM); 返回索引数组
■$row=$rs->fetchAll(PDO::FETCH_OBJ); 如果fetch()则返回对象,如果是fetchall(),返回由对象组成的二维数组,
pdo mysql dsn(Data Source Name) http://www.php.net/manual/zh/ref.pdo-mysql.connection.php
参考: http://hi.baidu.com/traindiy/blog/item/050f3087a544e621c75cc378.html
http://www.php.net/manual/zh/book.pdo.php
- PDO — The PDO class
- PDO::beginTransaction — Initiates a transaction
- PDO::commit — Commits a transaction
- PDO::__construct — Creates a PDO instance representing a connection to a database
- PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle
- PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle
- PDO::exec — Execute an SQL statement and return the number of affected rows
- PDO::getAttribute — Retrieve a database connection attribute
- PDO::getAvailableDrivers — Return an array of available PDO drivers
- PDO::inTransaction — Checks if inside a transaction
- PDO::lastInsertId — Returns the ID of the last inserted row or sequence value
- PDO::prepare — Prepares a statement for execution and returns a statement object
- PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object
- PDO::quote — Quotes a string for use in a query.
- PDO::rollBack — Rolls back a transaction
- PDO::setAttribute — Set an attribute
- PDOStatement — The PDOStatement class
- PDOStatement->bindColumn — Bind a column to a PHP variable
- PDOStatement->bindParam — Binds a parameter to the specified variable name
- PDOStatement->bindValue — Binds a value to a parameter
- PDOStatement->closeCursor — Closes the cursor, enabling the statement to be executed again.
- PDOStatement->columnCount — Returns the number of columns in the result set
- PDOStatement->debugDumpParams — Dump an SQL prepared command
- PDOStatement->errorCode — Fetch the SQLSTATE associated with the last operation on the statement handle
- PDOStatement->errorInfo — Fetch extended error information associated with the last operation on the statement handle
- PDOStatement->execute — Executes a prepared statement
- PDOStatement->fetch — Fetches the next row from a result set
- PDOStatement->fetchAll — Returns an array containing all of the result set rows
- PDOStatement->fetchColumn — Returns a single column from the next row of a result set
- PDOStatement->fetchObject — Fetches the next row and returns it as an object.
- PDOStatement->getAttribute — Retrieve a statement attribute
- PDOStatement->getColumnMeta — Returns metadata for a column in a result set
- PDOStatement->nextRowset — Advances to the next rowset in a multi-rowset statement handle
- PDOStatement->rowCount — Returns the number of rows affected by the last SQL statement
- PDOStatement->setAttribute — Set a statement attribute
- PDOStatement->setFetchMode — Set the default fetch mode for this statement