MYSQLI Operating Database

1 Connecting the database: You can use objects or functions to connect (we mainly use mysqli objects here, with a function connection)

// Create a Mysqli object (also called a resource handle) $_mysqli New mysqli (); // Connect to Database//If you do not use object-oriented, you can use the Mysqli_connect () function to connect $_mysqli->connect (' localhost ', ' root ', ' kang123456 ', ' guest '); // disconnecting the Mysqli//mysqli_close () function $_mysqli->close ();

You can also connect directly when you create an object

// connect directly to the database when creating Mysqli objects $_mysqli new mysqli (' localhost ', ' root ', ' kang123456 ', ' guest '); // also select a database $_mysqli->select_db (' testguest '); $_mysqli->close ();

2 Error: Connection error and operation error

Connection error:

// connection Error//When the connection fails because of a parameter error, the object is not created successfully, so there is no way to invoke the Mysqli object, so use the function to catch the error @$_mysqlinew mysqli ( ' localhost ', ' roo ', ' kang123456 ', ' guest '); // 0 means there are no errors if (Mysqli_connect_errno()) {    echo ' database connection error '.  Mysqli_connect_error();     Exit ();} $_mysqli->close ();

Operation Error:

 //  operation error  @ $_mysqli  = new  mysqli (' localhost ', ' root ', ' kang123456 ', ' guest '  //  Select a database that does not exist, resulting in an operation error  $_mysqli ->select_db (' DFAs '  //  operation error  if  ($_mysqli ->errno) { echo  ' database operation error '.    $_mysqli ->ERROR;  exit   ();}  $_mysqli ->close (); 

3 Interacting with the database: creating a Get update delete

Create and get

<?PHP//Operation Error@$_mysqli=NewMysqli (' localhost ', ' root ', ' kang123456 ', ' testguest ')); //1 Setting the encoding    $_mysqli->set_charset (' UTF8 '); //2 Creating SQL    $_sql= ' SELECT * from Tg_user '; //3 Execute SQL, assign the result set to the variable    $_result=$_mysqli->query ($_sql); //get the first row of data, run once, and move the pointer down one bar    $_user=$_result-Fetch_row (); //4 Get//4-1 use an indexed array to loop out a user name     while(!!$_row=$_result-Fetch_row ()) {        Echo $_row[3]. ' <br/> '; }    //4-2 using associative arrays to loop out user names     while(!!$_assoc=$_result-Fetch_assoc ()) {        Echo $_assoc[' Tg_username ']. ' <br/> '; }    //4-3 using an index plus an associative array    Print_r($_result-Fetch_array ()); //4-4 uses OOP, but returns an object     while(!!$_object=$_result-Fetch_object ()) {        Echo $_object->tg_username. ' <br/> '; }?>

See how many rows are selected and how many rows are affected: properties that affect how many rows are mysqli

    //determine how many rows and affected rows are selected@$_mysqli=NewMysqli (' localhost ', ' root ', ' kang123456 ', ' testguest ')); if($_mysqli-errno) {        Echo' Database connection error '.$_mysqli-error; }    $_mysqli->set_charset (' UTF8 '); $_sql= ' SELECT * from Tg_user limit 0,10 '; $_result=$_mysqli->query ($_sql); //look at the number of lines selected, only check    Echo $_result->num_rows;//10///delete change how many rows are affected (if changed to the same effect 0 rows)    Echo $_mysqli->affected_rows;//Ten

Get Field (column)

    //1 See how many fields (columns) are in the result set    Echo $_result-Field_count; //2 Gets the name of the field, one at a time, and the pointer moves down    $_field=$_result->fetch_field ();//The object is returned    Echo $_field->name;//tg_id    Print_r($_field);//print as follows/*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] = [flags] = 49699 [Type] = 9 [Decimals] = > 0)*/    //Traverse     while(!!$_field=$_result-Fetch_field ()) {        Echo $_field->name. ' <br/> '; }    //31 times to get all of the fields    $_fields=$_result->fetch_fields ();//returns an array, each of which is the same object as the top    Print_r($_fields); //Traverse    foreach($_fields  as $_field){        Echo $_field->name. ' <br/> '; }

Move pointer: Move data pointer and move field pointer

    //Move pointer//1 move data pointer    $_result->data_seek (0);//moving to the No. 0 is the original position.    $_row=$_result-Fetch_row (); Echo $_row[3]; //2 moving field pointers    $_result->field_seek (0); $_field=$_result-Fetch_field (); Echo $_field->name;//tg_id

Multiple SQL statements executed together

    // create three modified SQL statements    $_sql . = ' Update tg_user set tg_username= ' Party Hing Ming ' where tg_id=43; ' ;     $_sql . = ' Update tg_flower set tg_fromuser= ' Party Hing Ming ' where tg_id=1; ' ;     $_sql . = ' Update tg_friend set tg_fromuser= ' Party Hing Ming ' where tg_id=1 ';     // Execute together    $_mysqli->multi_query ($_sql);

    //create three selection data    $_sql. = ' select * from Tg_user; '; $_sql. = ' select * from Tg_flower; '; $_sql. = ' SELECT * from Tg_friend '; //Echo $_mysqli->multi_query ($_sql); 1    if($_mysqli->multi_query ($_sql)){        //get the current result set        $_result=$_mysqli->store_result ();//First SQL statement        Print_r($_result-Fetch_row ()); Echo' <br > '; //Move the result set pointer to the next bar        $_mysqli-Next_result (); $_result=$_mysqli->store_result ();//Second SQL statement        Print_r($_result-Fetch_row ()); Echo' <br > '; //Move the result set pointer to the next bar        $_mysqli-Next_result (); $_result=$_mysqli->store_result ();//Third SQL statement        Print_r($_result-Fetch_row ()); }Else {        EchoError; Exit(); }

Transaction:

    //Transactions@$_mysqli=NewMysqli (' localhost ', ' root ', ' kang123456 ', ' testguest ')); $_mysqli->set_charset (' UTF8 '); //Turn off auto-commit    $_mysqli->autocommit (false); //create two SQL statements    $_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 '; //executes multiple SQL statements, and only two sentences succeed or rollback    if($_mysqli->multi_query ($_sql)){        //by the number of rows affected, to determine whether the successful execution, if the SQL statement is wrong, then perform a rollback, or manually commit        $_success=$_mysqli->affected_rows = = 1?true:false; //Move pointer down        $_mysqli-Next_result (); $_success2=$_mysqli->affected_rows = = 1?true:false; //if both are successful        if($_success&&$_success2){            //Submit Manually            $_mysqli-commit (); Echo' Perfect submission '; }Else {            //otherwise roll back            $_mysqli-rollback (); Echo' Rollback, Operation Zeroing '; }    }Else {        Echo' First article has errors '; Exit(); }    //turn on auto-commit    $_mysqli->autocommit (true);

MYSQLI Operating Database