How to use Mysqli to operate databases (connection, query, transaction rollback, etc)

Source: Internet
Author: User
This article describes how to use Mysqli to operate a database in the PHP getting started tutorial. it involves the basic connection, encoding settings, query, modification, transaction rollback, and other operation skills of php + mysqli to operate a database, for more information about how to use Mysqli to operate databases, see the example in this article. We will share this with you for your reference. The details are as follows:

Demo1.php

 Connect (); $ _ mysqli-> connect ('localhost', 'root', '123', 'guest '); // disconnect MySQL mysqli_close () ==$ _ mysqli-> close (); $ _ mysqli-> close ();?>

Demo2.php

 Select_db ('school '); $ _ mysqli-> close ();?>

Demo3.php

 Close ();?>

Demo4.php

 Select_db ('fsdffd'); // if ($ _ mysqli-> errno) {echo 'database operation error :'. $ _ mysqli-> error; }$ _ mysqli-> close ();?>

Demo5.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user"; // execute the SQL statement, grant the result set $ _ result =$ _ mysqli-> query ($ _ SQL); // var_dump ($ _ result); // object (mysqli_result) #2 (0) {}// through the result set, I want to obtain the first row of data // fetch_row (); is the returned array, it contains the first data set print_r ($ _ result-> fetch_row (); // run once, and the pointer moves down a print_r ($ _ result-> fetch_row ()); // destroy the result set $ _ result-> free (); $ _ mysqli-> close ();?>

Demo6.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL ); // use the index array value // print_r ($ _ result-> fetch_row (); $ _ row =$ _ result-> fetch_row (); echo $ _ row [3]; // traversal, subscripts are hard to remember [3] while (!! $ _ Row =$ _ result-> fetch_row () {echo $ _ row [3].'
';}$ _ Mysqli-> close () ;?>

Demo7.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL ); // use the associated array value // print_r ($ _ result-> fetch_assoc (); $ _ assoc =$ _ result-> fetch_assoc (); echo $ _ assoc ['tg _ username']; // traverse while (!! $ _ Assoc =$ _ result-> fetch_assoc () {echo $ _ assoc ['tg _ username'].'
';}$ _ Mysqli-> close () ;?>

Demo8.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL ); // use index + join array value // print_r ($ _ result-> fetch_array (); $ _ array =$ _ result-> fetch_array (); echo $ _ array [3]; echo $ _ array ['tg _ username']; // traverse ..... $ _ mysqli-> close ();?>

Demo9.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL ); // use the OOP method object // print_r ($ _ result-> fetch_object (); echo $ _ result-> fetch_object ()-> tg_username; // use OOP to traverse while (!! $ _ Object =$ _ result-> fetch_object () {echo $ _ object-> tg_username .'
';}$ _ Mysqli-> close () ;?>

Demo10.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user limit "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL); // I want to see how many lines I have selected echo $ _ result-> num_rows; // how many lines have I affected? echo $ _ mysqli-> affected_rows; $ _ mysqli-> close ();?>

Demo11.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "UPDATE tg_user SET tg_username = 'all-in-one website 'Where tg_id = 5 "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL); // I want to see how many lines I have selected echo $ _ result-> num_rows; echo '|'; // how many rows have I affected? echo $ _ mysqli-> affected_rows; $ _ mysqli-> close ();?>

Demo12.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL); // Obtain the number of fields in the table echo $ _ result-> field_count; // Obtain the field name // $ _ field =$ _ result-> fetch_field (); // echo $ _ field-> name; // $ _ field =$ _ result-> fetch_field (); // echo $ _ field-> name; // while (!! $ _ Field =$ _ result-> fetch_field () {// echo $ _ field-> name .'
'; //} // Obtain all fields $ _ fields =$ _ result-> fetch_fields (); // echo $ _ fields [0]-> name; foreach ($ _ fields as $ _ field) {echo $ _ field-> name.'
';}$ _ Mysqli-> close () ;?>

Demo13.php

 Set_charset ('utf8'); // Create an SQL statement to obtain the data of the database table $ _ SQL = "SELECT * FROM tg_user "; // create a result set $ _ result =$ _ mysqli-> query ($ _ SQL); // move the data pointer $ _ result-> data_seek (9 ); $ _ row =$ _ result-> fetch_row (); echo $ _ row [3]; // move the field pointer $ _ result-> field_seek (3 ); $ _ field =$ _ result-> fetch_field (); echo $ _ field-> name; $ _ mysqli-> close ();?>

Demo14.php

 Set_charset ('utf8'); // create three modified SQL statements $ _ SQL. = "UPDATE tg_article SET tg_username = 'kakaka' WHERE tg_id = 1;"; $ _ SQL. = "UPDATE tg_flower SET tg_fromuser = 'callaca' WHERE tg_id = 1;"; $ _ SQL. = "UPDATE tg_friend SET tg_fromuser = 'callaca' WHERE tg_id = 1"; // use the notification execution method $ _ mysqli-> multi_query ($ _ SQL ); // The normal method that can only execute SQL is: $ _ mysqli-> query ($ _ SQL); $ _ mysqli-> close ();?>

Demo15.php

 Set_charset ('utf8'); // create three selection statements $ _ SQL. = "SELECT * FROM tg_photo;"; $ _ SQL. = "SELECT * FROM tg_user;"; $ _ SQL. = "SELECT * FROM tg_friend"; if ($ _ mysqli-> multi_query ($ _ SQL )) {// Obtain the current result set $ _ result = $ _ mysqli-> store_result (); print_r ($ _ result-> fetch_row (); echo'
'; // Move the result set pointer to the next $ _ mysqli-> next_result (); $ _ result = $ _ mysqli-> store_result (); if (! $ _ Result) {echo 'the second SQL statement has five! '; Exit ();} print_r ($ _ result-> fetch_row (); echo'
'; $ _ Mysqli-> next_result (); $ _ result = $ _ mysqli-> store_result (); if (! $ _ Result) {echo 'the third SQL statement has five! '; Exit ();} print_r ($ _ result-> fetch_row ();} else {echo 'the first SQL statement is incorrect'; exit ();} $ _ mysqli-> close ();?>

Demo16.php

 Set_charset ('utf8'); // Set to disable automatic submission (manual submission) $ _ 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"; // execute multiple SQL statements // as long as both SQL statements succeed, manually submit it to the database // otherwise, roll back and cancel the previous valid operations. If ($ _ mysqli-> multi_query ($ _ SQL) {// number of affected rows, to determine whether the SQL statement is successfully executed // if $ _ success is false, it indicates that the SQL statement has Wu, then the rollback is executed, otherwise, manually submit $ _ success =$ _ mysqli-> affected_rows = 1? True: false; // move down the pointer $ _ mysqli-> next_result (); $ _ success2 =$ _ mysqli-> affected_rows = 1? True: false; // if both are successful, if ($ _ success & $ _ success2) {// execute manual submission $ _ mysqli-> commit (); echo 'perfect submit';} else {// roll back, undo all previous operations $ _ mysqli-> rollback (); echo all operations to zero! ';}} Else {echo' The first SQL statement has an error! ';} // Enable automatic submission $ _ mysqli-> autocommit (true); $ _ mysqli-> close ();?>

I hope this article will help you with PHP programming.

For more information about how to use Mysqli to operate databases (connection, query, transaction rollback, etc.) in the PHP Getting Started Tutorial, please follow the PHP Chinese network!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.