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”);
}
?>


错误检查

1)
    if ($mysqli->query($sql) <> TRUE) {
         printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
   }
?>
2)
    $mysqli->query($sql) or printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
?>
3)
   $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());
}
?>
 

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]);
}
?>