1. Establishing a database connection
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "" "," mydb ");
?>
Establishing a database connection requires four parameters, namely the database address, database access username, database access password, and database name. In addition to establishing a database connection using the construction method of the Mysqli object above, you can also call its Connect method to establish a connection to the database.
Copy Code code as follows:
<?php
$mysqli = new mysqli ();
$mysqli->connect ("localhost", "root", "" "," mydb ");
?>
You can also establish a data connection through the Mysqli object's construction method, specifying the database to access through the select_db method.
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
?>
Gets the error number of the current connection through the Errno property of the Mysqli object, and if there are no errors for the current connection, the error number is returned to 0.
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
}
Else
{
echo "The Connection is error!";
Exit ();
}
?>
You can, of course, get the error information for the current connection by using the Mysqli object's Error property, and if there are no errors, return "".
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "rootsss", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
}
Else
{
Echo $mysqli->error; Output current error message
Exit ();
}
?>
2. Querying the database
The query database can use the query method of the Mysqli object, which returns the result set of the query database.
The syntax is: $MYSQLI->query (query statement, query mode);
There are two types of query modes:
①mysqli_store_result. Returns the result as a cache set, which means that the entire result set can be navigated immediately. This setting is the default setting. The result set is placed in memory when it is queried, which means that if the data in the result set is large, it takes up more memory. But in this way we can easily know how many rows a query returns or want to jump immediately to a row in the result set.
②mysqli_use_result. Returns the result set as a non-cached set. This means that the result set is obtained from the database server as needed, which can improve performance for larger result set data. However, it can cause many restrictions on the operation of the result set, such as getting the number of query rows.
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
$sql = "SELECT * from student";
$result = $mysqli->query ($sql);
echo "Result row nums:". $result->num_rows. " <br> "; Show number of result sets
Iteration result set
while (list ($id, $name, $age, $address) = $result->fetch_row ())
{
echo "$id: $name: $age: $address." <br> ";
}
}
Else
{
Echo $mysqli->error; Output current error message
Exit ();
}
?>
Use the Fetch_row method of the result set object to get each row of data in the result sets, each row of data being an associative array, and use the list method to output each of these data. You can also output each row in the result set by using the output object.
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
$sql = "SELECT * from student";
$result = $mysqli->query ($sql);
echo "Result row nums:". $result->num_rows. " <br> "; Show number of result sets
Iteration result set
while ($rowObject = $result->fetch_object ())
{
echo "$rowObject->id: $rowObject->name: $rowObject->age: $rowObject->address". <br> ";
}
}
Else
{
Echo $mysqli->error; Output current error message
Exit ();
}
?>
Using the Fetch_object method, the data in each row is encapsulated as an object, and in the example above the object is $rowobject, and each column in the database becomes the property of the object, and the corresponding field value can be obtained by invoking its property name. For example, get the student name $rowobject->name.
You can also use the Fetch_array method to return each row of data to an associative array or an indexed array, or to return both an associative and an indexed array. Fetch_array the mode parameter of the method to specify the pattern of the current return array:
①mysqli_assoc. Returns the associative array, key is the field name, and value is the field value.
②mysqli_num. Returns an indexed array with the same order of return and query fields.
③mysqli_both. Returns both an associative and an indexed array. Default settings.
Copy Code code as follows:
<?php
while ($row = $result->fetch_array (MYSQLI_ASSOC))//return associative array
{
echo $row [' ID ']. $row [' name ']. $row [' age ']. $row [' address ']. " <br> ";
}
?>
Or
Copy Code code as follows:
<?php
while ($row = $result->fetch_array (mysqli_num))//Return index array
{
echo $row [0]. $row [1]. $row [2]. $row [3]. " <br> ";
}
?>
3. Free Memory
The free method of the result set object is used to release the memory occupied by the result set if the data volume is large and is already used. Once the free method is invoked, the result set is no longer available.
Copy Code code as follows:
<?php
...
$result->free (); Free memory
?>
4. Add, modify, and delete operations
The query method using the Mysqli object can still be added, modified, and deleted to the database, except for the different SQL statements. Let's add the data as an example:
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
$sql = "INSERT into student (id,name,age,address) VALUES (' 8 ', ' Kay ', ' n ', ' Xian ')";
$result = $mysqli->query ($sql);
Echo $mysqli->affected_rows; Number of rows affected by output
}
Else
{
Echo $mysqli->error; Output current error message
Exit ();
}
?>
The Affected_rows property that invokes the Mysqli object can get the number of rows affected.
5. Close the database connection
When a database connection is used, call the Mysqli object's Close method to turn it off.
Copy Code code as follows:
<?php
...
$mysqli->close ();
?>
6. Using Binding parameters
Binding parameters in PHP are the same principle as preprocessing SQL in Java, and when you execute a SQL repeatedly, you can use binding parameters to increase the execution speed of SQL.
Copy Code code as follows:
<?php
$mysqli = new Mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = = 0)//Determine if the current connection is successful
{
$sql = "INSERT into student (id,name,age,address) VALUES (?,?,?,?)";
$stmt = $mysqli->stmt_init (); To create a preprocessing object
$stmt->prepare ($sql); preprocessing of SQL
$stmt->bind_param ("ISIS", $id, $name, $age, $address); Set the variable of the binding the first parameter is the data type of the variable
for ($i = $i <100; $i + +)
{
$id = $i + 1;
$name = "Kaikai";
$age = 23;
$address = "Xian";
$stmt->execute (); Execute SQL statement
}
Echo $mysqli->affected_rows; Number of rows affected by output
$stmt->close (); Frees memory occupied by Preprocessed objects
$mysqli->close (); To close a database connection
}
Else
{
Echo $mysqli->error; Output current error message
Exit ();
}
?>
Note that the first parameter of the Bind_param method, which specifies the data type of the subsequent variables, is as follows:
①i: All the integer types.
②d: All double and float types.
③b:blob type.
④s: Other data types include strings.
7. Result binding
The result binding is used to bind the query results with some variables.
Copy Code code as follows:
<?php
$mysqli = new mysqli ("localhost", "root", "");
$mysqli->select_db ("MyDB");
if ($mysqli->errno = 0)//To determine whether the current connection succeeded
{
$sql = "SELECT * from student";
$stmt = $mysqli->stmt_init ();///Create preprocessed Object
$stmt->prepare ($sql);//Preprocess SQL
$stmt->bind_result ($ ID, $name, $age, $address); Bind the query result field to the
$stmt->execute ();//Execute SQL statement
while ($stmt->fetch ())//fetch method to get each row in the result set and assign the corresponding field value to the variable br>{
Echo $id: $name: $age: $address "." <br> ";
}
$stmt->close ();//free memory occupied by preprocessed objects
$mysqli->close ();//Close database connection
}
Else
{
echo $ mysqli->error; Prints the current error message
exit ();
}
?>
