Home Backend Development PHP Tutorial PHP database operations

PHP database operations

Aug 08, 2016 am 09:20 AM
mysql query quot user

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:

<span>$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');</span><p><span>mysqli extension: </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><span>$link = mysqli_connect('mysql_host', 'mysql_user', 'mysql_password');</span><p><span>PDO extension</span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$dsn = 'mysql:dbname=testdb;host=127.0.0.1';</span></p><span></span><p><span>$user = 'dbuser';</span></p><p><span>$password = 'dbpass';</span></p><p><span>$dbh = new PDO($dsn, $user, $password);</span></p><divmicrosoft yahei sans gb neue font-size:14px line-height:21px><p><span>Connect to MySQL database</span></p> <divmicrosoft yahei sans gb neue font-size:14px line-height:21px margin:0px padding:0px word-break:break-all><p></p> <p><span>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. </span></p> <preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$host = 'localhost';</span></p><span></span><p><span>$user = 'code1';</span></p><p><span>$link = mysql_connect($host, $user, $pass);</span></p><p><span>$pass = '';</span></p><p><span>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></span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><span>mysql_select_db('code1');</span><p><span>Usually we will first set the character encoding used for the current connection. Generally, we will use utf8 encoding. </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><span>mysql_query("set names 'utf8'");</span><p><span>Through the above steps, we have established a connection with the database and can perform data operations. </span></p><p><span>Execute MySQL query</span></p><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><p><span>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. </span></p> <preubuntu mono line-height:1.6em font-size:13px word-break:break-word><span>$res = mysql_query('select * from user limit 1');</span><p><span>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. </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$row = mysql_fetch_array($res);</span></p><span></span><p><span>var_dump($row);</span></p><p><span>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. </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$link1 = mysql_connect('127.0.0.1', 'code1', '');</span></p><span></span><p><span>$link2 = mysql_connect('127.0.0.1', 'code1', '', true); //开启一个新的连接</span></p><p><span>$res = mysql_query('select * from user limit 1', $link1); //从第一个连接中查询数据</span></p><divmicrosoft yahei sans gb neue font-size:14px line-height:21px><p><span>Insert new data into MySQL</span></p> <divmicrosoft yahei sans gb neue font-size:14px line-height:21px><p></p> <p><span>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: </span> </p> <preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$sql = "insert into user(name, age, class) values('李四', 18, '高三一班')";</span></p><span></span><p><span>mysql_query($sql); //执行插入语句</span></p><p><span>Usually data is stored in variables or arrays, so the sql statement needs to be string spliced ​​first. </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$name = '李四';</span></p><span></span><p><span>$age = 18;</span></p><p><span>$class = '高三一班';</span></p><p><span>$sql = "insert into user(name, age, class) values('$name', '$age', '$class')";</span></p><p><span>mysql_query($sql); //执行插入语句</span></p><p><span>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. </span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><span>$uid = mysql_insert_id();</span><p><span>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. </span></p><p><span>Getting data query results</span></p><p><divmicrosoft yahei sans gb neue font-size:14px><p><span>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. </span></p> <p><span>PHP有多个函数可以获取数据集中的一行数据,最常用的是mysql_fetch_array,可以通过设定参数来更改行数据的下标,默认的会包含数字索引的下标以及字段名的关联索引下标。</span></p> <preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$sql = "select * from user limit 1";</span></p><span></span><p><span>$result = mysql_query($sql);</span></p><p><span>$row = mysql_fetch_array($result);</span></p><p><span>可以通过设定参数MYSQL_NUM只获取数字索引数组,等同于mysql_fetch_row函数,如果设定参数为MYSQL_ASSOC则只获取关联索引数组,等同于mysql_fetch_assoc函数。</span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$row = mysql_fetch_row($result);</span></p><span></span><p><span>$row = mysql_fetch_array($result, MYSQL_NUM); //这两个方法获取的数据是一样的</span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$row = mysql_fetch_assoc($result);</span></p><span></span><p><span>$row = mysql_fetch_array($result, MYSQL_ASSOC);</span></p><p><span>如果要获取数据集中的所有数据,我们通过循环来遍历整个结果集。</span></p><preubuntu mono line-height:1.6em font-size:13px word-break:break-word><p><span>$data = array();</span></p><span></span><p><span>while ($row = mysql_fetch_array($result)) {</span></p><p><span> $data[] = $row;</span></p><p><span>}</span></p></preubuntu></preubuntu></preubuntu></preubuntu></divmicrosoft></p><divmicrosoft yahei sans gb neue font-size:14px line-height:21px><p>查询分页数据</p> <divmicrosoft yahei sans gb neue font-size:14px line-height:21px><p></p> <p>上一节中,我们了解到通过循环可以获取一个查询的所有数据,在实际应用中,我们并不希望一次性获取数据表中的所有数据,那样性能会非常的低,因此会使用翻页功能,每页仅显示10条或者20条数据。</p> <p>通过mysql的limit可以很容易的实现分页,limit m,n表示从m行后取n行数据,在PHP中我们需要构造m与n来实现获取某一页的所有数据。</p> <p>假定当前页为$page,每页显示$n条数据,那么m为当前页前面所有的数据,既$m = ($page-1) * $n,在知道了翻页原理以后,那么我们很容易通过构造SQL语句在PHP中实现数据翻页。</p> <preubuntu mono line-height:1.6em font-size:13px word-break:break-word background:rgb><p><span>$page = 2;</span></p><p><span>$n = 2;</span></p><p><span>$m = ($page - 1) * $n;</span></p><p><span>$sql = "select * from user limit $m, $n";</span></p><p><span>$result = mysql_query($sql);</span></p><p><span>//循环获取当前页的数据</span></p><p><span>while ($row = mysql_fetch_assoc($result)) {</span></p><p><span>$data = array();</span></p><p><span> $data[] = $row;</span></p><p><span>}</span></p><p>在上面的例子中,我们使用了$m与$n变量来表示偏移量与每页数据条数,但我们推荐使用更有意义的变量名来表示,比如$pagesize, $start, $offset等,这样更容易理解,有助于团队协作开发。</p><p></p><pre name="code"><?php //连接数据库 mysql_connect(&#39;127.0.0.1&#39;, &#39;code1&#39;, &#39;&#39;); mysql_select_db(&#39;code1&#39;); mysql_query("set names &#39;utf8&#39;"); //预设翻页参数 $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 &#39;<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会自动关闭数据库连接,一般情况下已经满足需求,但是在对性能要求比较高的情况下,可以在进行完数据库操作之后尽快关闭数据库连接,以节省资源,提高性能。

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

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



版权声明:本文为博主原创文章,未经博主允许不得转载。

以上就介绍了PHP数据库操作,包括了方面的内容,希望对PHP教程有兴趣的朋友有所帮助。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles