1. Establish a database connection
<?php
$mysqli = new mysqli("localhost","root","","mydb");
?>
Establishing a database connection requires four parameters, namely the database address, database access user name, database access password, and database name. In addition to using the above mysqli object constructor to establish a database connection, it can also call its connect method to establish a database connection.
<?php
$mysqli = new mysqli();
$mysqli->connect("localhost","root","","mydb");
?>
You can also establish a data connection through the mysqli object construction method and specify the database to be accessed through the select_db method.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
?>
Get the error number of the current connection through the errno property of the mysqli object. If there is no error in the current connection, the error number will be returned to 0.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
}
else
{
echo "The Connection is Error!";
exit();
}
?>
Of course, you can obtain the error information of the current connection through the error property of the mysqli object. If there is no error, return "".
<?php
$mysqli = new mysqli("localhost","rootsss","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
2. Query the database
Query databases can use the query method of mysqli object, which returns the result set of query databases.
The syntax is: $mysqli->query (query statement, query mode);
There are two types of query modes:
① MYSQLI_STORE_RESULT. Return the results 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 queryed and put into memory, which means that if the data in the result set is large, it will occupy more memory. But using this method we can easily know how many rows a query returns or hopes to jump to a row in the result set immediately.
② MYSQLI_USE_RESULT. Returns the result set as a non-cache set. This means that the result set will be retrieved from the database server as needed. Doing so for larger result set data can improve performance. However, it will lead to many operations on the result set being restricted, such as getting the number of query rows, etc.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$result = $mysqli->query($sql);
echo "Result row nums:".$result->num_rows."<br>"; //Show the 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 set. Each row of data is an associative array, and use the list method to output each data in it. You can also use the output object to output each row in the result set.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$result = $mysqli->query($sql);
echo "Result row nums:".$result->num_rows."<br>"; //Show the 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();
}
?>
The fetch_object method is used to encapsulate the data in each row into an object. In the above example, the object is $rowObject, and each column in the database becomes the attribute of the object. By calling its attribute name, the corresponding field value can be obtained. For example, get the student name $rowObject->name.
You can also use the fetch_array method to return each row of data as an associative array or an index array, or return the associative array and index array at the same time. The pattern parameter of the fetch_array method specifies the pattern of the currently returned array:
① MYSQLI_ASSOC. Returns the associative array, key is the field name, and value is the field value.
② MYSQLI_NUM. Returns the index array, the return order is the same as the query field order.
③ MYSQLI_BOTH. Returns the associative array and index array at the same time. Default settings.
<?php
while($row = $result->fetch_array(MYSQLI_ASSOC)) //Return associative array
{
echo $row['id']. $row['name'].$row['age'].$row['address']."<br>";
}
?>
or
<?php
while($row = $result->fetch_array(MYSQLI_NUM)) //Return the index array
{
echo $row[0]. $row[1].$row[2].$row[3]."<br>";
}
?>
3. Free memory
If the result set data is huge and has been used, the free method of the result set object is used to free the memory occupied by the result set. Once the free method is called, the result set will no longer be available.
<?php
...
$result->free(); //Release memory
?>
4. Add, modify and delete operations
Using the query method of mysqli object can still perform the addition, modification and deletion of the database, but it is just the difference between SQL statements. Let's take adding data as an example:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge 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; //The number of rows affected by the output
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
Calling the affected_rows property of the mysqli object can get the number of rows affected.
5. Close the database connection
When a database connection is completed, call the close method of the mysqli object to close it.
<?php
...
$mysqli->close();
?>
6. Use binding parameters
The binding parameters in PHP are the same as preprocessed SQL in Java. When a SQL is repeatedly executed, the binding parameters can be used to improve the execution speed of SQL when the SQL parameters are not fixed.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge 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); //Preprocess SQL
$stmt->bind_param("isis",$id,$name,$age,$address); //Set the bound variable The first parameter is the data type of the variable
for($i = 12;$i<100;$i++)
{
$id = $i + 1;
$name = "Fan Kai";
$age = 23;
$address = "xian";
$stmt->execute(); //Execute SQL statement
}
echo $mysqli->affected_rows; //The number of rows affected by the output
$stmt->close(); //Release the memory occupied by the preprocessing object
$mysqli->close(); //Close the database connection
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
It should be noted that the first parameter of the bind_param method, which specifies the data type of the following variable, and these 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.
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$stmt = $mysqli->stmt_init(); //Create a preprocessing object
$stmt->prepare($sql); //Preprocess SQL
$stmt->bind_result($id,$name,$age,$address); //Bind the query result field to a variable
$stmt->execute(); //Execute 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"."<br>";
}
$stmt->close(); //Release the memory occupied by the preprocessing object
$mysqli->close(); //Close the database connection
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","","mydb");
?>
Establishing a database connection requires four parameters, namely the database address, database access user name, database access password, and database name. In addition to using the above mysqli object constructor to establish a database connection, it can also call its connect method to establish a database connection.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli();
$mysqli->connect("localhost","root","","mydb");
?>
You can also establish a data connection through the mysqli object construction method and specify the database to be accessed through the select_db method.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
?>
Get the error number of the current connection through the errno property of the mysqli object. If there is no error in the current connection, the error number will be returned to 0.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
}
else
{
echo "The Connection is Error!";
exit();
}
?>
Of course, you can obtain the error information of the current connection through the error property of the mysqli object. If there is no error, return "".
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","rootsss","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
2. Query the database
Query databases can use the query method of mysqli object, which returns the result set of query databases.
The syntax is: $mysqli->query (query statement, query mode);
There are two types of query modes:
① MYSQLI_STORE_RESULT. Return the results 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 queryed and put into memory, which means that if the data in the result set is large, it will occupy more memory. But using this method we can easily know how many rows a query returns or hopes to jump to a row in the result set immediately.
② MYSQLI_USE_RESULT. Returns the result set as a non-cache set. This means that the result set will be retrieved from the database server as needed. Doing so for larger result set data can improve performance. However, it will lead to many operations on the result set being restricted, such as getting the number of query rows, etc.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$result = $mysqli->query($sql);
echo "Result row nums:".$result->num_rows."<br>"; //Show the 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 set. Each row of data is an associative array, and use the list method to output each data in it. You can also use the output object to output each row in the result set.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$result = $mysqli->query($sql);
echo "Result row nums:".$result->num_rows."<br>"; //Show the 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();
}
?>
The fetch_object method is used to encapsulate the data in each row into an object. In the above example, the object is $rowObject, and each column in the database becomes the attribute of the object. By calling its attribute name, the corresponding field value can be obtained. For example, get the student name $rowObject->name.
You can also use the fetch_array method to return each row of data as an associative array or an index array, or return the associative array and index array at the same time. The pattern parameter of the fetch_array method specifies the pattern of the currently returned array:
① MYSQLI_ASSOC. Returns the associative array, key is the field name, and value is the field value.
② MYSQLI_NUM. Returns the index array, the return order is the same as the query field order.
③ MYSQLI_BOTH. Returns the associative array and index array at the same time. Default settings.
Copy the codeThe code is 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 the codeThe code is as follows:
<?php
while($row = $result->fetch_array(MYSQLI_NUM)) //Return the index array
{
echo $row[0]. $row[1].$row[2].$row[3]."<br>";
}
?>
3. Free memory
If the result set data is huge and has been used, the free method of the result set object is used to free the memory occupied by the result set. Once the free method is called, the result set will no longer be available.
Copy the codeThe code is as follows:
<?php
...
$result->free(); //Release memory
?>
4. Add, modify and delete operations
Using the query method of mysqli object can still perform the addition, modification and deletion of the database, but it is just the difference between SQL statements. Let's take adding data as an example:
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge 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; //The number of rows affected by the output
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
Calling the affected_rows property of the mysqli object can get the number of rows affected.
5. Close the database connection
When a database connection is completed, call the close method of the mysqli object to close it.
Copy the codeThe code is as follows:
<?php
...
$mysqli->close();
?>
6. Use binding parameters
The binding parameters in PHP are the same as preprocessed SQL in Java. When a SQL is repeatedly executed, the binding parameters can be used to improve the execution speed of SQL when the SQL parameters are not fixed.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge 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); //Preprocess SQL
$stmt->bind_param("isis",$id,$name,$age,$address); //Set the bound variable The first parameter is the data type of the variable
for($i = 12;$i<100;$i++)
{
$id = $i + 1;
$name = "Fan Kai";
$age = 23;
$address = "xian";
$stmt->execute(); //Execute SQL statement
}
echo $mysqli->affected_rows; //The number of rows affected by the output
$stmt->close(); //Release the memory occupied by the preprocessing object
$mysqli->close(); //Close the database connection
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>
It should be noted that the first parameter of the bind_param method, which specifies the data type of the following variable, and these 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.
Copy the codeThe code is as follows:
<?php
$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Judge whether the current connection is successful
{
$sql = "SELECT * FROM student";
$stmt = $mysqli->stmt_init(); //Create a preprocessing object
$stmt->prepare($sql); //Preprocess SQL
$stmt->bind_result($id,$name,$age,$address); //Bind the query result field to a variable
$stmt->execute(); //Execute 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"."<br>";
}
$stmt->close(); //Release the memory occupied by the preprocessing object
$mysqli->close(); //Close the database connection
}
else
{
echo $mysqli->error; //Output current error message
exit();
}
?>