I suddenly found myself busy this week .. Playing-PHP advanced, a little busy-php_PHP tutorial

I suddenly found myself busy this week .. Playing-PHP advanced, a little busy-php. I suddenly found myself busy this week .. Playing-PHP advanced, a little busy-phphi was only available on Tuesday, but I suddenly realized that this week was a little busy and I was still very expecting to go to the city this afternoon, I suddenly found this week a little busy .. Playing-PHP advanced, a little busy-php

Hi

Tuesday, but I suddenly realized that this week was a bit busy and I was still looking forward to it. I went to the city this afternoon and came back to watch a movie at night. I had a hot pot dinner tomorrow night. I took a short film the next afternoon, playing in the evening, Friday. well, it's Friday. Although I don't even know how to write weekly reports in this way, I 'd better do it.

1. PHP advanced completion

11. database operations

11.1 What databases does PHP support?

PHP implements database operations by installing corresponding extensions. Modern application design is inseparable from database applications. Currently, mainstream databases include MsSQL, MySQL, Sybase, Db2, Oracle, PostgreSQL, such as Access, PHP can be installed with extensions to support these databases. Generally, the LAMP architecture refers to Linux, Apache, Mysql, PHP, therefore, Mysql databases are widely used in PHP. in this chapter, we will briefly understand the Mysql operation methods.

11.2 database expansion

A database in PHP may have one or more extensions, either officially or by a third party. Common Mysql extensions include native mysql databases, enhanced Mysql I extensions, and PDO connections and operations.

Different extensions provide similar operation methods. different extensions may have some new features and may have different operation performance.

Mysql extension for database connection method:

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

Mysqli extension:

$link = mysqli_connect('mysql_host', 'mysql_user', 'mysql_password');

PDO extension

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';$user = 'dbuser';$password = 'dbpass';$dbh = new PDO($dsn, $user, $password);

$ Link = mysql_connect ('192. 0.0.1 ', 'code1', '') or die ('database connection failed ');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
$ Result = mysql_query ('select * from user limit 1 ');
$ Row = mysql_fetch_assoc ($ result );
Print_r ($ row );

11.3 connect to the MySQL database

To operate the database in PHP, you must first establish a connection with the database. generally, we use the mysql_connect function to connect to the database. this function must specify the database address, user name, and password.

$host = 'localhost';$user = 'code1';$pass = '';$link = mysql_connect($host, $user, $pass);

The PHP method for connecting to the database is similar to directly connecting to the database through the command line, similar:mysql -hlocalhost -ucode1 -pAfter the connection is successful, we need to select a database for the operation and use the mysql_select_db function to select the database.

mysql_select_db('code1');

Generally, we will first set the character encoding used for the current connection. generally, we will use utf8 encoding.

mysql_query("set names 'utf8'");

Through the above steps, we have established a connection with the database to perform data operations.

$ Host = '1970. 0.0.1 ';
$ User = 'code1 ';
$ Pass = '';
// Write the database connection code here
Mysql_connect ($ host, $ user, $ pass );
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");

11.4 execute MySQL Query

After the database is connected, the query command can be sent to the database in the form of mysql_query and SQL statements.

$res = mysql_query('select * from user limit 1');

A resource is returned for a query statement. you can use this resource to obtain data in the query result set.

$row = mysql_fetch_array($res);var_dump($row);

By default, PHP uses the latest database connection for query. However, if multiple connections exist, you can use the parameter command to query the connection.

$ Link1 = mysql_connect ('2017. 0.0.1 ', 'code1', ''); $ link2 = mysql_connect ('2017. 0.0.1 ', 'code1', '', true); // start a new connection $ res = mysql_query ('select * from user limit 1', $ link1 ); // query data from the first connection

// Connect to the database
Mysql_connect ('2014. 0.0.1 ', 'code1 ','');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
// Query data here
$ Arr = mysql_query ("select * from user limit 1 ");
$ Row = mysql_fetch_row ($ arr );
Print_r ($ row );
Echo $ row [0];

11.5 insert new data into MySQL

After learning how to use mysql_query to query data, you can execute an SQL statement to insert data. for example:

$ SQL = "insert into user (name, age, class) values ('li Si', 18, 'high Sany ban')"; mysql_query ($ SQL ); // execute the insert statement

Generally, data is stored in variables or arrays. Therefore, SQL statements must be concatenated with strings.

$ Name = 'Li si'; $ age = 18; $ class = 'higher than class s3'; $ SQL = "insert into user (name, age, class) values ('$ name',' $ age', '$ class') "; mysql_query ($ SQL); // execute the insert statement

In mysql, after the insert statement is executed, you can obtain the auto-increment primary key id, which can be obtained through the mysql_insert_id function of PHP.

$uid = mysql_insert_id();

This id is very useful. it can be used to determine whether the insert is successful or to perform other data operations as the associated ID.

11.6 obtain data query results

Through the previous chapter, we found that the operations on the PHP database are very similar to those on the MySql client. First, we connect, execute SQL statements, and then obtain the desired result set.

PHP has multiple functions to obtain a row of data in a dataset. The most common function is mysql_fetch_array. you can change the subscript of row data by setting parameters, by default, it contains the subscript of the numeric index and the associated index subscript of the field name.

$sql = "select * from user limit 1";$result = mysql_query($sql);$row = mysql_fetch_array($result);

You can set the parameter MYSQL_NUM to retrieve only the numeric index array, which is equivalent to the mysql_fetch_row function. if the parameter is set to MYSQL_ASSOC, only the correlated index array is obtained, which is equivalent to the mysql_fetch_assoc function.

$ Row = mysql_fetch_row ($ result); $ row = mysql_fetch_array ($ result, MYSQL_NUM); // The data obtained by these two methods is the same

$row = mysql_fetch_assoc($result);$row = mysql_fetch_array($result, MYSQL_ASSOC);

If you want to obtain all the data in the dataset, we use a loop to traverse the entire result set.

$data = array();while ($row = mysql_fetch_array($result)) {    $data[] = $row;}

// Connect to the database
Mysql_connect ('2014. 0.0.1 ', 'code1 ','');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
// Data preprocessing prevents data from being queried
Mysql_query ("insert into user (name, age, class) values ('Wang 'ER ', 19, 'high 3 5 class ')");
// Query data
$ SQL = "select * from user limit 1 ";
$ Result = mysql_query ($ SQL );

// Obtain a row of data here
$ Row = mysql_fetch_assoc ($ result );

Echo'

';
print_r($row);
echo '

';

11.7 querying paging data

In the previous section, we learned that all the data in a query can be obtained through a loop. in actual applications, we do not want to obtain all the data in the data table at a time, which means the performance will be very low, therefore, the paging function is used to display only 10 or 20 pieces of data per page.

Using mysql limit, you can easily implement paging. limit m and n indicate that n rows of data are retrieved from m rows, in PHP, we need to construct m and n to obtain all data on a certain page.

Assuming that the current page is $ page and $ n data entries are displayed on each page, m is all the data before the current page, which is $ m = ($ page-1) * $ n, after understanding the paging principle, we can easily flip data in PHP by constructing SQL statements.

$ Page = 2; $ n = 2; $ m = ($ page-1) * $ n; $ SQL = "select * from user limit $ m, $ n "; $ result = mysql_query ($ SQL); // cyclically obtain the data on the current page $ data = array (); while ($ row = mysql_fetch_assoc ($ result )) {$ data [] = $ row ;}

In the above example, we use the $ m and $ n variables to represent the offset and the number of data entries per page. However, we recommend that you use more meaningful variable names, such as $ pagesize, $ start, $ offset, etc. This makes it easier to understand and facilitates collaborative development.

// Connect to the database
Mysql_connect ('2014. 0.0.1 ', 'code1 ','');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
// Preset paging parameters
$ Page = 2;
$ Pagesize = 2;
// Create a paging query here
$ Start = ($ page-1) * $ pagesize;

$ SQL = "SELECT * FROM user LIMIT $ start, $ pagesize ";

// Retrieve paging data
$ Result = mysql_query ($ SQL );
$ Data = array ();
While ($ row = mysql_fetch_array ($ result, MYSQL_ASSOC )){
$ Data [] = $ row;
}
Echo'

';
print_r($data);
echo '

';

11.8 update and delete data

Data Update and deletion are relatively simple. you only need to build the corresponding SQL statement, and then call mysql_query for execution to complete the corresponding update and deletion operations.

$ SQL = "update user set name = 'caocao' where id = 2 limit 1"; if (mysql_query ($ SQL) {echo 'updated successfully ';}

You can use the following code to delete a file:

$ SQL = "delete from user where id = 2 limit 1"; if (mysql_query ($ SQL) {echo 'deleted successfully ';}

For delete and update operations, you can use the mysql_affected_rows function to obtain the number of updated data rows. if the data does not change, the result is 0.

$ SQL = "update user set name = 'caocao' where id = 2 limit 1"; if (mysql_query ($ SQL) {echo mysql_affected_rows ();}

// Connect to the database
Mysql_connect ('2014. 0.0.1 ', 'code1 ','');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
// Preset data for update
Mysql_query ("insert into user (name, age, class) values ('Wang 'ER ', 19, 'high 3 5 class ')");
$ Id = mysql_insert_id ();
// Update the row whose id is $ id to Li Bai
$ SQL = "update user set name = 'Li Bai 'where id = $ id limit 1 ";
Mysql_query ($ SQL );
// Output the number of updated data entries
Echo 'number of data update rows: '. mysql_affected_rows ();
Mysql_query ("delete from user where id = '$ ID '");

11.9 close MySQL connection

After the database operation is complete, you can use mysql_close to close the database connection. by default, when PHP is executed, the database connection is automatically closed.

mysql_close();

Although PHP will automatically close the database connection, it generally meets the needs, but in the case of high performance requirements, you can close the database connection as soon as possible after the database operations to save resources, improve performance.

When multiple database connections exist, you can set connection resource parameters to close the specified database connection.

$link = mysql_connect($host, $user, $pass);mysql_close($link);

// Connect to the database
$ Con = mysql_connect ('2017. 0.0.1 ', 'code1 ','');
Mysql_select_db ('code1 ');
Mysql_query ("set names 'utf8 '");
// Close the database connection here

Mysql_close ($ con );

Bytes .. Playing-PHP advanced, a little busy-php hi Tuesday, but I suddenly realized that this week was a little busy, and I was still looking forward to going to the city this afternoon...