PHP mysqli(数据库操作)

    PHP5.0开始,不仅可以使用早期的mysql数据库扩展函数,还能使用新扩展的mysqli技术实现与mysql数据库的信息交流,PHP的mysqli扩展被封装在在一个类中,它是一种面向对象技术,只能在PHP5和MYSQL4,1或更高的版本才能使用,(i)表示该进,使用mysqli,执行速度更快,更方便,更高效,也可以使数据库访问更安全(因为用类模式)

 

使用mysqli 简单流程

-------------------------------------------------

1.设置PHP.ini 配置文件

extension=php_mysqli.dll

 

 

2.连接mysqli

  建立MYSQL配置文件 config.ini.php

  config.ini.php  内容如下:

<?php 

  $dbhost ="locallhost";

  $dbuser = "hehehe";

  $dbpwd = "123456";

  $dbname = "Student";

  $charName = "gbk2312"; 设置查询字符集gbk,gbk2312,utf-8

?>

 

 

(用mysqli链接MYSQL数据库)

requery_once("config.ini.php");

$mysqliObj = mysqli($dbhost,$dbuser,$dbpwd,$dbname);
if(mysqli_connect_errno()){
 echo "连接失败".mysqli_connect_error();
 exit();
}
$mysqliObj->query("set name $charName");

 

查询

-----------------------------------------------------

(单条查询)

$sql = "drop table  if exists user;";

$mysqliObj->query($sql);

 

(多条查询)

$musqliObj->multip_query($sql)

 

 

 

返回执行$sql受影响的行数()

---------------------------------------------------- 

if($mysqliObj->query($sql))

echo $mysqliObj->affected_rows;

 

insert 插入时,返回插入的id (很有用) 

---------------------------------------------------

 $num = $mysqliObj->insert_id;

 

 

 

 

 

三种处理查询结果

-------------------------------------------------------

 $sql = "select * from user";
 $result = $mysqli->query($sql);

 

(1)fetch_row()   返回索引数组

fetch_row()
while(list($id,$name,$pwd)=$result->fetch_row()){
echo "id: ".$id." name:".$name."  pwd:".$pwd."<br>";
 
}

(2)fetch_assoc()  返回关联数组

fetch_assoc()
while ($row = $result->fetch_assoc()){
 echo "id:".$row["userId"]." name:".$row["userName"]."  pwd:".$row["password"]."<br>";
}

 

(3)fetch_object()返回对象

while($row = $result->fetch_object()){
echo "id:".$row->userId." name:".$row->uerName."  pwd:".$row->password."<br>";

 
}

 

关联数组,就是嵌套的数组 如:

<?php
$data = array(
          array('name' => 'John Smith', 'home' => '555-555-5555',
                'cell' => '666-555-5555', 'email' => 'john@myexample.com'),
          array('name' => 'Jack Jones', 'home' => '777-555-5555',
                'cell' => '888-555-5555', 'email' => 'jack@myexample.com'),
          array('name' => 'Jane Munson', 'home' => '000-555-5555',
                'cell' => '123456', 'email' => 'jane@myexample.com')
        );

?>

 

使用 mysqli_stmt 类

-----------------------------------------------

mysql4.1版本开始提供一种预处理(prepared statement的机制,它可以见整个命令向MYSQL服务器发送一次,以后只有参数变化,MYSQL只需要对命令做一次的分析就够了,大大减少需要传输的数据量,还提高了命令的处理效率(注,在不需要连接时应该立刻关闭close())

 

步骤:

1,预备SQL命令

  $sql = "insert into user(name,pwd) values(?,?)";

  $stms = $mysqli->prepare($sql);

2.绑定数据

  $stms->bind_param('ss',$name,$pwd);   (注意'ss':其要与后面的变量对应($name,$pwd)

                                           i  所有inerger类型

                                           d  double 或float 类型

                                           s  所有其他类型包括字符串

                                           b  二进制(blob,二进制字节串)

                                         

                                         )

  $name = "huang";

  $pwd = "123456";

3.执行

  $stms->execute();

[4.在绑定执行另一组数据  

  $name = "he";

  $pwd = "666666";

  $stms->execute();

 ]

5.关闭

  $stmt->close();

  $mysqli->close();

 

(其他有用参数)

 $num = $stmt->affected_rows;      受影响的行数

 $id  = $stmt->insert_id;          当是插入命令时,返回插入的行id(自动)

 

 

 

事务处理

-----------------------------------------------

在默认情况下,MYSQL是以自动提交(autocommit)模式运行,这就意味着所执行的每一个语句都将立即写入数据库中,但如果使用事务安全的表格类型,是不希望自动提交的行为的

事务处理

当执行多条MYSQL命令时,当然希望当其中一条命令出错时,所有的命令都不执行,返回执行命令之前的状态

这就用到事务了

 

 

 简单运用事务流程

1.写好SQL命令

  $sql1 = "insert user(name) values('huang','123456')";

  $sql2 = "update account set number = number+1";

2.关闭MYSQL事务处理的自动提交模式

  $mysqli->cutocommit(0);

3.试执行命令 

  $success = true;

  $res1 = $mysqli->query($sql1);

  if(!$res1 or $mysqli->affected_rows !=1){

     $success = false;

  }

  $res2 = $mysqli->query($sql2);

  if(!$res2 or $mysqli->affected_rows !=1){

     $success = false;

  }

4.查看执行情况,都成功执行,有失败回滚初态

  if($success ){

     $mysqli->commit();

     echo "执行成功";

  }else{

     $mysqli->rollback();

     echo "执行失败";

  }

5.恢复MYSQL事务处理的自动提交模式

  $mysqli->cutocommit(1);

  $mysqli->close();

 

 

 

mysql 目前只有InnDB和BDB两种数据包类型才支持事务 

InnoDB最快

(创建InnDB类型表)

create table user(

id int(10) not null auto_increment,

name varchar(50) not null,

pwd varchar(50) not null,

primary key(id)

)type=InnoDB

发布了5 篇原创文章 · 获赞 3 · 访问量 6万+
展开阅读全文

使用PHP MySQLi准备语句进行动态SQL连接

04-17

<div class="post-text" itemprop="text"> <p>I am writing a PHP prepared statement using MySQLi prepared statements where the first bound parameter is a user's ID and the second parameter can be one of 5 different pieces of SQL code (the <strong>$redeemLimitCheckRange</strong> variable) based on other conditions represented as a string. I am having trouble binding the second parameter.</p> <p>I have tried binding the SQL code as a string, have tried using the SQL CONCAT() function, but neither seem to work.</p> <pre><code><?php if($dbRedeemStart != '' && $dbRedeemEnd != ''){ $redeemLimitCheckRange = "AND DATE(chg_date) >='$dbRedeemStart' AND DATE(chg_date) <= '$dbRedeemEnd'"; else if($dbRedeemStart != '' && $dbRedeemEnd == ''){ $redeemLimitCheckRange = "AND DATE(chg_date) >= '$dbRedeemStart'"; else if($dbRedeemStart == '' && $dbRedeemEnd != ''){ $redeemLimitCheckRange = "AND DATE(chg_date) <= '$dbRedeemEnd'"; }else{ $redeemLimitCheckRange = ""; } $sql = "SELECT COUNT(recId) totalRedeem FROM transactions WHERE userId = ? AND `type` = 'Redeemed' ?"; $stmt = mysqli_stmt_init($connection); if(!mysqli_stmt_prepare($stmt, $sql)){ error_log("mysqli_stmt_prepare failure", 0); }else{ mysqli_stmt_bind_param($stmt, "is", $usrId, $redeemLimitCheckRange); mysqli_stmt_execute($stmt); $rslt = mysqli_stmt_get_result($stmt); mysqli_stmt_close($stmt); } ?> </code></pre> <p>Currently, php is failing on the mysqli_stmt_prepare($stmt, $sql) line and throwing an error.</p> </div> 问答

PHP mysqli在调用存储过程时没有捕获一些错误

02-12

<div class="post-text" itemprop="text"> <p>We're building a production PHP-MySQL application, and want MySQL stored procedures to be the central bullet-proof gateway to the database. Duplicate keys, table not found, server instance going down, etc all and any kind of error needs to be trapped and conveyed to the calling PHP web-based UI, and transaction rolled back in the stored proc upon such errors.</p> <p>I am using PHP mysqli and calling a stored procedure as follows:</p> <pre><code>$stmt = mysqli_prepare($db, "call my_stored_proc(?, ?, ?, @ptid)"); if ($stmt && mysqli_stmt_bind_param($stmt, "sss", 'p1', 'p2', 'p3') && mysqli_stmt_execute($stmt) && mysqli_stmt_close($stmt)) { echo "All fine!" } else { echo mysqli_error($db); db_disconnect($db); exit; } </code></pre> <p>The stored procedure does some basic validation and signals a user-defined condition if the validation fails. And sure enough, my PHP code is able to catch and see those non-database (eg. formatting) validation errors arising from the stored procedure. After the non-database validations pass, the stored procedure goes on to do a database-related validation and if those pass it inserts a row in a table, and passes the ID in the last OUT parameter.</p> <p>My problem is that if this insert fails (say, bcoz duplicate key error, or table not found error), my PHP code is simply <strong>not</strong> catching the error! It prints "All fine"!</p> <p>Why is that? What am I missing?</p> <p>I want my invocation of the stored proc to be bullet-proof, and all errors raised by the stored proc should be trappable in PHP.</p> <p>FYI: If I call the stored proc from a mysql client (like MySQL Workbench or the mysql client on Linux), the errors are correctly reported.</p> <p>LATER EDITS: FYI, the stored procedure code is simply:</p> <pre><code>delimiter $$ drop procedure if exists my_stored_proc $$ create procedure my_stored_proc ( in p_name VARCHAR(31), in p_notes VARCHAR(510), in p_created_by VARCHAR(31), out p_pt_id INT ) begin declare custom_exception condition for sqlstate '45000'; declare l_retval boolean; declare l_right_now datetime default now(); select p_name regexp '^[[:space:]]*$' into l_retval; if l_retval then signal custom_exception set message_text = 'NAME cannot be blank.'; end if; select p_name regexp '[^0-9_]' into l_retval; if l_retval then signal custom_exception set message_text = 'Invalid NAME.'; end if; call validate_user_in_db(p_created_by, true, l_retval); if not l_retval then signal custom_exception set message_text = 'Invalid CREATED_BY user.'; end if; insert into some_table ( NAME, NOTES, CREATED_BY, CREATED_ON ) values ( p_name, p_notes, p_created_by, l_right_now ); set p_pt_id = last_insert_id(); end $$ delimiter ; </code></pre> <p>EVEN LATER UPDATE: The weird thing is, if I comment out the call to <code>validate_user_in_db</code> in the above stored proc, things work fine and errors are correctly trapped (eg. duplicate key, etc) in PHP.</p> <p>FYI: <code>validate_user_in_db</code> does the following:</p> <pre><code>create procedure validate_user_in_db (in p_user VARCHAR(127), in p_active_only boolean, out p_retval boolean) begin set p_retval = false; if p_active_only then select sql_calc_found_rows 'x' from SOME_USERS_TABLE where username = p_user and active = true limit 1; else select sql_calc_found_rows 'x' from SOME_USERS_TABLE where username = p_user limit 1; end if; set @l_num_rows = found_rows() ; if @l_num_rows = 1 then set p_retval = true; end if; end $$ </code></pre> <p>Sorry for the long post. But I thought I'd give the full picture.</p> <p>What am I missing? Why is my PHP code not getting back errors if the call to <code>validate_user_in_db</code> is enabled? Is <code>validate_user_in_db</code> changing some state permanently? Is the sql_calc_found_rows keyword messing things up?</p> <p>FYI: This is PHP 7.3 and MySQL 5.6</p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览