PHP database operations-PHP Tutorial-php.cn

Which 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 can install extensions to support it. Generally speaking, the LAMP architecture refers to: Linux, Apache, Mysql, PHP, so the Mysql database is widely used in PHP. We will discuss this in this chapter. Learn how to operate Mysql in a simple way.

Database extensions

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 version of 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: <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$link = mysqli_connect('mysql_host', 'mysql_user', 'mysql_password');PDO extension<preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$dsn = 'mysql:dbname=testdb;host=127.0.0.1';$user = 'dbuser';$password = 'dbpass';$dbh = new PDO($dsn, $user, $password);<divmicrosoft yahei sans gb neue font-size:14px line-height:21px>Connect to MySQL database <divmicrosoft yahei sans gb neue font-size:14px line-height:21px margin:0px padding:0px word-break:break-all> PHP needs to perform operations on the database Operation, 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 needs to specify the address, user name and password of the database. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$host = 'localhost';$user = 'code1';$link = mysql_connect($host, $user, $pass);$pass = '';The way PHP connects to the database is similar to connecting directly under the command line, similar to: <codeubuntu mono>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. </codeubuntu><preubuntu mono line-height:1.6em font-size:13px word-break:break-word>mysql_select_db('code1');Usually we will first set the character encoding used for the current connection. Generally, we will use utf8 encoding. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>mysql_query("set names 'utf8'");Through the above steps, we have established a connection with the database and can perform data operations. Execute MySQL query<divmicrosoft yahei sans gb neue font-size:13px line-height:1.6 margin:0px padding:0px word-break:break-all><divmicrosoft yahei sans gb neue font-size:14px>You can query after the database connection is established, and use the form of mysql_query plus sql statement to send query instructions to the database. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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); //从第一个连接中查询数据<divmicrosoft yahei sans gb neue font-size:14px line-height:21px>Insert new data into MySQL <divmicrosoft yahei sans gb neue font-size:14px line-height:21px> After we understand how to use mysql_query for data query, then similarly, inserting data is actually achieved by executing a sql statement, for example: <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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. <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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. Getting data query results<divmicrosoft yahei sans gb neue font-size:14px>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 get the results we want. set. PHP有多个函数可以获取数据集中的一行数据，最常用的是mysql_fetch_array，可以通过设定参数来更改行数据的下标，默认的会包含数字索引的下标以及字段名的关联索引下标。 <preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$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函数。<preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$row = mysql_fetch_row($result);$row = mysql_fetch_array($result, MYSQL_NUM); //这两个方法获取的数据是一样的<preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$row = mysql_fetch_assoc($result);$row = mysql_fetch_array($result, MYSQL_ASSOC);如果要获取数据集中的所有数据，我们通过循环来遍历整个结果集。<preubuntu mono line-height:1.6em font-size:13px word-break:break-word>$data = array();while ($row = mysql_fetch_array($result)) { $data[] = $row;}</preubuntu></preubuntu></preubuntu></preubuntu></divmicrosoft><divmicrosoft yahei sans gb neue font-size:14px line-height:21px>查询分页数据 <divmicrosoft yahei sans gb neue font-size:14px line-height:21px> 上一节中，我们了解到通过循环可以获取一个查询的所有数据，在实际应用中，我们并不希望一次性获取数据表中的所有数据，那样性能会非常的低，因此会使用翻页功能，每页仅显示10条或者20条数据。 通过mysql的limit可以很容易的实现分页，limit m,n表示从m行后取n行数据，在PHP中我们需要构造m与n来实现获取某一页的所有数据。 假定当前页为$page，每页显示$n条数据，那么m为当前页前面所有的数据，既$m = ($page-1) * $n，在知道了翻页原理以后，那么我们很容易通过构造SQL语句在PHP中实现数据翻页。 <preubuntu mono line-height:1.6em font-size:13px word-break:break-word background:rgb>$page = 2;$n = 2;$m = ($page - 1) * $n;$sql = "select * from user limit $m, $n";$result = mysql_query($sql);//循环获取当前页的数据while ($row = mysql_fetch_assoc($result)) {$data = array(); $data[] = $row;}在上面的例子中，我们使用了$m与$n变量来表示偏移量与每页数据条数，但我们推荐使用更有意义的变量名来表示，比如$pagesize, $start, $offset等，这样更容易理解，有助于团队协作开发。<pre name="code"><?php //连接数据库 mysql_connect('127.0.0.1', 'code1', ''); mysql_select_db('code1'); mysql_query("set names 'utf8'"); //预设翻页参数 $page = 2; $pagesize = 2; //在这里构建分页查询 $count = ($page - 1) * $pagesize; $sql = "select * from user limit $count, $pagesize"; //获取翻页数据 $result = mysql_query($sql); $data = array(); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $data[] = $row; } echo '<pre class="brush:php;toolbar:false">'; print_r($data); echo '';

Copy after login

更新与删除数据

数据的更新与删除相对比较简单，只需要构建好相应的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连接

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

mysql_close();

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