MySQLi面向对象实践--select

对于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();

?>

巴特西