PHP5 basic MySQL database operation code

PHP 5 operates the basic MySQL database code, so remember, thanks a few times, even getting started with database operations. You can learn some string processing methods later or later. 1. establish a database connection

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root", "", "mydb ");
?>

To establish a database connection, four parameters are required: database address, database access username, database access password, and database name. In addition to using the above mysqli object constructor to establish a database connection, you can also call its connect method to establish a database connection.

The code is as follows:

$ Mysqli = new mysqli ();
$ Mysqli-> connect ("localhost", "root", "", "mydb ");
?>

You can also use the mysqli object constructor to establish a data connection and use the select_db method to specify the database to be accessed.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
?>

Get the error code of the current connection through the errno attribute of the mysqli object. if there is no error in the current connection, the error code is returned as 0.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
}
Else
{
Echo "The Connection is Error! ";
Exit ();
}
?>

Of course, you can use the error attribute of the mysqli object to get the error message of the current connection. if there is no error, "" is returned.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "rootsss ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>

2. query the database
You can use the query method of the mysqli object to query a database. this method returns the result set of the database.
Syntax: $ mysqli-> query (query statement, query mode );
There are two query modes:
① MYSQLI_STORE_RESULT. Return results as a cache set, which means you can immediately navigate the entire result set. This is the default setting. After the result set is queried, it is put into the memory, which means that if the data volume in the result set is large, it will occupy a large amount of memory. However, using this method, we can easily know how many rows of records are returned by a query or want to jump to a row in the result set immediately.
② MYSQLI_USE_RESULT. Return the result set as a non-cache set. This means that the result set will be obtained from the database server as needed. this can improve the performance of large result set data. However, many operations on the result set are restricted, such as obtaining the number of queried rows.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
$ SQL = "SELECT * FROM student ";
$ Result = $ mysqli-> query ($ SQL );
Echo "Result row nums:". $ result-> num_rows ."
"; // Display the number of result sets
// Iteration result set
While (list ($ id, $ name, $ age, $ address) = $ result-> fetch_row ())
{
Echo "$ id: $ name: $ age: $ address "."
";
}
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>

Use the fetch_row method of the result set object to obtain each row of data in the result set. each row of data is an associated array. use the list method to output each row of data. You can also use an output object to output each row in the result set.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
$ SQL = "SELECT * FROM student ";
$ Result = $ mysqli-> query ($ SQL );
Echo "Result row nums:". $ result-> num_rows ."
"; // Display the number of result sets
// Iteration result set
While ($ rowObject = $ result-> fetch_object ())
{
Echo "$ rowObject-> id: $ rowObject-> name: $ rowObject-> age: $ rowObject-> address "."
";
}
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>

The fetch_object method is used to encapsulate data in each row as an object. in the preceding example, this object is $ rowObject, and each column in the database becomes the attribute of this object, you can call the property name of this object to obtain the corresponding field value. For example, get the student name $ rowObject-> name.
You can also use the fetch_array method to return each row of data as an associated array or an index array, or return both the associated array and the index array. The mode parameter of the fetch_array method to specify the mode of the currently returned array:
① MYSQLI_ASSOC. Returns the joined array, where key is the field name and value is the field value.
② MYSQLI_NUM. Returns an index array. The Returned order is the same as the query field order.
③ MYSQLI_BOTH. Returns the associated array and index array. Default settings.

The code is as follows:

While ($ row = $ result-> fetch_array (MYSQLI_ASSOC) // returns the associated array
{
Echo $ row ['id']. $ row ['name']. $ row ['age']. $ row ['address']."
";
}
?>

Or

The code is as follows:

While ($ row = $ result-> fetch_array (MYSQLI_NUM) // returns the index array
{
Echo $ row [0]. $ row [1]. $ row [2]. $ row [3]."
";
}
?>

3. release memory
If the data volume in the result set is large and used, the free method of the result set object is used to release the memory occupied by the result set. Once the free method is called, the result set is no longer available.

The code is as follows:

...
$ Result-> free (); // release memory
?>

4. add, modify, and delete operations
The query method of the mysqli object can still be used to add, modify, and delete databases, except that SQL statements are different. Let's take adding data as an example:

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
$ SQL = "INSERT INTO student (id, name, age, address) VALUES ('8', 'kay ', '23', 'xian ')";
$ Result = $ mysqli-> query ($ SQL );
Echo $ mysqli-> affected_rows; // number of affected rows
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>

You can call the affected_rows attribute of the mysqli object to obtain the number of affected rows.
5. close database connection
After a database connection is used, call the close method of the mysqli object to close it.

The code is as follows:

...
$ Mysqli-> close ();
?>

6. bind parameters
The binding parameters in PHP are the same as those in the pre-processing SQL statements in Java. When an SQL statement is executed repeatedly, you can bind parameters to improve the SQL execution speed.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
$ SQL = "INSERT INTO student (id, name, age, address) VALUES (?,?,?,?) ";
$ Stmt = $ mysqli-> stmt_init (); // Create a preprocessing object
$ Stmt-> prepare ($ SQL); // preprocessing SQL
$ Stmt-> bind_param ("isis", $ id, $ name, $ age, $ address); // set the first parameter of the bound variable to the data type of the variable.
For ($ I = 12; I I <100; $ I ++)
{
$ Id = $ I + 1;
$ Name = "fan Kai ";
$ Age = 23;
$ Address = "xian ";
$ Stmt-> execute (); // execute an SQL statement
}
Echo $ mysqli-> affected_rows; // number of affected rows
$ Stmt-> close (); // release the memory occupied by the preprocessing object
$ Mysqli-> close (); // close the database connection
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>

Note that the first parameter of the bind_param method specifies the data type of the variable that follows. The data types are as follows:
① I: all Integer types.
② D: All double and float types.
③ B: Blob type.
④ S: other data types include strings.
7. result binding
Result binding is used to bind query results to some variables.

The code is as follows:

$ Mysqli = new mysqli ("localhost", "root ","");
$ Mysqli-> select_db ("mydb ");
If ($ mysqli-> errno = 0) // checks whether the current connection is successful.
{
$ SQL = "SELECT * FROM student ";
$ Stmt = $ mysqli-> stmt_init (); // Create a preprocessing object
$ Stmt-> prepare ($ SQL); // preprocessing SQL
$ Stmt-> bind_result ($ id, $ name, $ age, $ address); // bind the query result field to the variable
$ Stmt-> execute (); // execute an SQL statement
While ($ stmt-> fetch () // The fetch method is used to obtain each row in the result set and assign the corresponding field value to the variable.
{
Echo "$ id: $ name: $ age: $ address "."
";
}
$ Stmt-> close (); // release the memory occupied by the preprocessing object
$ Mysqli-> close (); // close the database connection
}
Else
{
Echo $ mysqli-> error; // output the current error message
Exit ();
}
?>