PHP5 operates the basic code of MySQL database_PHP tutorial

1. Establish a database connection

Copy the code The code is as follows:

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

Four parameters are required to establish a database connection, which are the database address , database access user name, database access password, database name. In addition to using the constructor method of the mysqli object above to establish a database connection, you can also call its connect method to establish a database connection.

Copy code The code is as follows:

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

You can also establish a data connection through the construction method of the mysqli object. Specify the database to be accessed through the select_db method.

Copy code The code is as follows:

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

Get the error number of the current connection through the errno attribute of the mysqli object. If there are no errors on the current connection, the error number is returned as 0.

Copy code The code is as follows:

$mysqli = new mysqli("localhost"," root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Determine whether the current connection is successful
{
}
else
{
echo "The Connection is Error!";
exit();
}
?>

Sure Get the error information of the current connection through the error attribute of the mysqli object. If there is no error, return "".

Copy code The code is as follows:

$mysqli = new mysqli("localhost"," rootsss","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Determine whether the current connection is successful
{
}
else
{
echo $mysqli->error; //Output the current error message
exit();
}
?>

2. Query the database
To query the database, you 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 query modes:
① MYSQLI_STORE_RESULT. Returning results as a cached set means that the entire result set can be navigated at once. This setting is the default. After the result set is queried, it is put into memory. This means that if the amount of data in the result set is large, it will occupy more memory. But using this method, we can easily know how many rows of records are returned by a query or want to jump to a certain row of the result set immediately.
② MYSQLI_USE_RESULT. Return the result set as a non-cached set. This means that result sets are fetched from the database server as needed, which can improve performance for larger result set data. However, many operations on the result set will be restricted, such as obtaining the number of query rows.

Copy code The code is as follows:

$mysqli = new mysqli("localhost"," root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Determine whether the current connection is successful
{
$sql = "SELECT * FROM student";
$result = $mysqli->query($sql);
echo "Result row nums:".$result->num_rows."//Iterate the 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 get each row of data in the result set, each row The data is an associative array, and each data in it is output using the list method. You can also use the output object to output each row in the result set.

Copy code The code is as follows:

$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if ($mysqli->errno == 0) //Determine 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
//Iterate the 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 the data in each row into an object. In the above example, the object is $rowObject, and each column in the database becomes an attribute of the object. Through this The corresponding field value can be obtained by calling the object's attribute name. 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 both an associative array and an index array. The mode parameter of the fetch_array method specifies the mode of the currently returned array:
① MYSQLI_ASSOC. Returns an associative array, where key is the field name and value is the field value.
②MYSQLI_NUM. Returns an index array in the same order as the query fields.
③MYSQLI_BOTH. Returns both associative and indexed arrays. Default setting.

Copy code The code is as follows:

while($row = $result->fetch_array (MYSQLI_ASSOC)) //Return associative array
{
echo $row['id']. $row['name'].$row['age'].$row['address']."
";
}
?>

or

Copy code The code is as follows:

while($row = $result->fetch_array(MYSQLI_NUM)) //Return index array
{
echo $row[0]. $ row[1].$row[2].$row[3]."
";
}
?>

3. Release memory
If the amount of data in the result set is huge and has been 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.

Copy code The code is as follows:

...
$result-> free(); //Release memory
?>

4. Add, modify and delete operations
Using the query method of the mysqli object can still be performed The addition, modification and deletion operations of the database are just different SQL statements. Let's take adding data as an example:

Copy code The code is as follows:

$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Determine whether the current connection is Success
{
$sql = "INSERT INTO student(id,name,age,address) VALUES('8','kay','23','xian')";
$result = $mysqli->query($sql);
echo $mysqli->affected_rows; //Output the number of affected rows
}
else
{
echo $mysqli-> error; //Output the current error message
exit();
}
?>

Calling the affected_rows attribute of the mysqli object can get the number of affected rows.
5. Close the database connection
When a database connection is finished using, call the close method of the mysqli object to close it.

Copy code The code is as follows:

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

6. Use binding parameters
The binding parameters in PHP are the same as the preprocessing SQL in Java The principle is that when executing a SQL repeatedly, if the SQL parameters are irregular, you can use the binding parameter method to improve the execution speed of the SQL.

Copy code The code is as follows :

$mysqli = new mysqli("localhost","root","");
$mysqli->select_db("mydb");
if ($mysqli->errno == 0) //Determine whether the current connection is successful
{
$sql = "INSERT INTO student(id,name,age,address) VALUES(?,?,?,? )";
$stmt = $mysqli->stmt_init(); //Create preprocessing object
$stmt->prepare($sql); //Preprocess 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< ;100;$i++)
{
$id = $i + 1;
$name = "Fan Kai";
$age = 23;
$address = "xian";
$stmt->execute(); //Execute SQL statement
}
echo $mysqli->affected_rows; //Output the 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();
}
?>

It should be noted that the first parameter of the bind_param method specifies the following The data type of the variable. 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 and some variables.

Copy code The code is as follows:

$mysqli = new mysqli("localhost"," root","");
$mysqli->select_db("mydb");
if($mysqli->errno == 0) //Determine whether the current connection is successful
{
$sql = "SELECT * FROM student";
$stmt = $mysqli->stmt_init(); //Create preprocessing object
$stmt->prepare($sql); //For SQL Perform preprocessing
$stmt->bind_result($id,$name,$age,$address); //Bind the query result fields to variables
$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"."
";
}
$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();
}
?>

http://www.bkjia.com/PHPjc/320663.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/320663.htmlTechArticle1. Establish a database connection and copy the code as follows: ?php $mysqli = new mysqli("localhost","root ","","mydb"); ? Establishing a database connection requires four parameters, which are the database address...