时间:2021-07-01 10:21:17 帮助过:14人阅读
查询所在的行
- <span style="color: #008080;"> 1</span>
- <span style="color: #008080;"> 2</span> <span style="color: #800080;">$city</span> = "宿迁2"<span style="color: #000000;">;
- </span><span style="color: #008080;"> 3</span>
- <span style="color: #008080;"> 4</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> 创建准备好的语句 "SELECT District FROM City WHERE Name=?") </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;"> 5</span> <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$stmt</span> = <span style="color: #800080;">$mysqli</span>->prepare("SELECT District FROM tree WHERE Name=?"<span style="color: #000000;">)) {
- </span><span style="color: #008080;"> 6</span>
- <span style="color: #008080;"> 7</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> bind parameters for markers </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;"> 8</span> <span style="color: #800080;">$stmt</span>->bind_param("s", <span style="color: #800080;">$city</span><span style="color: #000000;">);
- </span><span style="color: #008080;"> 9</span>
- <span style="color: #008080;">10</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> execute query </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;">11</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">execute();
- </span><span style="color: #008080;">12</span>
- <span style="color: #008080;">13</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> 绑定结果变量 </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;">14</span> <span style="color: #800080;">$stmt</span>->bind_result(<span style="color: #800080;">$id</span><span style="color: #000000;">);
- </span><span style="color: #008080;">15</span>
- <span style="color: #008080;">16</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> fetch value </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;">17</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">fetch();
- </span><span style="color: #008080;">18</span>
- <span style="color: #008080;">19</span> <span style="color: #008080;">printf</span>("%s 在区域 %s\n", <span style="color: #800080;">$city</span>, <span style="color: #800080;">$id</span><span style="color: #000000;">);
- </span><span style="color: #008080;">20</span>
- <span style="color: #008080;">21</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> close statement </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;">22</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">close();
- </span><span style="color: #008080;">23</span> <span style="color: #000000;">}
- </span><span style="color: #008080;">24</span>
- <span style="color: #008080;">25</span>
执行一个查询
- <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$mysqli</span>->multi_query(<span style="color: #800080;">$sql</span><span style="color: #000000;">))
- {
- </span><span style="color: #0000ff;">do</span><span style="color: #000000;">{
- </span><span style="color: #800080;">$res</span> = <span style="color: #800080;">$mysqli</span>-><span style="color: #000000;">store_result();
- </span><span style="color: #008080;">var_dump</span>(<span style="color: #800080;">$res</span>); <span style="color: #008000;">//</span><span style="color: #008000;">输出信息 </span>
- <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$res</span><span style="color: #000000;"> instanceof mysqli_result)
- {
- </span><span style="color: #008000;">//</span><span style="color: #008000;">select 查询 </span>
- <span style="color: #0000ff;">while</span> (<span style="color: #800080;">$row</span> = <span style="color: #800080;">$res</span>-><span style="color: #000000;">fetch_assoc())
- {
- </span><span style="color: #008000;">//</span><span style="color: #008000;">... </span>
- <span style="color: #000000;"> }
- }
- </span><span style="color: #0000ff;">else</span><span style="color: #000000;">
- {
- </span><span style="color: #008000;">//</span><span style="color: #008000;">insert update delete查询 </span>
- <span style="color: #000000;"> }
- } </span><span style="color: #0000ff;">while</span> (<span style="color: #800080;">$mysqli</span>->more_results() && <span style="color: #800080;">$mysqli</span>->next_result()); <span style="color: #008000;">//</span><span style="color: #008000;">调用next_result()之前必须调用more_result() </span>
- }
- <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$stmt</span> = <span style="color: #800080;">$mysqli</span>->prepare(<span style="color: #800080;">$query</span><span style="color: #000000;">)) {
- </span><span style="color: #008080;"> 2</span>
- <span style="color: #008080;"> 3</span> <span style="color: #008000;">//</span><span style="color: #008000;"> 执行查询 /</span>
- <span style="color: #008080;"> 4</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">execute();
- </span><span style="color: #008080;"> 5</span>
- <span style="color: #008080;"> 6</span> <span style="color: #008000;">//</span><span style="color: #008000;"> 存储结果//</span>
- <span style="color: #008080;"> 7</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">store_result();
- </span><span style="color: #008080;"> 8</span>
- <span style="color: #008080;"> 9</span> <span style="color: #008080;">printf</span>("行数: %d.\n", <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">num_rows);
- </span><span style="color: #008080;">10</span>
- <span style="color: #008080;">11</span> <span style="color: #008000;">//</span><span style="color: #008000;"> 成功的结果 //</span>
- <span style="color: #008080;">12</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">free_result();
- </span><span style="color: #008080;">13</span>
- <span style="color: #008080;">14</span> <span style="color: #008000;">//</span><span style="color: #008000;"> 关闭语句 //</span>
- <span style="color: #008080;">15</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">close();
- </span><span style="color: #008080;">16</span> }
查询3
- <span style="color: #008080;"> 1</span> <span style="color: #008000;">//</span><span style="color: #008000;"> $stmt=$this->mysqli->prepare("SELECT surname, name, user_id, last_m_own, last_m_str, role FROM users WHERE referer_id=(?)");</span>
- <span style="color: #008080;"> 2</span> <span style="color: #800080;">$stmt</span>=<span style="color: #800080;">$this</span>->mysqli->prepare("select id,name,pid from `tree`"<span style="color: #000000;">);
- </span><span style="color: #008080;"> 3</span> <span style="color: #800080;">$stmt</span>->bind_param(‘i‘,<span style="color: #800080;">$referer_id</span><span style="color: #000000;">);
- </span><span style="color: #008080;"> 4</span> <span style="color: #800080;">$stmt</span>->execute(); <span style="color: #008000;">//</span><span style="color: #008000;"> 执行查询 </span>
- <span style="color: #008080;"> 5</span> <span style="color: #800080;">$stmt</span>->store_result();<span style="color: #008000;">//</span><span style="color: #008000;"> 存储结果</span>
- <span style="color: #008080;"> 6</span> <span style="color: #800080;">$stmt</span>->bind_result(<span style="color: #800080;">$ans</span>[‘id‘], <span style="color: #800080;">$ans</span>[‘name‘], <span style="color: #800080;">$ans</span>[‘pid‘<span style="color: #000000;">]);
- </span><span style="color: #008080;"> 7</span> <span style="color: #800080;">$j</span>=<span style="color: #800080;">$stmt</span>-><span style="color: #000000;">num_rows;
- </span><span style="color: #008080;"> 8</span> <span style="color: #0000ff;">for</span> (<span style="color: #800080;">$i</span>=0;<span style="color: #800080;">$i</span><<span style="color: #800080;">$j</span>;<span style="color: #800080;">$i</span>++<span style="color: #000000;">){
- </span><span style="color: #008080;"> 9</span> <span style="color: #800080;">$stmt</span>->data_seek(<span style="color: #800080;">$i</span><span style="color: #000000;">);
- </span><span style="color: #008080;">10</span> <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">fetch();
- </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">foreach</span> (<span style="color: #800080;">$ans</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$key</span>=><span style="color: #800080;">$value</span><span style="color: #000000;">){
- </span><span style="color: #008080;">12</span> <span style="color: #800080;">$result</span>[<span style="color: #800080;">$i</span>][<span style="color: #800080;">$key</span>]=<span style="color: #800080;">$value</span><span style="color: #000000;">;
- </span><span style="color: #008080;">13</span> <span style="color: #0000ff;">echo</span> <span style="color: #800080;">$result</span>[<span style="color: #800080;">$i</span><span style="color: #000000;">] ;
- </span><span style="color: #008080;">14</span>
- <span style="color: #008080;">15</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">16</span> }
- <span style="color: #008080;">1</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> 关闭连接 </span><span style="color: #008000;">*/</span>
- <span style="color: #008080;">2</span> <span style="color: #800080;">$mysqli</span>->close();
Php使用mysqli_result类处理结果集有以下几种方法
fetch_all() | 抓取所有的结果行并且以关联数据,数值索引数组,或者两者皆有的方式返回结果集。 |
fetch_array() | 以一个关联数组,数值索引数组,或者两者皆有的方式抓取一行结果。 |
fetch_object() | 以对象返回结果集的当前行。 |
fetch_row() | 以枚举数组方式返回一行结果 |
fetch_assoc() | 以一个关联数组方式抓取一行结果。 |
fetch_field_direct() | 以对象返回结果集中单字段的元数据。 |
fetch_field() | 以对象返回结果集中的列信息。 |
fetch_fields() | 以对象数组返回代表结果集中的列信息。 |
fetch_all (从结果集中取得所有行作为关联数组)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>->fetch_all(MYSQLI_BOTH);<span style="color: #008000;">//</span><span style="color: #008000;">参数MYSQL_ASSOC、MYSQLI_NUM、MYSQLI_BOTH规定产生数组类型</span>
- <span style="color: #008080;">4</span> <span style="color: #800080;">$n</span>=0<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$n</span><<span style="color: #008080;">mysqli_num_rows</span>(<span style="color: #800080;">$result</span><span style="color: #000000;">)){
- </span><span style="color: #008080;">6</span> <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["id"]."用户名:".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["name"]."密码:".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["password"]."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">7</span> <span style="color: #800080;">$n</span>++<span style="color: #000000;">;
- </span><span style="color: #008080;">8</span> }
fetch_array (以一个关联数组,数值索引数组,或者两者皆有的方式抓取一行结果)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_array()){
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>["id"]."用户名:".<span style="color: #800080;">$row</span>[1]."密码:".<span style="color: #800080;">$row</span>["password"]."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">fetch_array方法不再有结果时返回返回NULL
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//其返回结果可以是关联数组也可以是数值数组索引,故$row["id"]、$row[1]都可以</span>
fetch_object (以对象返回结果集的当前行)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_object()){
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>->id."用户名:".<span style="color: #800080;">$row</span>->name."密码:".<span style="color: #800080;">$row</span>->password."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">如果无更多的行则返回NULL
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//返回的结果是对象,要以对象的方式调用</span>
fetch_row (以枚举数组方式返回一行结果)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_row()){
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>[0]."用户名:".<span style="color: #800080;">$row</span>[1]."密码:".<span style="color: #800080;">$row</span>[2]."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">无更多行时返回NULL
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以数值下标调用数组,a[0]正确、a["id"]则不</span>
fetch_assoc (以一个关联数组方式抓取一行结果)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_assoc()){
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>["id"]."用户名:".<span style="color: #800080;">$row</span>["name"]."密码:".<span style="color: #800080;">$row</span>["password"]."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">无更多行时返回NULL
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以关联索引访问数组,a["id"]正确、a[0]则不</span>
fetch_field_direct (以对象返回结果集中单字段的元数据既单列的信息)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #800080;">$n</span>=0<span style="color: #000000;">;
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">while</span>(1<span style="color: #000000;">){
- </span><span style="color: #008080;">5</span> <span style="color: #0000ff;">if</span>(!<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>->fetch_field_direct(<span style="color: #800080;">$n</span>++)) <span style="color: #0000ff;">break</span><span style="color: #000000;">;
- </span><span style="color: #008080;">6</span> <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$row</span>->name."所在表:".<span style="color: #800080;">$row</span>->table."数据类型:".<span style="color: #800080;">$row</span>->type."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">7</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">8</span> <span style="color: #008000;">//</span><span style="color: #008000;">fetch_field_direct($n)只返回单个列,所以得不断调用该方法,没有该列时返回false</span>
fetch_field (以对象返回结果集中的列信息)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_field()){
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$row</span>->name."所在表:".<span style="color: #800080;">$row</span>->table."数据类型:".<span style="color: #800080;">$row</span>->type."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">5</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">该方法检索所有的列
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以对象方式返回列信息
- </span><span style="color: #008080;">8</span> <span style="color: #008000;">//返回对象属性如:name - 列名,table - 该列所在的表名,type - 该列的类型,等</span>
fetch_fields (以对象数组返回代表结果集中的列信息)
- <span style="color: #008080;">1</span> <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
- </span><span style="color: #008080;">2</span> <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
- </span><span style="color: #008080;">3</span> <span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_fields();
- </span><span style="color: #008080;">4</span> <span style="color: #0000ff;">foreach</span>(<span style="color: #800080;">$row</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$val</span><span style="color: #000000;">){
- </span><span style="color: #008080;">5</span> <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$val</span>->name."所在表:".<span style="color: #800080;">$val</span>->table."数据类型:".<span style="color: #800080;">$val</span>->type."<br />"<span style="color: #000000;">;
- </span><span style="color: #008080;">6</span> <span style="color: #000000;"> }
- </span><span style="color: #008080;">7</span> <span style="color: #008000;">//</span><span style="color: #008000;">该方法功能与目的fetch_field一样
- </span><span style="color: #008080;">8</span> <span style="color: #008000;">//不一样的是该方法返回一个对象数组(如:echo $row[0]->name;输出第一列的名字),而不是一次检索一列</span>
另:mysqli_result类还有其他方法
field_tell() | 返回字段指针的位置 |
data_seek() | 调整结果指针到结果集中的一个任意行 |
num_fields() | 返回结果集中的字段数(列数) |
field_seek() | 调整字段指针到特定的字段开始位置 |
free() | 释放与某个结果集相关的内存 |
fetch_lengths() | 返回结果集中当前行的列长度 |
num_rows() | 返回结果集中的行数 |
mysqli
标签:变量 cut sed statement mysq use 语句 event fetch