[轉]mysqli & pdo使用实例和详解

From :http://blog.youkuaiyun.com/yuan_moon/archive/2008/11/28/3404606.aspx

<? php
$conn = new  mysqli( " localhost " , " root " , " root " , " db_database09 " );
$conn -> query( " set names gb2312 " );
$id = $_GET [id];
include_once ( " conn.php " );
$conn -> autocommit( false );
if ( ! $conn -> query( " delete from tb_sco where id=' " . $id . " ' " )){
     $conn -> rollback();
}
if ( ! $conn -> query( " delete from tb_stu where id=' " . $id . " ' " )){
     $conn -> rollback();
}
$conn -> commit();
$conn -> autocommit( true );
/*
Mysqli
连接数据库
*/
$mysqli   =   new  mysqli( " localhost " ,   " root " ,   " secret " ,   " test " );
if  ( mysqli_connect_errno ( )) {
     printf ( " Connect failed: %s\n " ,   mysqli_connect_error ( ));
     exit  ();
}  else  {
     printf ( " Connect succeeded\n " );
}


// 错误检查

if  ( $mysqli -> query( $sql )  <>   TRUE ) {
     printf ( " Statement failed %d: (%s) %s\n "   , $mysqli -> errno , $mysqli -> sqlstate , $mysqli -> error);
}


$mysqli -> query( $sql ) or  printf ( " Statement failed %d: (%s) %s\n "   , $mysqli -> errno , $mysqli -> sqlstate , $mysqli -> error);
$mysqli -> query( $sql );
if  ( $mysqli -> errno  <>   0  ) {
     printf ( " Statement failed %d: (%s) %s\n "   , $mysqli -> errno , $mysqli -> sqlstate , $mysqli -> error);
}


// 简单无返回查询

$mysqli -> query( " CREATE TABLE guy_1 (guys_integers INT) " );
if  ( $mysqli -> errno  <>   0  ) {
     printf ( " Statement failed %d: (%s) %s\n "   , $mysqli -> errno , $mysqli -> sqlstate , $mysqli -> error);
}

// 返回结果集fetch_object

$sql = " SELECT employee_id, surname, salary FROM employees WHERE salary>95000 AND department_id=1 AND status='G' " ;
$results = $mysqli -> query( $sql );
if  ( $mysqli -> errno) {  die  ( $mysqli -> errno . "   " . $mysqli -> error); }
while ( $row = $results -> fetch_object( )) {
     printf ( " %d\t%s\t%d\n " , $row -> employee_id , $row -> surname , $row -> salary);
}


// 使用fetch_row返回结果集

$sql = " SELECT employee_id, surname, salary FROM employees WHERE salary>95000 AND department_id=1 AND status='G' " ;
$results = $mysqli -> query( $sql );
if  ( $mysqli -> errno) {  die  ( $mysqli -> errno . "   " . $mysqli -> error); }
while ( $row = $results -> fetch_row( )) {
     printf ( " %d\t%s\t%d\n " , $row [ 0 ] , $row [ 1 ] , $row [ 2 ]);
}

// 事务管理
$mysqli -> autocommit( FALSE );
$mysqli -> query( " UPDATE account_balance SET balance=balance- $tfer_amount  WHERE account_id= $from_account " );
if  ( $mysqli -> errno) {
     printf ( " transaction aborted: %s\n " , $mysqli -> error);
     $mysqli -> rollback( );
} else  {
     $mysqli -> query( " UPDATE account_balance SET balance=balance+ $tfer_amount  WHERE account_id= $to_account " );
     if  ( $mysqli -> errno) {
         printf ( " transaction aborted: %s\n " , $mysqli -> error);
         $mysqli -> rollback();
    } else  {
         printf ( " transaction succeeded\n " );
         $mysqli -> commit( );
    }
}


// prepare语句

$insert_stmt = $mysqli -> prepare( " INSERT INTO x VALUES(?,?) " ) or  die ( $mysqli -> error);
$insert_stmt -> bind_param( " is " ,   $my_number , $my_string );  # i=integer
for  ( $my_number   =   1 ;  $my_number   <=   10 ;  $my_number ++ ) {
     $my_string = " row  " . $my_number ;
     $insert_stmt -> execute( ) or  die  ( $insert_stmt -> error);
}
$insert_stmt -> close();


// 从prepared语句中返回结果集

$sql = " SELECT employee_id,surname,firstname FROM employees WHERE department_id=? AND status=? IMIT 5 " ;
$stmt   =   $mysqli -> prepare( $sql );
if  ( $mysqli -> errno <> 0 ) { die ( $mysqli -> errno . " :  " . $mysqli -> error);}
$stmt -> bind_param( " is " , $input_department_id , $input_status ) or  die ( $stmt - error);
$stmt -> bind_result(  $employee_id , $surname , $firstname ) or  die ( $stmt -> error);
$input_department_id = 1 ;
$input_status = ' G ' ;
$stmt -> execute( );
if  ( $mysqli -> errno <> 0 ) { die ( $stmt . errno . " :  " . $stmt -> error) ;}
while  ( $stmt -> fetch( )) {
     printf ( " %s %s %s\n " ,   $employee_id , $surname , $firstname );
}


// 获得 Metadata结果集

$metadata   =   $stmt -> result_metadata( );
$field_cnt   =   $metadata -> field_count;
while  ( $colinfo   =   $metadata -> fetch_field( )) {
     printf ( " Column: %s\n " ,   $colinfo -> name);
     printf ( " max. Len: %d\n " ,   $colinfo -> max_length);
     printf ( " Type: %d\n\n " ,   $colinfo -> type);
}


// 调用无结果集的存储过程

$sql   =   ' call simple_stored_proc( ) ' ;
$mysqli -> query( $sql );
if  ( $mysqli -> errno) {
     die ( " Execution failed:  " . $mysqli -> errno . " :  " . $mysqli -> error);
} else  {
     printf ( " Stored procedure execution succeeded\n " );
}


// 返回单个结果集的存储过程

//CREATE PROCEDURE department_list( ) READS SQL DATA SELECT department_name,location from departments;

$sql   =   " call department_list( ) " ;
$results   =   $mysqli -> query( $sql );
if  ( $mysqli -> errno) {
     die ( " Execution failed:  " . $mysqli -> errno . " :  " . $mysqli -> error);
}
while  ( $row   =   $results -> fetch_object( )) {
     printf ( " %s\t%s\n " ,   $row -> department_name ,   $row -> location);
}


// 有输入参数和返回结果集的存储过程

//CREATE PROCEDURE customers_for_rep(in_sales_rep_id INT) READS SQL DATA SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id;

$sql   =   " CALL customers_for_rep(?) " ;
$stmt   =   $mysqli -> prepare( $sql );
if  ( $mysqli -> errno) { die ( $mysqli -> errno . " ::  " . $mysqli -> error);}
$stmt -> bind_param( " i " ,   $in_sales_rep_id );
$in_sales_rep_id   =   1 ;
$stmt -> execute( );
if  ( $mysqli -> errno) { die ( $mysqli -> errno . " :  " . $mysqli -> error);}
$stmt -> bind_result( $customer_id , $customer_name );
while  ( $stmt -> fetch( )) {
     printf ( " %d %s \n " ,   $customer_id , $customer_name );
}


// 输出参数的处理

//CREATE PROCEDURE sp_rep_customer_count(in_emp_id DECIMAL(8,0), OUT out_cust_count INT) NOT DETERMINISTIC READS SQL DATA BEGIN SELECT count(*) INTO out_cust_count FROM customers WHERE sales_rep_id=in_emp_id; END;

$sql = " CALL sp_rep_customer_count(1,@customer_count) " ;
$stmt   =   $mysqli -> prepare( $sql );
if  ( $mysqli -> errno) { die ( $mysqli -> errno . " :  " . $mysqli -> error);}
$stmt -> execute( );
if  ( $mysqli -> errno) { die ( $mysqli -> errno . " :  " . $mysqli -> error);}
$stmt -> close( );
$results   =   $mysqli -> query( " SELECT @customer_count AS customer_count " );
$row   =   $results -> fetch_object( );
printf ( " Customer count=%d\n " , $row -> customer_count);


// 多结果集处理

//CREATE PROCEDURE stored_proc_with_2_results(in_sales_rep_id INT) DETERMINISTIC READS SQL DATA BEGIN SELECT employee_id,surname,firstname FROM employees WHERE employee_id=in_sales_rep_id; SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id; END;

$query   =   " call stored_proc_with_2_results(  $employee_id  ) " ;
if  ( $mysqli -> multi_query( $query )) {
     $result   =   $mysqli -> store_result();
     while  ( $row   =   $result -> fetch_object()) {
         printf ( " %d %s %s\n " , $row -> employee_id , $row -> surname , $row -> firstname);
    }
     $mysqli -> next_result();
     $result   =   $mysqli -> store_result();
     while  ( $row   =   $result -> fetch_object()) {
         printf ( " %d %s \n " , $row -> customer_id , $row -> customer_name);
    }
}


// 不确定结果集数的处理

$query   =   " call stored_proc_with_2_results(  $employee_id  ) " ;
if  ( $mysqli -> multi_query( $query )) {
     do  {
         if  ( $result   =   $mysqli -> store_result( )) {
             while  ( $finfo   =   $result -> fetch_field( )) {
                 printf ( " %s\t " ,   $finfo -> name);
            }
             printf ( " \n " );
             while  ( $row   =   $result -> fetch_row( )) {
                 for  ( $i = 0 ; $i < $result -> field_count; $i ++ ) {
                     printf ( " %s\t " ,   $row [ $i ]);
                }
                 printf ( " \n " );
            }
             $result -> close( );
        }
    }  while  ( $mysqli -> next_result());
}
?>

<? php


// PDO

//连接数据库

$dsn   =   ' mysql:dbname=prod;host=localhost;port=3305 ' ;
$user   =   ' root ' ;
$password   =   ' secret ' ;
try  {
     $dbh   =   new  PDO( $dsn ,   $user ,   $password );
}
catch  (PDOException  $e ) {
     die ( ' Connection failed:  ' . $e -> getMessage( ));
}
print   " Connected\n " ;


// 简单查询

$sql = " CREATE TABLE my_numbers (a_number INT) " ;
$dbh -> exec ( $sql );


$rows = $dbh -> exec ( " INSERT INTO my_numbers VALUES (1), (2), (3) " );
printf ( " %d rows inserted\n " , $rows );


// 错误处理

$sql = " CREATE TABLE my_numbers (a_number INT) " ;
$dbh -> exec ( $sql );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     $error_array = $dbh -> errorInfo( );
     printf ( " SQLSTATE : %s\n " , $error_array [ 0 ]);
     printf ( " MySQL error code : %s\n " , $error_array [ 1 ]);
     printf ( " Message : %s\n " , $error_array [ 2 ]);
}


$sql = " CREATE TABLE my_numbers (a_number INT) " ;
$dbh -> exec ( $sql );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     die ( " Error:  " . implode ( ' :  ' , $dbh -> errorInfo( )) . " \n " );
}


事务管理

$dbh -> beginTransaction( );
$dbh -> exec ( " UPDATE account_balance SET balance=balance- $tfer_amount  WHERE account_id= $from_account " );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     printf ( " transaction aborted: %s\n " , implode ( ' :  ' , $dbh -> errorInfo( )));
     $dbh -> rollback( );
} else  {
     $dbh -> exec ( " UPDATE account_balance SET balance=balance+ $tfer_amount  WHERE account_id= $to_account " );
     if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
         printf ( " transaction aborted: %s\n " , implode ( ' :  ' , $dbh -> errorInfo( )));
         $dbh -> rollback( );
    } else  {
         printf ( " transaction succeeded\n " );
         $dbh -> commit( );
    }
}


// 结果集处理

$sql   =   ' SELECT department_id,department_name FROM departments ' ;
foreach  ( $dbh -> query( $sql )  as   $row ) {
     printf ( " %d \t %s\n " , $row [ ' department_id ' ] , $row [ ' department_name ' ]);
}


$sql   =   ' SELECT department_id,department_name FROM departments ' ;
foreach  ( $dbh -> query( $sql )  as   $row ) {
     printf ( " %d \t %s\n " , $row [ 0 ] , $row [ 1 ]);
}


// prepare语句

$sql   =   ' INSERT INTO my_numbers VALUES(1),(2),(3) ' ;
$sth   =   $dbh -> prepare( $sql );
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));


// 从prepare语句中返回结果集

$sql = ' SELECT department_id,department_name FROM departments LIMIT 5 ' ;
$sth = $dbh -> prepare( $sql ) or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
while ( $row = $sth -> fetch( )) {
     printf ( " %d \t %s \n " , $row [ ' department_id ' ] , $row [ ' department_name ' ]);
}

// 为prepare绑定数据

$sql = ' SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=:sales_rep_id AND contact_surname=:surname ' ;
$sth   =   $dbh -> prepare( $sql );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     die ( " Error:  " . implode ( ' :  ' , $dbh -> errorInfo( )) . " \n " );
}
$sth -> bindParam( ' :sales_rep_id ' ,   $sales_rep_id ,  PDO :: PARAM_INT);
$sth -> bindParam( ' :surname ' ,   $surname ,  PDO :: PARAM_STR ,   30 );
$sales_rep_id = 41 ;
$surname   =   ' SMITH ' ;
$sth -> execute( );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     die ( " Error:  " . implode ( ' :  ' , $dbh -> errorInfo( )) . " \n " );
}
while ( $row = $sth -> fetch( )) {
     printf ( " %d %s \n " , $row [ ' customer_id ' ] , $row [ ' customer_name ' ]);
}


// 查询 metadata

$sth   =   $dbh -> prepare( " SELECT employee_id,surname,date_of_birth FROM employees where employee_id=1 " );
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
$cols = $sth -> columnCount( );
for  ( $i = 0 ;  $i < $cols  ; $i ++ ) {
     $metadata = $sth -> getColumnMeta( $i );
     printf ( " \nDetails for column %d\n " , $i + 1 );
     printf ( "  Name: %s\n " , $metadata [ " name " ]);
     printf ( "  Datatype: %s\n " , $metadata [ " native_type " ]);
     printf ( "  Length: %d\n " , $metadata [ " len " ]);
     printf ( "  Precision: %d\n " , $metadata [ " precision " ]);
}


// 执行简单存储过程

$sql = ' call simple_stored_proc( ) ' ;
$dbh -> exec ( $sql );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     die ( " Error:  " . implode ( ' :  ' , $dbh -> errorInfo( )) . " \n " );
}


// 单个结果集的存储过程

$sql   =   ' call stored_proc_with_1_result( ) ' ;
foreach  ( $dbh -> query( $sql )  as   $row ) {
     printf ( " %d \t %s\n " , $row [ 0 ] , $row [ 1 ]);
}


$sql = ' call stored_proc_with_1_result( ) ' ;
$sth = $dbh -> prepare( $sql ) or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
while ( $row = $sth -> fetch( )) {
     printf ( " %s \t %s \n " , $row [ ' department_name ' ] , $row [ ' location ' ]);
}


// 绑定输入参数

$sql = ' CALL customers_for_rep(:sales_rep_id,:surname) ' ;
$sth   =   $dbh -> prepare( $sql );
if  ( $dbh -> errorCode( ) <> ' 00000 ' ) {
     die ( " Error:  " . implode ( ' :  ' , $dbh -> errorInfo( )) . " \n " );
}
$sth -> bindParam( ' :sales_rep_id ' ,   $sales_rep_id ,  PDO :: PARAM_INT);
$sth -> bindParam( ' :surname ' ,   $surname ,  PDO :: PARAM_STR ,   30 );
$sales_rep_id = 41 ;
$surname   =   ' SMITH ' ;
$sth -> execute( );


// 多结果集处理

//CREATE PROCEDURE stored_proc_with_2_results(in_sales_rep_id INT) DETERMINISTIC READS SQL DATA BEGIN SELECT employee_id,surname,firstname FROM employees WHERE employee_id=in_sales_rep_id; SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id; END;

$sth   =   $dbh -> prepare( " call stored_proc_with_2_results(  $employee_id  ) " );
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
while  ( $row1 = $sth -> fetch( )) {
     printf ( " %d %s %s\n " , $row1 [ ' employee_id ' ] , $row1 [ ' surname ' ] , $row1 [ ' firstname ' ]);
}
$sth -> nextRowset( );
while  ( $row2 = $sth -> fetch( )) {
     printf ( " %d %s \n " , $row2 [ ' customer_id ' ] , $row2 [ ' customer_name ' ]);
}


// 不确定结果集数量的处理

//CREATE PROCEDURE sp_employee_report(in_emp_id decimal(8,0)) READS SQL DATA BEGIN DECLARE customer_count INT; SELECT surname,firstname,date_of_birth FROM employees WHERE employee_id=in_emp_id; SELECT department_id,department_name FROM departments WHERE department_id=(select department_id FROM employees WHERE employee_id=in_emp_id); SELECT count(*) INTO customer_count FROM customers WHERE sales_rep_id=in_emp_id; IF customer_count=0 THEN SELECT 'Employee is not a current sales rep'; ELSE SELECT customer_name,customer_status FROM customers HERE sales_rep_id=in_emp_id; SELECT customer_name,sum(sale_value) FROM sales JOIN customers USING (customer_id) WHERE customers.sales_rep_id=in_emp_id GROUP BY customer_name; END IF;

function  many_results( $dbh ,   $sql_text ) {
     $sth   =   $dbh -> prepare( $sql_text );
     $sth -> execute() or  die ( implode ( ' : ' ,   $sth -> errorInfo( )));
     do  {
         if  ( $sth -> columnCount( )  >   0 ) {  // 是结果集
        //输出字段名
             for  ( $i   =   0 ;  $i   <   $sth -> columnCount( );  $i   ++ ) {
                 $meta   =   $sth -> getColumnMeta( $i );
                 printf ( " %s\t " ,   $meta [ " name " ]);
            }
             printf ( " \n " );
             while  ( $row   =   $sth -> fetch( )) {
                 for  ( $i   =   0 ;  $i   <   $sth -> columnCount( );  $i   ++ ) {
                     printf ( " %s\t " ,   $row [ $i ]);
                }
                 printf ( " \n " );
            }
             printf ( " -------------------\n " );
        }
    }  while  ( $sth -> nextRowset( ));
}


// 输出参数
//CREATE PROCEDURE 'sp_rep_customer_count'(in_emp_id DECIMAL(8,0),OUT out_cust_count INT) READS SQL DATA BEGIN SELECT count(*) AS cust_count INTO out_cust_count FROM customers WHERE sales_rep_id=in_emp_id; END ;

$sql   =   " call sp_rep_customer_count(?,?) " ;
$sth   =   $dbh -> prepare( $sql ) or  die ( implode ( ' : ' ,   $sth -> errorInfo( )));
$sth -> bindParam( 1 , $sales_rep_id , PDO :: PARAM_STR , 4000 );
$sth -> bindParam( 2 , $customer_count ,  PDO :: PARAM_INT | PDO :: PARAM_INPUT_OUTPUT);
$sth -> execute() or  die ( implode ( ' : ' ,   $sth -> errorInfo( )));


// 不使用bindParam获得输出参数

$sql = " call sp_rep_customer_count(1,@customer_count) " ;
$sth   =   $dbh -> prepare( $sql );
$sth -> execute() or  die  ( implode ( ' : ' , $sth -> errorInfo( )));
$sql = " SELECT @customer_count " ;
foreach  ( $dbh -> query( $sql )  as   $row ) {
     printf ( " Customer count=%d\n " , $row [ 0 ]);
}
?>