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

I suddenly found myself a little busy this week. . Just playing - PHP advanced, a little busy - php

　hi

It’s only Tuesday, but I suddenly realized that this week is a bit busy, and I’m still looking forward to it – I’ll go to the city this afternoon, come back to watch a movie in the evening, have a dinner party for hot pot tomorrow night, shoot a short film in the afternoon the day after tomorrow, and maybe in the evening To play, Friday, well, it’s Friday. Although I don’t even know how to write a (bian) weekly report if this continues, it’s still a good idea to do this.

1. PHP Advanced Completion

11. Database Operation

11.1 What databases does PHP support?

PHP implements database operations by installing corresponding extensions. The design of modern applications is inseparable from the application of databases. The current mainstream databases include MsSQL, MySQL, Sybase, Db2, Oracle, PostgreSQL, Access, etc., these databases PHP Extensions can be installed to support it. Generally speaking, the LAMP architecture refers to: Linux, Apache, Mysql, and PHP. Therefore, Mysql database is widely used in PHP. We will briefly understand the operation method of Mysql in this chapter.

11.2 Database Extension

A database in PHP may have one or more extensions, including official ones and those provided by third parties. Commonly used extensions for Mysql include the native mysql library, you can also use the enhanced mysqli extension, and you can also use PDO for connection and operation.

Different extensions provide basically similar operation methods. The difference is that they may have some new features and the operation performance may be different.

Mysql extension method for database connection:

$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('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

To operate the database in PHP, the first thing to do is to establish a connection with the database. Usually we use the mysql_connect function to connect to the database. This function requires specifying the address, username and password of the database.

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

The way PHP connects to the 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 a database for operation and select the database through the mysql_select_db function.

mysql_select_db('code1');

Usually 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 and can perform data operations.

$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 Execute MySQL query

After establishing a connection to the database, you can query, using the form of mysql_query plus sql statement to send query instructions to the database.

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

For query class statements, a resource handle (resource) will be returned, and the data in the query result set can be obtained through this resource.

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

By default, PHP uses the nearest database connection to execute the query, but if there are multiple connections, you can query from that connection through the parameter command.

$link1 = mysql_connect('127.0.0.1', 'code1', '');
$link2 = mysql_connect('127.0.0.1', 'code1', '', true); //开启一个新的连接
$res = mysql_query('select * from user limit 1', $link1); //从第一个连接中查询数据

//Connect to the database
mysql_connect('127.0.0.1', 'code1', '');
mysql_select_db('code1');
mysql_query("set names 'utf8'");
//Perform data query 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 we understand how to use mysql_query for data query, similarly, inserting data is actually achieved by executing a sql statement, for example:

$sql = "insert into user(name, age, class) values('李四', 18, '高三一班')";
mysql_query($sql); //执行插入语句

Usually data is stored in variables or arrays, so the sql statement needs to be string spliced ​​first.

$name = '李四';
$age = 18;
$class = '高三一班';
$sql = "insert into user(name, age, class) values('$name', '$age', '$class')";
mysql_query($sql); //执行插入语句

In mysql, after executing the insert statement, you can get the auto-incremented primary key id, which can be obtained through PHP's mysql_insert_id function.

$uid = mysql_insert_id();

This ID is very useful. It can usually be used to determine whether the insertion is successful, or as an associated ID for other data operations.

11.6 Get data query results

Through the previous chapters, we found that operating the database in PHP is very similar to the operation on the MySql client. First connect, then execute the sql statement, and then obtain the result set we want.

PHP has multiple functions that can obtain a row of data in the data set. The most commonly used is mysql_fetch_array. You can change the subscript of the row data by setting parameters. The default will include the subscript of the numeric index and the associated index of the field name. subscript.

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

可以通过设定参数MYSQL_NUM只获取数字索引数组，等同于mysql_fetch_row函数，如果设定参数为MYSQL_ASSOC则只获取关联索引数组，等同于mysql_fetch_assoc函数。

$row = mysql_fetch_row($result);
$row = mysql_fetch_array($result, MYSQL_NUM); //这两个方法获取的数据是一样的

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

如果要获取数据集中的所有数据，我们通过循环来遍历整个结果集。

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

//连接数据库
mysql_connect('127.0.0.1', 'code1', '');
mysql_select_db('code1');
mysql_query("set names 'utf8'");
//数据预处理 防止查询不到数据
mysql_query("insert into user(name, age, class) values('王二', 19, '高三五班')");
//进行数据查询
$sql = "select * from user limit 1";
$result = mysql_query($sql);

//在这里获取一行数据
$row=mysql_fetch_assoc($result);

echo '

';<br>print_r($row);<br>echo '

';

11.7 查询分页数据

上一节中，我们了解到通过循环可以获取一个查询的所有数据，在实际应用中，我们并不希望一次性获取数据表中的所有数据，那样性能会非常的低，因此会使用翻页功能，每页仅显示10条或者20条数据。

通过mysql的limit可以很容易的实现分页，limit m,n表示从m行后取n行数据，在PHP中我们需要构造m与n来实现获取某一页的所有数据。

假定当前页为$page，每页显示$n条数据，那么m为当前页前面所有的数据，既$m = ($page-1) * $n，在知道了翻页原理以后，那么我们很容易通过构造SQL语句在PHP中实现数据翻页。

$page = 2;
$n = 2;
$m = ($page - 1) * $n;
$sql = "select * from user limit $m, $n";
$result = mysql_query($sql);
//循环获取当前页的数据
$data = array();
while ($row = mysql_fetch_assoc($result)) {
    $data[] = $row;
}

在上面的例子中，我们使用了$m与$n变量来表示偏移量与每页数据条数，但我们推荐使用更有意义的变量名来表示，比如$pagesize, $start, $offset等，这样更容易理解，有助于团队协作开发。

//连接数据库
mysql_connect('127.0.0.1', 'code1', '');
mysql_select_db('code1');
mysql_query("set names 'utf8'");
//预设翻页参数
$page = 2;
$pagesize = 2;
//在这里构建分页查询
$start=($page-1)*$pagesize;

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

//获取翻页数据
$result = mysql_query($sql);
$data = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$data[] = $row;
}
echo '

';<br>print_r($data);<br>echo '

';

11.8 更新与删除数据

数据的更新与删除相对比较简单，只需要构建好相应的sql语句，然后调用mysql_query执行就能完成相应的更新与删除操作。

$sql = "update user set name = '曹操' where id=2 limit 1";
if (mysql_query($sql)) {
    echo '更新成功';
}

同样的删除可以使用类似以下的代码：

$sql = "delete from user where id=2 limit 1";
if (mysql_query($sql)) {
    echo '删除成功';
}

对于删除与更新操作，可以通过mysql_affected_rows函数来获取更新过的数据行数，如果数据没有变化，则结果为0。

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

//连接数据库
mysql_connect('127.0.0.1', 'code1', '');
mysql_select_db('code1');
mysql_query("set names 'utf8'");
//预设数据以便进行更新操作
mysql_query("insert into user(name, age, class) values('王二', 19, '高三五班')");
$id = mysql_insert_id();
//在这里更新id为$id的行的名字为李白
$sql="update user set name='李白' where id=$id limit 1";
mysql_query($sql);
//输出更新数据条数
echo '数据更新行数：'.mysql_affected_rows();
mysql_query("delete from user where id='$id'");

11.9 关闭MySQL连接

当数据库操作完成以后，可以使用mysql_close关闭数据库连接，默认的，当PHP执行完毕以后，会自动的关闭数据库连接。

mysql_close();

虽然PHP会自动关闭数据库连接，一般情况下已经满足需求，但是在对性能要求比较高的情况下，可以在进行完数据库操作之后尽快关闭数据库连接，以节省资源，提高性能。

在存在多个数据库连接的情况下，可以设定连接资源参数来关闭指定的数据库连接。

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

//连接数据库
$con=mysql_connect('127.0.0.1', 'code1', '');
mysql_select_db('code1');
mysql_query("set names 'utf8'");
//在这里关闭数据库连接

mysql_close($con);

 

 

 

 

 

http://www.bkjia.com/PHPjc/1077136.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1077136.htmlTechArticleI suddenly found myself a little busy this week. . Playing - PHP advanced, a little busy - php hi It’s only Tuesday, but I suddenly realized that I am a little busy playing this week. I am still looking forward to going to the city this afternoon, late...