mysqli操作数据库

            //
            创建mysqli对象(也可以叫做资源句柄)
            $_mysqli = new mysqli();
//连接数据库
//如果不使用面向对象，完全可以使用mysqli_connect()函数来连接$_mysqli->connect(‘localhost‘, ‘root‘, ‘kang123456‘, ‘guest‘);

//断开mysqli
//mysqli_close()函数$_mysqli->close();

            //
            创建mysqli对象时直接连接数据库
            $_mysqli = new mysqli(‘localhost‘, ‘root‘, ‘kang123456‘, ‘guest‘);
//另外选择一个数据库$_mysqli->select_db(‘testguest‘);

$_mysqli->close();

            //
            连接错误
//当因为参数错误导致连接失败是，对象没有创建成功，所以就没有权力调用mysqli对象的方法，所以要用函数方式去捕捉错误
@$_mysqli = new mysqli(‘localhost‘, ‘roo‘, ‘kang123456‘, ‘guest‘);
//0表示没有任何错误if (mysqli_connect_errno()){
    echo ‘数据库连接错误‘.mysqli_connect_error();
    exit();
}
$_mysqli->close();

            //
            操作错误
@$_mysqli = new mysqli(‘localhost‘, ‘root‘, ‘kang123456‘, ‘guest‘);
//选择一个不存在的数据库，产生操作错误$_mysqli->select_db(‘dfas‘);
//操作错误if ($_mysqli->errno){
    echo ‘数据库操作错误‘.$_mysqli->error;
    exit();
}
$_mysqli->close();

<?php
//操作错误
    @$_mysqli = new mysqli(‘localhost‘, ‘root‘, ‘kang123456‘, ‘testguest‘);
    //1 设置编码$_mysqli->set_charset(‘utf8‘);
    //2 创建sql$_sql = ‘select * from tg_user‘;
    //3 执行sql，把结果集赋给变量$_result = $_mysqli->query($_sql);
    //取得第一行数据，运行一次，指针下移一条$_user = $_result->fetch_row();
    //4 获取
    //4-1 使用索引数组循环出用户名while (!!$_row = $_result->fetch_row()){
        echo$_row[3].‘<br />‘;
    }
    //4-2 使用关联数组循环出用户名while (!!$_assoc = $_result->fetch_assoc()){
        echo$_assoc[‘tg_username‘].‘<br />‘;
    }
    //4-3 使用索引加关联数组print_r($_result->fetch_array());
    //4-4 使用oop方式，但返回的是对象while (!!$_object = $_result->fetch_object()){
        echo$_object->tg_username.‘<br />‘;
    }
?>

            //
            确定选择了多少行和受影响的行
    @$_mysqli = new mysqli(‘localhost‘, ‘root‘, ‘kang123456‘, ‘testguest‘);
    if ($_mysqli->errno){
        echo ‘数据库连接错误‘.$_mysqli->error;
    }
    $_mysqli->set_charset(‘utf8‘);
    $_sql = ‘select * from tg_user limit 0,10‘;
    $_result = $_mysqli->query($_sql);
    //看选择了多少行，只有查echo$_result->num_rows; //10
    //增删改查影响了多少行（如果改成一样的影响0行）echo$_mysqli->affected_rows;//10

            //
            1 查看结果集里有多少字段（列）
            echo
            $_result->field_count;
    //2 获取字段的名字，一次一个，指针下移$_field = $_result->fetch_field();//返回的是对象echo$_field->name; //tg_idprint_r($_field);//打印如下
/*     stdClass Object
    (
        [name] => tg_id
        [orgname] => tg_id
        [table] => tg_user
        [orgtable] => tg_user
        [def] =>
        [db] => testguest
        [catalog] => def
        [max_length] => 2
        [length] => 8
        [charsetnr] => 63
        [flags] => 49699
        [type] => 9
        [decimals] => 0
    ) *///遍历while (!!$_field = $_result->fetch_field()) {
        echo$_field->name.‘<br />‘;
    }
    //3 一次性取得所有字段$_fields = $_result->fetch_fields(); //返回的是数组，每个是和上边一样的对象print_r($_fields);
    //遍历foreach ($_fieldsas$_field){
        echo$_field->name.‘<br />‘;
    }

            //
            移动指针
    //1 移动数据指针
            $_result->data_seek(0);//移动到第0个就是原来的位置$_row = $_result->fetch_row();
    echo$_row[3];
    
    //2 移动字段指针$_result->field_seek(0);
    $_field = $_result->fetch_field();
    echo$_field->name; //tg_id

            //
            创建三个修改的sql语句
            $_sql .= ‘update tg_user set tg_username="党兴明" where tg_id=43;‘;
    $_sql .= ‘update tg_flower set tg_fromuser="党兴明" where tg_id=1;‘;
    $_sql .= ‘update tg_friend set tg_fromuser="党兴明" where tg_id=1‘;
    //一起执行$_mysqli->multi_query($_sql);

            //
            创建三条选择数据
            $_sql .= ‘select * from tg_user;‘;
    $_sql .= ‘select * from tg_flower;‘;
    $_sql .= ‘select * from tg_friend‘;
    //echo $_mysqli->multi_query($_sql);  //1if ($_mysqli->multi_query($_sql)){
        //获取当前结果集$_result = $_mysqli->store_result();  //第一条sql语句print_r($_result->fetch_row());
        echo ‘<br >‘;
        //将结果集指针移动到下一条$_mysqli->next_result();
        $_result = $_mysqli->store_result();  //第二条sql语句print_r($_result->fetch_row());
        echo ‘<br >‘;
        //将结果集指针移动到下一条$_mysqli->next_result();
        $_result = $_mysqli->store_result();  //第三条sql语句print_r($_result->fetch_row());
    }else {
        echo ‘错误‘;
        exit();
    }

            //
            事务
    @$_mysqli = new mysqli(‘localhost‘, ‘root‘, ‘kang123456‘, ‘testguest‘);
    $_mysqli->set_charset(‘utf8‘);
    //关闭自动提交$_mysqli->autocommit(false);
    //创建两个sql语句$_sql .= ‘update tg_flower set tg_flower=tg_flower-50 where tg_id=1;‘;
    $_sql .= ‘update tg_friend set tg_state=tg_state+50 where tg_id=1‘;
    
    //执行多条sql语句，只有两句都成功就成功，否则回滚if ($_mysqli->multi_query($_sql)){
        //通过影响的行数，来判断是否成功执行，如果sql语句有误，那么就执行回滚，否则手工提交$_success = $_mysqli->affected_rows == 1 ? true : false;
        //下移指针$_mysqli->next_result();
        $_success2 = $_mysqli->affected_rows == 1 ? true : false;
        //如果两条都成功if ($_success && $_success2){
            //手工提交$_mysqli->commit();
            echo ‘完美提交‘;
        }else {
            //否则回滚$_mysqli->rollback();
            echo ‘回滚， 操作归零‘;
        }
    }else {
        echo ‘第一条有错误‘;
        exit();
    }
    //开启自动提交$_mysqli->autocommit(true);