Suddenly found this week a little busy playing-php advanced

Hi

Tuesday only, but I suddenly realized that this week is a bit busy to play the feeling, still very much looking forward to--this afternoon to the city, come back to watch movies in the Evening, tomorrow night to eat hot pot, the day after tomorrow's film, the evening may want to play, Friday, well, to Friday. Even though I don't know how to write (Bian) weekly, it's good to do it.

1, PHP advanced end of the article

XI. Database Operations

11.1 Which databases are supported by PHP

PHP through the installation of appropriate extensions to achieve database operations, modern application design is inseparable from the application of the database, the current mainstream database has mssql,mysql,sybase,db2,oracle,postgresql,access, These databases PHP can install extensions to support, generally speaking of the lamp architecture refers to: Linux, Apache, MySQL, PHP, so the MySQL database in PHP is very extensive, we will be in this chapter simple understanding of MySQL operation method.

11.2 Database Extensions

A database in PHP may have one or more extensions, both official and third-party. The usual extensions like MySQL have native MySQL libraries, you can also use the enhanced version of the mysqli extension, and you can use PDO for connection and operation.

Different extensions provide a basic approach to operations, but the difference is that there may be some new features, and operational performance may vary.

MySQL extension how to connect to a database:

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

MYSQLI Extensions:

$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 (' 127.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 Connecting to MySQL Database

PHP to operate the database, the first thing to do is to establish a connection to the database, usually we use the Mysql_connect function for database connection, the function needs to specify the database address, user name and password.

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

The way PHP connects to a database is similar to connecting directly under the command line, similar to: Mysql-hlocalhost-ucode1-p, when the connection is successful, we need to select an operational database and select the database through the mysql_select_db function.

mysql_select_db (' Code1 ');

Usually we will first set up the current connection using the character encoding, in general we will use UTF8 encoding.

mysql_query ("Set names ' UTF8 '");

With the above steps, we have established a connection with the database and can perform data manipulation.

$host = ' 127.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 Executing a MySQL query

After the database is established, queries can be made, and query instructions are sent to the database in the form of mysql_query plus SQL statements.

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

The statement for the query class returns a resource handle (resource) from which the data in the query result set can be obtained.

$row = Mysql_fetch_array ($res); Var_dump ($row);

By default, PHP executes the query using the most recent database connection, but if there are multiple connections, you can query from that connection by parameter directives.

$link 1 = mysql_connect (' 127.0.0.1 ', ' code1 ', '); $link 2 = mysql_connect (' 127.0.0.1 ', ' code1 ', ', true); Open a new connection $res = mysql_query (' select * from user Limit 1 ', $link 1); Querying data from the first connection

Connecting to a database
mysql_connect (' 127.0.0.1 ', ' code1 ', ');
mysql_select_db (' Code1 ');
mysql_query ("Set names ' UTF8 '");
Do a data query here
$arr =mysql_query ("SELECT * from User limit 1");
$row =mysql_fetch_row ($arr);
Print_r ($row);
echo $row [0];

11.5 Inserting new data into MySQL

When we understand how to use mysql_query for data queries, then, similarly, inserting data is actually done by executing an SQL statement, for example:

$sql = "INSERT into user (name, age, Class) VALUES (' John Doe ', 18, ' 31 classes ')"; mysql_query ($sql); Execute INSERT statement

Usually data is stored in variables or arrays, so SQL statements need to be preceded by string concatenation.

$name = ' John doe '; $age = +; $class = ' high 31 classes '; $sql = "INSERT into user (name, age, Class) VALUES (' $name ', ' $age ', ' $class ')"; MySQL _query ($sql); Execute INSERT statement

In MySQL, after executing the INSERT statement, you can get the self-increment primary key ID, which can be obtained through PHP's mysql_insert_id function.

$uid = mysql_insert_id ();

This ID is very useful, and can often be used to determine whether the insert succeeds or to perform other data operations as an association ID.

11.6 Getting data query results

From the previous chapters, we found that the PHP operating database is very similar to the operation on the MySQL client, first connecting, then executing the SQL statement, and then getting the result set we want.

PHP has several functions to get a row of data in the dataset, most commonly used is mysql_fetch_array, you can set parameters to change the subscript of the data, the default will include the index of the number of indexes and the field name of the associated index subscript.

$sql = "SELECT * from user limit 1"; $result = mysql_query ($sql); $row = Mysql_fetch_array ($result);

You can only get a numeric index array by setting the parameter Mysql_num, which is equivalent to the Mysql_fetch_row function, and if the set parameter is MYSQL_ASSOC, only the associative index array, 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 get all the data in a dataset, we iterate through the entire result set through a loop.

$data = Array (), while ($row = Mysql_fetch_array ($result)) {    $data [] = $row;}

Connecting to a database
mysql_connect (' 127.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 II ', 19, ' 35 classes ')");
Make a data query
$sql = "SELECT * from user limit 1";
$result = mysql_query ($sql);

Get 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 for a query can be retrieved through a loop, and in practice we don't want to get all the data in the data table at once, so the performance is very low, so we use page flipping, which shows only 10 or 20 data per page.

With MySQL's limit can be very easy to implement paging, limit m,n to take n rows from the M row data, in PHP we need to construct M and N to achieve all the data of a page.

Assuming that the current page is $page, each page displays $n data, then M is the data in front of the current page, both $m = ($page-1) * $n, after we know the page flipping principle, it is easy to flip the 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);//loop to get data for the current page $d ATA = Array (), while ($row = Mysql_fetch_assoc ($result)) {    $data [] = $row;}

In the example above, we used the $m and $n variables to represent the offsets and the number of data bars per page, but we recommend using more meaningful variable names, such as $pagesize, $start, $offset, and so on, which is easier to understand and facilitates team collaboration development.

Connecting to a database
mysql_connect (' 127.0.0.1 ', ' code1 ', ');
mysql_select_db (' Code1 ');
mysql_query ("Set names ' UTF8 '");
Preset paging parameters
$page = 2;
$pagesize = 2;
Build a paging query here
$start = ($page-1) * $pagesize;

$sql = "SELECT * from user LIMIT $start, $pagesize";

Get page flipping data
$result = mysql_query ($sql);
$data = Array ();
while ($row = Mysql_fetch_array ($result, Mysql_assoc)) {
$data [] = $row;
}
Echo '

';
Print_r ($data);
Echo '

';

11.8 Updating and deleting data

The update and deletion of data is relatively simple, only need to build the corresponding SQL statement, and then call mysql_query execution can complete the corresponding update and delete operation.

$sql = "Update user set name = ' Caocao ' where id=2 limit 1", if (mysql_query ($sql)) {    echo ' update succeeded ';}

The same deletion can use code similar to the following:

$sql = "Delete from user where id=2 limit 1", if (mysql_query ($sql)) {    echo ' delete succeeded ';}

For delete and update operations, the Mysql_affected_rows function can be used to get the number of updated rows of data, and 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 ();}

Connecting to a database
mysql_connect (' 127.0.0.1 ', ' code1 ', ');
mysql_select_db (' Code1 ');
mysql_query ("Set names ' UTF8 '");
Preset data for update operations
mysql_query ("INSERT into user (name, age, Class) values (' Wang II ', 19, ' 35 classes ')");
$id = mysql_insert_id ();
Here update the name of the line with ID $id for Li Bai
$sql = "Update user set Name= ' Li Bai ' where id= $id limit 1";
mysql_query ($sql);
Output Update Data Bar number
echo ' Data update line number: '. mysql_affected_rows ();
mysql_query ("Delete from user where id= ' $id '");

11.9 Turn off MySQL connection

When the database operation is complete, you can use Mysql_close to close the database connection, by default, when PHP is finished, the database connection is automatically closed.

Mysql_close ();

Although PHP automatically shuts down the database connection and generally satisfies the requirements, in the case of high performance requirements, you can close the database connection as soon as possible after the database operation to save resources and improve performance.

In the case of multiple database connections, you can set the connection resource parameter to close the specified database connection.

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

Connecting to a database
$con =mysql_connect (' 127.0.0.1 ', ' code1 ', ');
mysql_select_db (' Code1 ');
mysql_query ("Set names ' UTF8 '");
Close the database connection here

Mysql_close ($con);

