首页 »PHP教程 » mysqli:PDO MYSQLI简单应用举例 »正文
mysqli:PDO MYSQLI简单应用举例
来源: 发布时间:星期一, 2009年1月12日 浏览:37次 评论:0
Mysqli 连接数据库 $mysqli = mysqli("localhost", "root", "secret", "test"); (mysqli_connect_errno( )) { prf("Connect failed: %sn", mysqli_connect_error( )); exit ( ); } { prf("Connect succeededn"); } ?> 检查 1) ($mysqli->query($sql) <> TRUE) { prf("Statement failed %d: (%s) %sn" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error); } ?> 2) $mysqli->query($sql) or prf("Statement failed %d: (%s) %sn" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error); ?> 3) $mysqli->query($sql); ($mysqli->errno <> 0 ) { prf("Statement failed %d: (%s) %sn" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error); } ?> 简单无返回查询 $mysqli->query("CREATE TABLE guy_1 (guys_egers INT)"); ($mysqli->errno <> 0 ) { prf("Statement failed %d: (%s) %sn" ,$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); ($mysqli->errno) { die ($mysqli->errno." ".$mysqli->error); } while($row=$results->fetch_object( )) { prf("%dt%st%dn",$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); ($mysqli->errno) { die ($mysqli->errno." ".$mysqli->error); } while($row=$results->fetch_row( )) { prf("%dt%st%dn",$row[0],$row[1],$row[2]); } ?> 事务管理 $mysqli->autocommit(FALSE); $mysqli->query("UPDATE account_balance SET balance=balance-$tfer_amount WHERE account_id=$from_account"); ($mysqli->errno) { prf("transaction aborted: %sn",$mysqli->error); $mysqli->rollback( ); } { $mysqli->query("UPDATE account_balance SET balance=balance+$tfer_amount WHERE account_id=$to_account"); ($mysqli->errno) { prf("transaction aborted: %sn",$mysqli->error); $mysqli->rollback( ); } { prf("transaction succeededn"); $mysqli->commit( ); } } ?> prepare语句 $insert_stmt=$mysqli->prepare("INSERT INTO x VALUES(?,?)") or die($mysqli->error); $insert_stmt->bind_param("is", $my_number,$my_); #i=eger for ($my_number = 1; $my_number <= 10; $my_number) { $my_="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); ($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( ); ($mysqli->errno<>0) {die($stmt.errno.": ".$stmt->error) ;} while ($stmt->fetch( )) { prf("%s %s %sn", $employee_id,$surname,$firstname); } ?> 获得 Metadata结果集 $metadata = $stmt->result_metadata( ); $field_cnt = $metadata->field_count; while ($colinfo = $metadata->fetch_field( )) { prf("Column: %sn", $colinfo->name); prf("max. Len: %dn", $colinfo->max_length); prf("Type: %dnn", $colinfo->type); } ?> 无结果集 存储过程 $sql = 'call simple_stored_proc( )'; $mysqli->query($sql); ($mysqli->errno) { die("Execution failed: ".$mysqli->errno.": ".$mysqli->error); } { prf("Stored procedure execution succeededn"); } ?>
返回单个结果集 存储过程 CREATE PROCEDURE department_list( ) READS SQL DATA SELECT department_name,location from departments; $sql = "call department_list( )"; $results = $mysqli->query($sql); ($mysqli->errno) { die("Execution failed: ".$mysqli->errno.": ".$mysqli->error); } while ($row = $results->fetch_object( )) { prf("%st%sn", $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); ($mysqli->errno) {die($mysqli->errno.":: ".$mysqli->error);} $stmt->bind_param("i", $in_sales_rep_id); $in_sales_rep_id = 1; $stmt->execute( ); ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);} $stmt->bind_result($customer_id,$customer_name); while ($stmt->fetch( )) { prf("%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); ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);} $stmt->execute( ); ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);} $stmt->close( ); $results = $mysqli->query("SELECT @customer_count AS customer_count"); $row = $results->fetch_object( ); prf("Customer count=%dn",$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 )"; ($mysqli->multi_query($query)) { $result = $mysqli->store_result( ); while ($row = $result->fetch_object( )) { prf("%d %s %sn",$row->employee_id,$row->surname,$row->firstname); } $mysqli->next_result( ); $result = $mysqli->store_result( ); while ($row = $result->fetch_object( )) { prf("%d %s n",$row->customer_id,$row->customer_name); } } ?> 不确定结果集数 处理 $query = "call stored_proc_with_2_results( $employee_id )"; ($mysqli->multi_query($query)) { do { ($result = $mysqli->store_result( )) { while ($finfo = $result->fetch_field( )) { prf("%st", $finfo->name); } prf("n"); while ($row = $result->fetch_row( )) { for ($i=0;$i<$result->field_count;$i) { prf("%st", $row[$i]); } prf("n"); } $result->close( ); } } while ($mysqli->next_result); } ?>
PDO 连接数据库 $dsn = 'mysql:dbname=prod;host=localhost;port=3305'; $user = 'root'; $password = 'secret'; try { $dbh = PDO($dsn, $user, $password); } catch (PDOException $e) { die('Connection failed: '.$e->getMessage( )); } pr "Connectedn"; ?> 简单查询 $sql="CREATE TABLE my_numbers (a_number INT)"; $dbh->exec($sql); ?> $rows=$dbh->exec("INSERT INTO my_numbers VALUES (1), (2), (3)"); prf("%d rows insertedn",$rows); ?> 处理 $sql="CREATE TABLE my_numbers (a_number INT)"; $dbh->exec($sql); ($dbh->errorCode( )<>'00000') { $error_.gif' />=$dbh->errorInfo( ); prf("SQLSTATE : %sn",$error_.gif' />[0]); prf("MySQL error code : %sn",$error_.gif' />[1]); prf("Message : %sn",$error_.gif' />[2]); } ?> $sql="CREATE TABLE my_numbers (a_number INT)"; $dbh->exec($sql); ($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"); ($dbh->errorCode( )<>'00000') { prf("transaction aborted: %sn",implode(': ',$dbh->errorInfo( ))); $dbh->rollback( ); } { $dbh->exec("UPDATE account_balance SET balance=balance+$tfer_amount WHERE account_id=$to_account"); ($dbh->errorCode( )<>'00000') { prf("transaction aborted: %sn",implode(': ',$dbh->errorInfo( ))); $dbh->rollback( ); } { prf("transaction succeededn"); $dbh->commit( ); } } ?>
结果集处理 $sql = 'SELECT department_id,department_name FROM departments'; foreach ($dbh->query($sql) as $row) { prf("%d t %sn",$row['department_id'],$row['department_name']); } ?> $sql = 'SELECT department_id,department_name FROM departments'; foreach ($dbh->query($sql) as $row) { prf("%d t %sn",$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( )) { prf("%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); ($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( ); ($dbh->errorCode( )<>'00000') { die("Error: ".implode(': ',$dbh->errorInfo( ))."n"); } while($row=$sth->fetch( )) { prf("%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); prf("nDetails for column %dn",$i+1); prf(" Name: %sn",$metadata["name"]); prf(" Datatype: %sn",$metadata["native_type"]); prf(" Length: %dn",$metadata["len"]); prf(" Precision: %dn",$metadata["precision"]); } ?> 执行简单存储过程 $sql='call simple_stored_proc( )'; $dbh->exec($sql); ($dbh->errorCode( )<>'00000') { die("Error: ".implode(': ',$dbh->errorInfo( ))."n"); } ?> 单个结果集 存储过程 $sql = 'call stored_proc_with_1_result( )'; foreach ($dbh->query($sql) as $row) { prf("%d t %sn",$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( )) { prf("%s t %s n",$row['department_name'],$row['location']); } ?>
绑定输入参数 $sql='CALL customers_for_rep(:sales_rep_id,:surname)'; $sth = $dbh->prepare($sql); ($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( )) { prf("%d %s %sn",$row1['employee_id'],$row1['surname'],$row1['firstname']); } $sth->nextRow( ); while ($row2=$sth->fetch( )) { prf("%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 { ($sth->columnCount( ) > 0) { //是结果集 //输出字段名 for ($i = 0; $i < $sth->columnCount( ); $i ) { $meta = $sth->getColumnMeta($i); prf("%st", $meta["name"]); } prf("n"); while ($row = $sth->fetch( )) { for ($i = 0; $i < $sth->columnCount( ); $i ) { prf("%st", $row[$i]); } prf("n"); } prf("-------------------n"); } } while ($sth->nextRow( )); } ?> 输出参数 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) { prf("Customer count=%dn",$row[0]); } ?>
相关文章
读者评论
发表评论
|
|