PHP connection and Operation MySQL database basic tutorial, MySQL basic tutorial _php Tutorial

PHP connection and Operation MySQL database basic tutorial, MySQL basic tutorial

Start from here

What is my blog, background database? Yes, it is MySQL, the server side of the script is PHP, the entire framework uses WordPress. PHP and MySQL, like couples, are always working together. Now here, the collection of PHP, summarize the actual use of MySQL, also counted as the introduction of MySQL development. There are three ways to collaborate with MySQL on PHP:

1.mysql extension, but it is not recommended at present;

2.mysqli extension, while providing object-oriented style and process-oriented style, requires MySQL version is 4.1 and above;

The 3.PDO extension defines a lightweight, consistent interface for PHP access to the database, and Pdo_mysql is a concrete implementation of it. Here for the time being only concerned development. Since the MySQL extension is deprecated, I will keep up with the times, not summarize, and mysqli and PDO are used more, so this article will summarize how to use the mysqli extension to connect to the database server, how to query and retrieve data, and how to perform other important tasks. The next blog post will summarize the contents of PDO.

Using mysqli extensions

Let's look at the test data in the following test database db_test:

Copy the Code code as follows:
Mysql> select * from Tb_test;
+----+-----------+----------+------------+------------+
| ID | FirstName | LastName | email | Phone |
+----+-----------+----------+------------+------------+
| 1 | Young | Jelly | 123@qq.com | 1384532120 |
| 3 | Fang | Jone | 456@qq.com | 1385138913 |
| 4 | Yuan | Su | 789@qq.com | 1385138913 |
+----+-----------+----------+------------+------------+
3 Rows in Set (0.00 sec)

1. Establishing and disconnecting connections

When interacting with the MySQL database, you first establish a connection and finally disconnect it, including connecting to the server and selecting a database, and finally closing the connection to free up resources. Choosing to interact with the MySQL server using an object-oriented interface requires the first instantiation of the Mysqli class through its constructor function.

Copy the Code code as follows:
<?php
Instantiate the Mysqli class
$mysqliConn = new mysqli ();
Connect to the server and select a database
$mysqliConn->connect (' 127.0.0.1 ', ' root ', ' root ', ' db_test ');
printf ("MySQL error number:%d", $mysqliConn->errno);
Or
$mysqliConn->connect ("http://127.0.0.1", ' root ', ' root ');
$mysqliConn->select_db (' db_test ');

Interacting with the database

Close connection
$mysqliConn->close ();
?>

Once you have successfully selected the database, you can then perform a database query against the database. Once the script is executed, all open database connections are automatically closed and resources are freed. However, it is possible that a page will require multiple database connections during execution, and each connection should be properly closed. Even if you use only one connection, you should close it at the end of the script, which is a good practice. In any case, close () is responsible for closing the connection.

2. Handling Connection Errors

Of course, if the MySQL database cannot be connected, it is unlikely that the expected work will continue on this page. Therefore, be sure to monitor the connection errors and react accordingly. The Mysqli expansion pack contains many features that can be used to catch error messages, or you can use exceptions to do this. For example, you can use the Mysqli_connect_errno () and Mysqli_connect_error () methods to diagnose and display information about a MySQL connection error.

Specific information about MYSQLI can be viewed here: http://php.net/manual/zh/book.mysqli.php

Interacting with the database

The vast majority of queries are related to creating, fetching, updating, and deleting tasks, collectively referred to as CRUD. Here we start summarizing crud-related content.

1. Send a query to the database

Method Query () is responsible for sending query to the database. It is defined as follows:
Copy the Code code as follows:
Mixed Mysqli::query (string $query [, int $resultmode = Mysqli_store_result])

The optional parameter Resultmode can be used to modify the behavior of this method, which accepts two desirable values. This article summarizes the differences between the two. http://www.bkjia.com/article/55792.htm; Here is a simple example of use:

Copy the Code code as follows:
<?php
Instantiate the Mysqli class
$mysqliConn = new mysqli ();

Connect to the server and select a database
The wrong password
$mysqliConn->connect (' 127.0.0.1 ', ' root ', ' root ', ' db_test ');
if ($mysqliConn->connect_error)
{
printf ("Unable to connect to the database:%s", $mysqliConn->connect_error);
Exit ();
}

Interacting with the database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send a query to MySQL
$result = $mysqliConn->query ($query);

Iterating over result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s%s ' s email:%s
", $firstname, $lastname, $email);
}

Close connection
$mysqliConn->close ();
?>

2. Inserting, updating, and deleting data

Inserts, updates, and deletes are done using INSERT, UPDATE, and delete queries, which are actually the same as the select query. The sample code is as follows:

Copy CodeThe code is as follows:
<?php
Instantiate the Mysqli class
$mysqliConn = new mysqli ();

Connect to the server and select a database
The wrong password
$mysqliConn->connect (' 127.0.0.1 ', ' root ', ' root ', ' db_test ');
if ($mysqliConn->connect_error)
{
printf ("Unable to connect to the database:%s", $mysqliConn->connect_error);
Exit ();
}

Interacting with the database
$query = ' SELECT FirstName, LastName, email from tb_test; ';
Send a query to MySQL
$result = $mysqliConn->query ($query);

Iterating over result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s%s ' s email:%s
", $firstname, $lastname, $email);
}

$query = "Delete from tb_test where FirstName = ' Yuan ';";
$result = $mysqliConn->query ($query);

Tell the user how many rows are affected
printf ("%d row (s)" has been deleted.
", $mysqliConn->affected_rows);
Re-query result set
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send a query to MySQL
$result = $mysqliConn->query ($query);

Iterating over result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s%s ' s email:%s
", $firstname, $lastname, $email);
}
Close connection
$mysqliConn->close ();
?>

3. Release the query memory

Sometimes it is possible to get a particularly large result set, and once the processing is complete, it is necessary to release the memory requested by the result set. The free () method can perform this task for us. For example:
Copy the Code code as follows:
Interacting with the database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send a query to MySQL
$result = $mysqliConn->query ($query);

Iterating over result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s%s ' s email:%s
", $firstname, $lastname, $email);
}
$result->free ();

4. Parse Query Results

Once the query is executed and the result set is ready, the resulting rows can be parsed. You can use more than one method to get the fields in each row, depending on which one you prefer, because only the method that refers to the field is different.

(1) Put the result in the object

Use the Fetch_object () method to complete. The Fetch_object () method is typically called in a loop, and each invocation causes the next row in the returned result set to be populated with an object, which can then be accessed according to PHP's typical object access syntax. For example:
Copy the Code code as follows:
Interacting with the database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send a query to MySQL
$result = $mysqliConn->query ($query);

Iterating over result sets
while ($row = $result->fetch_object ())
{
$firstname = $row->firstname;
$lastname = $row->lastname;
$email = $row->email;
}
$result->free ();

(2) using indexed arrays and associative arrays to get results

The mysqli extension package also allows you to manage result sets by using the Fetch_array () method and the Fetch_row () method, respectively, with associative arrays and indexed arrays. The Fetch_array () method can actually get the rows of the result set as an associative array, a numeric index array, or both, so that Fetch_row () is a subset of the Fetch_array. By default, Fetch_array () gets both an associative array and an indexed array, which can be passed in Fetch_array to modify the default behavior.

Mysqli_assoc, the row is returned as an associative array, the key is represented by the field name, and the value is represented by the field contents;
Mysqli_num, returns the row as a numeric index array whose element order is determined by the order of the field names specified in the query;
Mysqli_both, is the default option.

Determine the selected row and the affected row

You typically want to be able to determine the number of rows returned by a select query, or the number of rows affected by an INSERT, update, or delete.

(1) Determine the number of rows returned

The Num_rows property is useful if you want to understand how many rows are returned by the SELECT query statement. For example:
Copy the Code code as follows:
Interacting with the database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send a query to MySQL
$result = $mysqliConn->query ($query);

Get row Count
$result->num_rows;

Remember that num_rows is useful only when determining the number of rows fetched by the select query, and if you want to get the number of rows affected by the INSERT, update, or delete, use the Affected_rows property summarized below.

(2) Determine the number of rows affected

The Affected_rows property is used to get the number of rows affected by an INSERT, update, or delete. The code example is shown in the code above.

Performing Database transactions

There are 3 new ways to enhance PHP's ability to perform MySQL transactions, respectively:

1.autocommit function, enable auto-commit mode;

The autocommit () function controls the behavior of the MySQL autocommit mode, which determines whether auto-commit is initiated or disabled by the parameters passed in, and if true, automatic commit is turned on, and false is disabled. Whether enabled or disabled, returns true on success and false on failure.

2.commit function, commits the transaction, submits the current transaction to the database, returns true on success, or false.

The 3.rollback function, which rolls back the current transaction, returns true on success, otherwise returns false.

On the transaction, I will continue to summarize, here is a brief summary of the three APIs.

Won't end

This is just the beginning of MySQL learning, not the end. And then the next.

Steps for PHP to operate the MySQL database

$conn =mysql_pconnect ("localhost", "root", "123456");//Open connection
mysql_select_db ("Database name", $conn);//Connect to the specified database
mysql_query ("Set names UTF8");//Set character encoding
$sql = "";
$R =mysql_query ($sql);//Execute SQL statement to return result set
while ($v =mysql_fetch_array ($R)) {
echo "field name". $v [' title '];
}

PHP How to connect MySQL database problem

$hostname = "localhost";//host address, generally do not need to change
$database = "Zx_title";//database table name
$username = "root";//username, the default is usually root
$password = "123"; Password for//mysql database
$conn = Mysql_pconnect ($hostname, $username, $password) or Trigger_error (Mysql_error (), e_user_error);
?>

http://www.bkjia.com/PHPjc/887352.html www.bkjia.com true http://www.bkjia.com/PHPjc/887352.html techarticle PHP connection and operation MySQL database basic tutorial, MySQL basic tutorial from here to start my blog, what is the background database? Yes, it is MySQL, the server side of the script is P ...

