对于update、insert、delete请参考http://www.cnblogs.com/-beyond/p/8457580.html
执行select,如果SQL语句执行成功,那么返回的是一个mysqli_result对象,如果SQL语句出错,则返回false。
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( ! $mysqli_result ){
echo "error:".$mysqli->errno." : ".$mysqli->error;
} else {
print_r( $mysqli_result ); //打印查看会是一个mysqli_result对象
}
$mysqli->close();
?>
结果如下:
mysqli_result Object
(
[current_field] => 0
[field_count] => 2
[lengths] =>
[num_rows] => 3
[type] => 0
)
上面的这几项都是mysqli_result类的成员属性,都是可以通过mysqli_result的对象加 ->属性访问,比如查看结果集的条数
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( ! $mysqli_result ){
echo "error:".$mysqli->errno." : ".$mysqli->error;
} else {
echo $mysqli_result->num_rows;
}
$mysqli->close();
?>
获取完整结果集
使用mysql_result::fetch_all([fetch_type])方法获取一个索引数组的结果集,该结果集为完整的结果集;
参数fetch_type可以为MYSQLI_NUM(索引数组,默认)、MYSQLI_ASSOC(关联数组)、MYSQLLI_BOTH(两者都要),但是这个方法只适用于mysqlnd,mysqlnd在PHP5.4以后已经作为默认驱动,
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( $mysqli_result && $mysqli_result->num_rows>0 ){
$res = $mysqli_result->fetch_all();
print_r($res);
} else {
echo "error:".$mysqli->errno." : ".$mysqli->error;
}
$mysqli->close();
?>
结果如下:
Array
(
[0] => Array
(
[0] => 1
[1] => aaaa
)
[1] => Array
(
[0] => 2
[1] => bbbb
)
)
获取一条结果集
移动内部指针:mysqli_result::data_seek( int offset ) 只需指定offset值即可。
使用mysqli_result::fetch_row()来获取“指针所值”的一条数据,每取一次数据,指针指向下一条记录,结果以索引数组返回。
使用mysqli_result::fetch_array([fetch_type]),参数fetch_type可以为MYSQLI_NUM(索引数组)、MYSQLI_ASSOC(关联数组)、MYSQLLI_BOTH(两者都要,默认)
使用mysqli_result::fetch_assoc()来获取一条数据,和fetch_row类似,只不过返回的结果集是以关联数组形式。
还可以使用fetch_object()以一个对象的形式返回。
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( $mysqli_result && $mysqli_result->num_rows>0 ){
$res = $mysqli_result->fetch_row();
print_r( $res ); //Array ( [0] => 1 [1] => aaaa )
$mysqli_result->data_seek(0);
$res = $mysqli_result->fetch_array();
print_r( $res ); //Array ( [0] => 1 [id] => 1 [1] => aaaa [name] => aaaa )
$mysqli_result->data_seek(0);
$res = $mysqli_result->fetch_assoc();
print_r( $res ); //Array ( [id] => 1 [name] => aaaa )
} else {
echo "error:".$mysqli->errno." : ".$mysqli->error;
}
$mysqli->close();
?>
释放结果集
mysqli_result::free
mysqli_result::close
mysqli_result::free_result
这三者都可以
遍历结果集
在遍历结果集之前可以先判断结果集中是否有记录。
方法一:一次性获取(fetch_all),然后循环遍历
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( $mysqli_result && $mysqli_result->num_rows>0 ){
$res = $mysqli_result->fetch_all();//一次性去的所有数据
foreach( $res as $row ) {
print_r( $row );
}
$mysqli_result->free_result(); //释放结果集
} else {
echo "error:".$mysqli->errno." : ".$mysqli->error;
}
$mysqli->close();
?>
方法二:循环获取(fetch_array、fetch_assoc、fetch_row)
<?php
$mysqli = new Mysqli();
$mysqli->connect("localhost","root","root","test");
if ( $mysqli->connect_errno ){
die( $mysqli->connect_error );
}
$mysqli->set_charset("utf8");
$sql = "select * from mysqli;";
$mysqli_result = $mysqli->query($sql);
if( $mysqli_result && $mysqli_result->num_rows>0 ){
while( $row = $mysqli_result->fetch_assoc() ){
print_r( $row );
}
$mysqli_result->free_result(); //释放结果集
} else {
echo "error:".$mysqli->errno." : ".$mysqli->error;
}
$mysqli->close();
?>