Mysqli 操作数据库，mysqli操作数据库_PHP教程
mysqli
Mysqli 操作数据库，mysqli操作数据库
每次用到mysql_connect连接数据库的时候都会提示：
<span>1</span> Deprecated: <span>mysql_connect</span>(): The <span>mysql</span> extension is deprecated and will be removed in the future: <span>use</span> mysqli or PDO instead in E:\wamp\www\myquote\includes\<span>mysql_connect</span>.php on line 5
登录后复制
作为一个高要求高完美的男人，实在受不了这提示。躲着他也不行——解决他心里的小石头才能碎掉
那么就先学习下使用Mysqli操作数据库：
1. 与Mysql数据库交互时，首先要建立连接，最后要断开连接，这包括与服务器连接并选择一个数据库，以及最后关闭连接。与Mysql几乎所有的特性一样，这一点可以使用面向对象的方法来完成，也可以采用过程的方式完成。
1.1 创建一个Mysqli的对象
<span>1</span> <?<span>php </span><span>2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span> <span>3</span> ?>
登录后复制
1.2 连接Mysql的主机、用户、密码、数据库
<span>1</span> <?<span>php
</span><span>2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span>3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span>4</span> ?>登录后复制
1.3 创建连接参数的Mysqli对象
<span>1</span> <?<span>php
</span><span>2</span> <span>$mysqli</span>=<span>new</span> mysqli('localhost','root','admin','test');<span>//</span><span>实例化mysqli
</span><span>3</span> <span>//$mysqli->connect('localhost','root','admin','test');</span>
<span>4</span> ?>登录后复制
1.4 更换默认连接的数据库
<span>1</span> <?<span>php
</span><span>2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span>3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span>4</span> <span>$mysqli</span>->select_db('abc');<span>//</span><span>更换连接默认的数据库为abc数据库</span>
<span>5</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>6</span> ?>登录后复制
2 处理连接错误
2.1 如果无法连接Mysql数据库，那么这个页面不太可能继续完成预期的工作，因此，一定要注意监视连接错误并相应地做出反应。Mysqli拓展包含有很多特性可以用来抓取错误信息，例如：mysqli_connect_errno() 和 mysqli_connect_error()方法。
<span>1</span> <span>mysqli_connect_errno</span><span>()函数返回链接数据库返回的错误号。 </span><span>2</span> <span>mysqli_connect_error</span><span>()函数返回连接数据库返回的错误代码。</span>
登录后复制
使用方法：
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>echo</span>'数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>();
</span><span> 6</span> <span>exit</span><span>();
</span><span> 7</span> }<span>else</span><span>{
</span><span> 8</span> <span>echo</span> '数据库连接成功'<span>;
</span><span> 9</span> <span>}
</span><span>10</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>11</span> ?>登录后复制
这里用了面向过程的方法来判断数据库是否连接成功。那么为什么不用面向对象的方法判断呢？我的理解是 判断数据库连接成功 之前首先要证明你要实例化的对象是否可用。。所以用了面向过程的方法来判断。（如果有不对的地方大家也可以一起讨论讨论，我自己也是个小学渣）
2.2 mysqli_error——返回数据库操作错误信息。mysqli_errno——返回数据库操作错误代码
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>echo</span>'数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>();
</span><span> 6</span> <span>exit</span><span>();
</span><span> 7</span> }<span>else</span><span>{
</span><span> 8</span> <span>echo</span> '数据库连接成功<br/>'<span>;
</span><span> 9</span> <span>}
</span><span>10</span> <span>if</span> (!<span>$mysqli</span>->query("SET a=1")) {<span>//</span><span>$mysqli->query()执行sql语句</span>
<span>11</span> <span>echo</span> "错误信息是: ".<span>$mysqli</span>->error.'<br/>'<span>;
</span><span>12</span> <span>echo</span> "错误代码是: ".<span>$mysqli</span>-><span>errno;
</span><span>13</span> <span>}
</span><span>14</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>15</span> ?>登录后复制
输出：
数据库连接成功
错误信息是: Unknown system variable 'a'
错误代码是: 1193
3 与数据库进行交互。
① 绝大多数查询都与创建(Creation)、获取(Retrieval)、更新(Update)和删除(Deletion)任务有关，这些任务统称为CRUD。
② 获取数据网页程序大多数工作都是在获取和格式化所请求的数据。为此，要向数据库发送SELECT查询，再对结果进行迭代处理，将各行输出给浏览器，并按照自己的要求输出。
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>print_r</span>(<span>$result</span>->fetch_row());<span>//</span><span>将结果集的第一行输出</span>
<span>11</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>12</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>13</span> ?>登录后复制
输出：
Array ( [0] => 1 [1] => 秋田嘉 [2] => 123456 )
3.1 解析查询结果
一旦执行了查询并准备好结果集，下面就可以解析获取到的结果行了。你可以使用多个方法来获取各行中的字段，具体选择哪一个方法主要取决于个人喜好，因为只是引用字段的方法有所不同。
3.1.1 将结果集放到对象中由于你可能会使用mysqli的面向对象的语法，所以完全可以采用面向对象的方式管理结果集。
可以使用 fetch_object() 方法来完成。
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>/*</span>
<span>11</span> <span>$row = $result->fetch_object();//将结果集包装成对象
</span><span>12</span> <span>echo $row->user;//输出对象中的一个字段（属性）
</span><span>13</span> <span>*/</span>
<span>14</span> <span>while</span> (!!<span>$row</span> = <span>$result</span>->fetch_object()){<span>//</span><span>//遍历所有的用户名称</span>
<span>15</span> <span>echo</span> <span>$row</span>->user."<br>"<span>;
</span><span>16</span> <span>var_dump</span>(<span>$row</span><span>);
</span><span>17</span> <span>}
</span><span>18</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>19</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>20</span> ?>登录后复制
输出：
秋田嘉
object(stdClass)#3 (3) { ["id"]=> string(1) "1" ["user"]=> string(9) "秋田嘉" ["psw"]=> string(6) "123456" }
刘德华
object(stdClass)#4 (3) { ["id"]=> string(1) "2" ["user"]=> string(9) "刘德华" ["psw"]=> string(6) "654321" }
3.1.2 使用索引数组和关联数组 fetch_array();
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span>
<span>11</span> <span>$row</span> = <span>$result</span>->fetch_array();<span>//</span><span>将结果集包装成数组(索引+关联)</span>
<span>12</span> <span>echo</span> <span>$row</span>[1].'<br/>';<span>//</span><span>输出下标是1的字段（属性）</span>
<span>13</span> <span>var_dump</span>(<span>$row</span><span>);
</span><span>14</span> <span>/*</span>
<span>15</span> <span>while (!!$row = $result->fetch_array()){//遍历全部
</span><span>16</span> <span>echo $row[1]."<br/>";
</span><span>17</span> <span>}</span><span>*/</span>
<span>18</span>
<span>19</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>20</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>21</span> ?>登录后复制
输出：
秋田嘉
array(6) { [0]=> string(1) "1" ["id"]=> string(1) "1" [1]=> string(9) "秋田嘉" ["user"]=> string(9) "秋田嘉" [2]=> string(6) "123456" ["psw"]=> string(6) "123456" }
3.1.3 使用索引数组 fetch_row();
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>$row</span> = <span>$result</span>->fetch_row();<span>//</span><span>将结果集包装成索引数组</span>
<span>11</span> <span>echo</span> <span>$row</span>[2<span>];
</span><span>12</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>13</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>14</span> ?>登录后复制
输出：
123456
3.1.4 使用关联数组 fetch_assoc();
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>$row</span> = <span>$result</span>->fetch_assoc();<span>//</span><span>将结果集包装成关联数组</span>
<span>11</span> <span>echo</span> <span>$row</span>['psw'<span>];
</span><span>12</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>13</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>14</span> ?>登录后复制
输出：
123456
4 确定所选择的行和受影响的行
通常希望能够确定SELECT查询返回的行数，或者受INSERT、UPDATE或DELET查询影响的行数。
我们可以使用num_rows和affected_rows两个属性
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>echo</span> <span>$result</span>->num_rows;<span>//</span><span>当使用查询时，想了解SELECT查询了多少行，可以使用num_rows。</span>
<span>11</span> <span>echo</span>"<br/>"<span>;
</span><span>12</span> <span>echo</span> <span>$mysqli</span>->affected_rows;<span>//</span><span>当使用查询时，想了解SELECT、INSERT、UPDATE、DELETE查询时影响的行数，可以使用affected_rows;注意，它是$mysqli下的属性</span>
<span>13</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>14</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>15</span> ?>登录后复制
输出：
2
2
5 移动指针的操作和获取字段
当你并不想从第一条数据开始获取，或者并不想从第一个字段获取，你可以使用数据指针移动或者字段指针移动的方式调整到恰当的位置。当然，你还可以获取字段的名称及其相关的属性
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span> 8</span> <span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span> 9</span> <span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>10</span> <span>echo</span> <span>$result</span>->field_count;<span>//</span><span>计算有多少条字段</span>
<span>11</span> <span>echo</span>"<br/>";<span>//</span><span>输出 3</span>
<span>12</span> <span>$field</span> = <span>$result</span>->fetch_field();<span>//</span><span>获取字段的名称</span>
<span>13</span> <span>echo</span> <span>$field</span>->name;<span>//</span><span>输出id</span>
<span>14</span> <span>echo</span> '<br/>'<span>;
</span><span>15</span> <span>while</span> (!!<span>$field</span> = <span>$result</span>->fetch_field()) {<span>//</span><span>遍历字段</span>
<span>16</span> <span>echo</span> <span>$field</span>->name.'<br />'<span>;
</span><span>17</span> <span>}
</span><span>18</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>19</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>20</span> ?>登录后复制
输出：
3
id
user
psw
5.1
<?<span>php
</span><span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
}
</span><span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码</span>
<span>$sql</span>='select * from user';<span>//</span><span>创建一句SQL语句</span>
<span>$result</span>=<span>$mysqli</span>->query(<span>$sql</span>);<span>//</span><span>执行sql语句把结果集赋给$result</span>
<span>print_r</span>(<span>$result</span>->fetch_fields());<span>//</span><span>一次性取得字段数组</span>
<span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
?>登录后复制
输出：
<span> 1</span> <span>Array</span> <span> 2</span> <span>( </span><span> 3</span> [0] => stdClass <span>Object</span> <span> 4</span> <span> ( </span><span> 5</span> [name] =><span> id </span><span> 6</span> [orgname] =><span> id </span><span> 7</span> [table] =><span> user </span><span> 8</span> [orgtable] =><span> user </span><span> 9</span> [def] => <span>10</span> [db] =><span> test </span><span>11</span> [catalog] =><span> def </span><span>12</span> [max_length] => 1 <span>13</span> [length] => 11 <span>14</span> [charsetnr] => 63 <span>15</span> [flags] => 49667 <span>16</span> [type] => 3 <span>17</span> [decimals] => 0 <span>18</span> <span> ) </span><span>19</span> <span>20</span> [1] => stdClass <span>Object</span> <span>21</span> <span> ( </span><span>22</span> [name] =><span> user </span><span>23</span> [orgname] =><span> user </span><span>24</span> [table] =><span> user </span><span>25</span> [orgtable] =><span> user </span><span>26</span> [def] => <span>27</span> [db] =><span> test </span><span>28</span> [catalog] =><span> def </span><span>29</span> [max_length] => 9 <span>30</span> [length] => 765 <span>31</span> [charsetnr] => 33 <span>32</span> [flags] => 4097 <span>33</span> [type] => 253 <span>34</span> [decimals] => 0 <span>35</span> <span> ) </span><span>36</span> <span>37</span> [2] => stdClass <span>Object</span> <span>38</span> <span> ( </span><span>39</span> [name] =><span> psw </span><span>40</span> [orgname] =><span> psw </span><span>41</span> [table] =><span> user </span><span>42</span> [orgtable] =><span> user </span><span>43</span> [def] => <span>44</span> [db] =><span> test </span><span>45</span> [catalog] =><span> def </span><span>46</span> [max_length] => 6 <span>47</span> [length] => 765 <span>48</span> [charsetnr] => 33 <span>49</span> [flags] => 4097 <span>50</span> [type] => 253 <span>51</span> [decimals] => 0 <span>52</span> <span> ) </span><span>53</span> <span>54</span> )
登录后复制
5.2
<span>1</span> <span>$result</span>->data_seek(1);<span>//</span><span>移动数据指针</span> <span>2</span> <span>$result</span>->field_seek(1);<span>//</span><span>移动字段指针</span>
登录后复制
6 执行多条SQL语句
有的时候，我们需要在一张页面上同时执行多条SQL语句，之前的方法就是分别创建多个结果集然后使用。但这样资源消耗很大，也不利于管理。 PHP提供了执行多条SQL语句的方法$mysqli->multi_query();
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码
</span><span> 8</span> <span>//创建多条SQL语句</span>
<span> 9</span> <span>$sql</span> .= "SELECT * FROM user;"<span>;
</span><span>10</span> <span>$sql</span> .= "SELECT * FROM photo;"<span>;
</span><span>11</span> <span>$sql</span> .= "SELECT * FROM article"<span>;
</span><span>12</span> <span>if</span> (<span>$mysqli</span>->multi_query(<span>$sql</span>)) {<span>//</span><span>开始执行多条SQL语句</span>
<span>13</span> <span>$result</span> = <span>$mysqli</span>-><span>store_result();
</span><span>14</span> <span>print_r</span>(<span>$result</span>->fetch_array());<span>//</span><span>开始获取第一条SQL语句的结果集</span>
<span>15</span> <span>$mysqli</span>->next_result();<span>//</span><span>将结果集指针移到下一个</span>
<span>16</span> <span>$result</span> = <span>$mysqli</span>-><span>store_result();
</span><span>17</span> <span>print_r</span>(<span>$result</span>-><span>fetch_array());
</span><span>18</span> <span>$mysqli</span>-><span>next_result();
</span><span>19</span> <span>$result</span> = <span>$mysqli</span>-><span>store_result();
</span><span>20</span> <span>print_r</span>(<span>$result</span>-><span>fetch_array());
</span><span>21</span> } <span>else</span><span> {
</span><span>22</span> <span>echo</span> 'sql语句有误！'<span>;
</span><span>23</span> <span>}
</span><span>24</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>25</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>26</span> ?>登录后复制
7 执行数据库事务
事务(transaction)是作为整个一个单元的一组有序的数据库操作。如果一组中的所有操作都成功，则认为事务成功，即使只有一个失败操作，事务也不成功。如果所有操作成功完成，事务则提交(commit)，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚(roll back)，该事务所有操作的影响都将取消。首先，您的MySQL是InnoDB或BDB引擎的一种，一般来说，你安装了AppServ的集成包，你选择InnoDB的引擎的数据库即可。如果你建立的表不是InnoDB，可以在phpmyadmin里修改
<span> 1</span> <?<span>php
</span><span> 2</span> <span>$mysqli</span>=<span>new</span> mysqli();<span>//</span><span>实例化mysqli</span>
<span> 3</span> <span>$mysqli</span>->connect('localhost','root','admin','test'<span>);
</span><span> 4</span> <span>if</span>(<span>mysqli_connect_error</span><span>()){
</span><span> 5</span> <span>exit</span>('数据库连接错误,错误信息是.'.<span>mysqli_connect_error</span><span>());
</span><span> 6</span> <span>}
</span><span> 7</span> <span>$mysqli</span>->set_charset("UTF8");<span>//</span><span>设置数据库编码
</span><span> 8</span> <span>//首先你必须关闭自动提交数据</span>
<span> 9</span> <span>$mysqli</span>->autocommit(<span>false</span><span>);
</span><span>10</span> <span>//</span><span>创建一个SQL语句，必须同时运行成功，不能出现一个成功，一个失败</span>
<span>11</span> <span>$sql</span> .= "UPDATE `friend` SET state=state+5 WHERE id=1;"<span>;
</span><span>12</span> <span>$sql</span> .= "UPDATE `flower` SET tg_flower=flower-5 WHERE id=1;"<span>;
</span><span>13</span> <span>//</span><span>执行两条SQL语句</span>
<span>14</span> <span>if</span> (<span>$mysqli</span>->multi_query(<span>$sql</span><span>)) {
</span><span>15</span> <span>//</span><span>获取第一条SQL一影响的行数</span>
<span>16</span> <span>$success</span> = <span>$mysqli</span>->affected_rows == 1 ? <span>true</span> : <span>false</span><span>;
</span><span>17</span> <span>//</span><span>下移，第二条SQL</span>
<span>18</span> <span>$mysqli</span>-><span>next_result();
</span><span>19</span> <span>//</span><span>获取第二条SQL影响的行数</span>
<span>20</span> <span>$success2</span> = <span>$mysqli</span>->affected_rows == 1 ? <span>true</span> : <span>false</span><span>;
</span><span>21</span> <span>//</span><span>判断是否都正常通过了，两个SQL</span>
<span>22</span> <span>if</span> (<span>$success</span> && <span>$success2</span><span>) {
</span><span>23</span> <span>$mysqli</span>-><span>commit();
</span><span>24</span> <span>echo</span> '完美提交！'<span>;
</span><span>25</span> } <span>else</span><span> {
</span><span>26</span> <span>$mysqli</span>-><span>rollback();
</span><span>27</span> <span>echo</span> '程序出现异常！'<span>;}
</span><span>28</span> } <span>else</span><span> {
</span><span>29</span> <span>echo</span> "SQL语句有误：".<span>$mysqli</span>->errno.<span>$mysqli</span>-><span>error;
</span><span>30</span> <span>}
</span><span>31</span> <span>//</span><span>最后还必须开启自动提交</span>
<span>32</span> <span>$mysqli</span>->autocommit(<span>true</span><span>);
</span><span>33</span> <span>$result</span>->free();<span>//</span><span>释放查询内存(销毁)</span>
<span>34</span> <span>$mysqli</span>->close();<span>//</span><span>别忘了关闭你的"小资源";</span>
<span>35</span> ?>登录后复制
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
刺客信条阴影：贝壳谜语解决方案
3 周前
By DDD
Windows 11 KB5054979中的新功能以及如何解决更新问题
2 周前
By DDD
刺客信条阴影 - 如何找到铁匠，解锁武器和装甲定制
1 个月前
By DDD
在哪里可以找到原子中的起重机控制钥匙卡
3 周前
By DDD
<🎜>：死铁路 - 如何完成所有挑战
3 周前
By DDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
PHP Fatal error: Call to undefined function mysqli_connect()的解决方法
Jun 23, 2023 am 09:40 AM
在使用PHP编写Web应用程序时，经常会使用MySQL数据库来存储数据。PHP提供了一种与MySQL数据库进行交互的方法，称为MySQLi。然而，有时在使用MySQLi时，会遇到一个错误信息，如以下所示：PHPFatalerror:Calltoundefinedfunctionmysqli_connect()这个错误信息意味着PHP无法找到my
PHP PDO 与 mysqli：比较和对比
Feb 19, 2024 pm 12:24 PM
PDOPDO是一个面向对象的数据库访问抽象层，它为PHP提供了一个统一的接口，允许您使用相同的代码与不同的数据库（如Mysql、postgresql、oracle）进行交互。PDO隐藏了底层数据库连接的复杂性，简化了数据库操作。优缺点优点：统一接口，支持多种数据库简化数据库操作，降低开发难度提供预处理语句，提高安全性支持事务处理缺点：性能可能比原生扩展稍低依赖外部库，可能会增加开销演示代码使用PDO连接mysql数据库：$db=newPDO("mysql:host=localhost;dbnam
php无法连接mysqli怎么办
Nov 09, 2022 am 10:07 AM
php无法连接mysqli的解决办法：1、打开“php.ini”文件；2、找到“mysqli.reconnect”；3、将“mysqli.reconnect = OFF”改成“mysqli.reconnect = on”即可。
PHP Warning: mysqli_connect(): (HY000/2002): Connection refused的解决方法
Jun 23, 2023 am 08:54 AM
如果你使用PHP连接MySQL数据库时遇到了以下错误提示：PHPWarning:mysqli_connect():(HY000/2002):Connectionrefused那么你可以尝试按照下面的步骤来解决这个问题。确认MySQL服务是否正常运行首先应该检查MySQL服务是否正常运行，如果服务未运行或者启动失败，就可能会导致连接被拒绝的错误。你可
mysql的运行文件是什么
Apr 11, 2023 am 10:38 AM
mysql的运行文件是mysqld；mysqld是一个可执行文件，代表着Mysql服务器程序，执行这个文件可以直接启动一个服务器进程；而mysqld_safe是一个启动脚本，它会间接调用mysqld，并且还会顺带启动一个监控进程。
在PHP中使用MySQLi建立数据库连接的详尽教程
Jun 04, 2024 pm 01:42 PM
如何在PHP中使用MySQLi建立数据库连接：包含MySQLi扩展（require_once）创建连接函数（functionconnect_to_db）调用连接函数（$conn=connect_to_db()）执行查询（$result=$conn->query()）关闭连接（$conn->close()）
PHP Fatal error: Call to undefined method mysqli::prepare()的解决方法
Jun 23, 2023 am 11:21 AM
当使用mysqli扩展来连接和操作MySQL数据库时，有时会遇到PHPFatalerror:Calltoundefinedmethodmysqli::prepare()的错误。这个错误通常是由以下几个原因引起的：PHP对mysqli扩展的支持不足；mysqli扩展没有正确加载或配置；PHP代码存在语法错误；MySQL服务器没有正确配置或正在运行
PHP Fatal error: Call to undefined function mysqli_stmt_bind_param()的解决方法
Jun 23, 2023 am 10:43 AM
在使用PHP开发网站时，数据库的操作是非常常见的。而MySQLi是PHP中常用的操作MySQL数据库的扩展，提供了比较完备的面向对象接口、过程化接口，以及支持预处理语句的操作。但是有时候我们在使用mysqli的预处理语句时，会遇到这样的错误：PHPFatalerror:Calltoundefinedfunctionmysqli_stmt_bin
