Detailed explanation of the operation of mysql database connection using PHP

Although PHP4 has implemented some object-oriented methods, it is only part of it. A language with such a high usage rate is not object-oriented. Maybe the fresh man who is new to the battlefield will find it incredible. But this is indeed the case. Only after PHP5 did the most important MySQL operations become OOP, and optimizations were made in terms of performance, security, etc. It can be said that the release of PHP5 restored the fans who were gradually leaving, and made it possible to write great PHP projects. It's easier. After OOP, more and more excellent frameworks were born, providing efficient equipment for enterprise production lines. In this article, let’s take a look at the operation of PHP-MySQL.

Why is PHP so popular all over the world, and it still occupies the top spot of WEB websites? There are many reasons. I personally think that among the many reasons there must be these two reasons:

• #1. One is that PHP supports both process-oriented and object-oriented. To put it bluntly, it is accessible to all ages. Eat, children can use it, and adults can also play it. Children can grow up while playing in PHP.

• 2. The other is that PHP is a scripting language, that is, an analytical language. It can run directly under WEB servers such as Apache without compilation. This also lays the foundation for the outbreak of VPS. A foreshadowing was made.

Before we start, let’s take a look at the life cycle of a database operation: > Connect to the database->CRUD->Release resources->Close the connection

Such a logic is actually the same everywhere, but the level of abstraction in each logical step is different. Okay, back to the topic, let’s first see how primary school students can write php-mysql.

1. Method 1: Primary school students

<?php//连接$link = mysql_connect($dbHost, $dbUser, $dbPasswd);//选择数据库mysql_select_db($dbName);//数据库中的查询$result = mysql_query($sql);//从结果集中取值$row = mysql_fetch_array($result,MYSQL_ASSOC);//释放mysql_free_result($result);//关闭mysql_close($link);?>

This method of database operation is the original combination of PHP and MySQL. However, this method does not support parameter binding and is prone to SQL injection attacks. For example, assigning a value to $sex that is malicious or not what we want. SQL statement, the consequences will be very serious, and it will be directly operated on the database.

Speaking of the security issues of database operations, general operations:

1. Filter the blank characters before and after the input
2. Filter control characters
3. Execute SQL

Use trim to remove leading and trailing whitespace characters, and then use addslashes to escape control characters. addslashes can escape single and double quotation marks, backslash (\) and NULL characters. In fact, PHP can help us automatically escape control characters. You can use get_magic_quotes_gpc to determine whether PHP has automatically escaped control characters for us.

<?phpif(!get_magic_quotes_gpc()){$param = addslashes($param);}?>

上面这个是最原始的PHP操作MySQL的做法，确实跟C很像，纯粹是面向过程的。下面我们来看一下MySQL的进步。

二、方式二：初中生

PHP5中对MySQL操作也进行了优化升级，也就是mysqli函数库。 这一节我们还是按面向过程的方式(Procedural style)来看一下mysqli的操作。

<?php$link = mysqli_connect($dbHost,$dbUser,$dbPasswd,$dbName);//如果上面没的指定数据库，这里还是可以选择//mysqli_select_db($link,$dbName);$result = mysqli_query($link,$sql);//行数$numResults = mysqli_num_rows($result);//$row = mysqli_fetch_assoc($result);//或者将结果取出到列表数组中，然后可以使用row[0],row[1]...的形式进行取值$row = mysqli_fetch_row($result);//或者可以将结果中的一行取回给一个对象$row = mysqli_fetch_object($result);mysqli_free_result($result);mysqli_close($result);?>

到目前为止，好像mysqli和之前的mysql并没有什么区别，其实以上的操作mysqli都进行了大量的优化，但是给我们印象最深的可能还是Prepared语句。 Prepared语句的操作我们放在高中课程来讲,虽然Prepared也支持面向过程，但是操作流程都一样，只是调用方式不一样，所以后面一起讨论。

三、方式三：高中生

其中这一节的操作我们还是使用mysqli，不过，我们使用它面向对象的形式（Object oriented style）.

<?php$link = new mysqli($dbHost,$dbUser,$dbPasswd,$dbName);//如果上面没的指定数据库，这里还是可以选择//$link->select_db($dbName);$result = $link->query($sql);//行数$numResults = $result->num_rows;//$row = $result->fetch_assoc();//或者将结果取出到列表数组中，然后可以使用row[0],row[1]...的形式进行取值$row = $result->fetch_row();//或者可以将结果中的一行取回给一个对象$row = $result->fetch_object();$result->free();$link->close();?>

上面就是我们进行MySQL操作最常用的面向对象形式的。接下来我们来讨论下Prepared语句。Prepared语句的基本思想就是向MySQL发送一个查询模板，然后再单独发送对应的数据，所以模板可以是同一个，但是发送的数据可以是大量的，这样一来对于批量数据插入操作非常有用。我们来看一个例子：

<?php$link = new mysqli($dbHost,$dbUser,$dbPasswd,$dbName);$query = "insert into User values(?,?,?,?)";$stmt = $link->prepare($query);$stmt->bind_param("sssi",$name,$email,$password,$sex);$stmt->execute();echo $stmt->affected_rows. 'user inserted into db';$stmt->close();?>

需要注意的地方是bind_param方法中第一个参数”sssd”代表后面每个字段的格式，i代表integer，d代表double，s代表string，b代表blog.

我们同样可以使用bind_result()像bind_param()那样绑定查询的结果：

<?php$link = new mysqli($dbHost,$dbUser,$dbPasswd,$dbName);$query = "select id,name,email,sex from User order by id limit 10";$stmt = $link->prepare($query);if ($stmt) {$stmt->execute();$stmt->bind_result($id,$name,$email,$sex);while ($stmt->fetch()) {echo $id . ','.$name.','.$email.','$sex;}$stmt->close();}$link->close();?>

四、方式四：大学生

大学生意味着就成年了，也就是说看待问题就更加的宏观了，我们可以看到上面的这些mysql或者mysqli方法都是针对mysql的，如果我们更换了数据库，那这些都将无法使用，于是我们可以像数据库的操作更加的抽象化，将数据库的操作透明化，用户对数据库的操作不应该限定为某一个数据库。市面上有很多的数据抽象层，如PDO、ADODC、PHPLib、DBA、dbx、ODBC等等，我们这里选择PDO。PDO支持众多数据库，可以使用

PDO::getAvailableDrivers()查询系统中PDO支持的数据库。

<?php$dsn = "mysql:host=%dbHost;dbname=$dbName";$dbh = new PDO($dsn,$dbUser,$dbPassword);$sql = "select `id`,`name`,`teamId`,`email`,`sex` from User  where `sex`=?,`teamId`=?";$sth = $dbh->prepare($sql);$sth->setFetchMode(PDO::FETCH_OBJ);while ($row = $sth->fetch()) {echo $row->id.','$row->name.','.$row->email;}$dbh = NULL;?>

OK,上面是一个最基本的PDO操作示例，我们一起来好好研究下PDO如何使用。

1、异常处理

PDO可以使用异常处理，所以所有的PDO操作我们都应该放在try,catch块中，PDO可以使用3种错误模式处理新建句柄时的错误。

<?php$dsn = "mysql:host=%dbHost;dbname=$dbName";$dbh = new PDO($dsn,$dbUser,$dbPassword);$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);?>

根据参数名称，我们就可以想到几个模式的意思，静默、警告、抛异常。

<?phptry{$dbh = new PDO(&#39;mysql:host=$host;dbname=$dbName&#39;,$user,$pass);$dbh = setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//没有xx字段$dbh = prepare(&#39;select xx from User&#39;);}catch(PDOException $e){echo &#39;PDO Error:&#39;.$e->getMessage();}?>

2、insert与update操作
我们看一下基本的操作流程：

Prepare -> Bind ->execute

<?php$sth = $link->prepare("insert into User(name) values('Grey')");$sth->execute();?>

prepare的占位符方式：

• 无占位符:

<?php$sth = $link->prepare("insert into User(name,email,password,sex) values($name,$email,$password,$sex)");?>

• 无名占位符:

<?phpsth = $link->prepare("insert into User(name,email,password,sex) values(?,?,?,?)");$sth->bindParam(1,$name);$sth->bindParam(2,$email);$sth->bindParam(3,$password);$sth->bindParam(4,$sex);//$name = 'Grey';$email = 'guohui.great@gmail.com';$password = '123456';$sex = 1;$sth->execute();//$name = 'Joseph';$email = 'joseph@gmail.com';$password = '654321';$sex = 0;$sth->execute();?>

每插入一条数据都要这样进行参数绑定，然后指定数据执行，效率确实有点低，数据也可以直接通过Array进行数据指定。

<?php$data = array(&#39;WeiFocus&#39;,&#39;weifocus@weifocus.com&#39;,&#39;111111&#39;,1);$sth = $link->prepare("insert into User(name,email,password,sex) values(?,?,?,?)");$sth->execute($data);?>

$data数组的数据将按顺序依次填充占位符。

• 命名占位符:

<?php$data = array(&#39;name&#39;=>'Paul','email'=>'paul@gmail.com','password'=>'123456','sex'=>1);$sth = $link->prepare('insert into User(name,email,password,sex) value(:name,:email,:password,:sex)');$sth->execute($data);?>

3、查询数据

<?php//设定数据获取方式$sth->setFetchMode(PDO::FETCH_ASSOC);$sth->fetch();?>

我们看一下PHP文档中关于此处数据获取方式的说明：

缺省为 PDO::ATTR_DEFAULT_FETCH_MODE 的值 （默认为 PDO::FETCH_BOTH ）。
PDO::FETCH_ASSOC：返回一个索引为结果集列名的数组
PDO::FETCH_BOTH（默认）：返回一个索引为结果集列名和以0开始的列号的数组
PDO::FETCH_BOUND：返回 TRUE ，并分配结果集中的列值给 PDOStatement::bindColumn() 方法绑定的 PHP 变量。
PDO::FETCH_CLASS：返回一个请求类的新实例，映射结果集中的列名到类中对应的属性名。如果 fetch_style 包含 PDO::FETCH_CLASSTYPE（例如：PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE），则类名由第一列的值决定
PDO::FETCH_INTO：更新一个被请求类已存在的实例，映射结果集中的列到类中命名的属性
PDO::FETCH_LAZY：结合使用 PDO::FETCH_BOTH 和 PDO::FETCH_OBJ，创建供用来访问的对象变量名
PDO::FETCH_NUM：返回一个索引为以0开始的结果集列号的数组
PDO::FETCH_OBJ：返回一个属性名对应结果集列名的匿名对象

其中最常用的就是PDO::FETCH_ASSOC、PDO::FETCH_OBJ、PDO::FETCH_CLASS三种取值模式。

• PDO::FETCH_ASSOC:
  这种模式的时候，fetch()调用的时候将会创建一个关联数组，然后可以通过列的名称索引数组。

<?php$sth = $link->query("select name,email,sex from User");$sth->setFetchMode(PDO::FETCH_ASSOC);while($row=$sth->fetch()){echo $row['name'].','.$row['email'].','.$row['sex'];}?>

• PDO::FETCH_OBJ:
  这种模式，fetch()将为返回的每一行创建一个标准对象。

<?php$sth = $link->query("select name,email,sex from User");$sth->setFetchMode(PDO::FETCH_OBJ);while($row=$sth->fetch()){echo $row->name.','.$row->email.','.$row->sex;}?>

• PDO::FETCH_CLASS: 
  这种模式将返回的结果直接填充到设定的类中，与类成员建立一一对应的关系。 使用这种模式时需要注意的是，我们都知道构造函数在对象创建时是第一个被调用的，但是这里略有不同，对象的成员在构造函数被调用前都已经被赋值了，

<?phpclass User{public $name;public $email;public $sex;function __construct($nameMark = &#39;【WeiFocusIo】&#39;){$this->name .= $nameMark;}} $sth = $link->query('select name,email,sex from User');$sth->setFetchMode(PDO::FETCH_CLASS,'User');while($obj = $sth->fetch()){echo $obj->name;}?>

当需要构造函数在数据填充之前就被调用，可以设置fetch模式为：

<?php$sth->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE,'User');?>

咱们对比一下这两种模式的结果会发现前者的name会添加后缀，而后者并没有后缀。
到目前为止，我们还没有看到如何给构造函数传值，其实fetch可以在后面添加参数：

<?php> $sth->setFetchMode(PDO::FETCH_CLASS,'User',array($nameMark));?>

It should be noted that the value mode set by fetch is for the next set of values.

Before ending the PDO section, let’s look at some common operation methods:

$link->lastInsertId(); will return the last piece of data inserted by the connection The self-increasing ID value.

$link->exec($sql); is used to execute commands that have no return value or affect row data,

$ link->quote($unsafe); Filters string quotes, used when prepare preprocessing is not used.

$sth->rowCount(); Returns the number of data rows affected by an operation.

5. Method Five: Company Practice

The above methods are all content for academic study, but in our actual production environment, We are facing a large-scale project. Operating the database in this way will inevitably involve too many redundant operations. We will waste a lot of time writing SQL statements for the database, and a large number of places in the code are filled with SQL, which does not reflect object orientation. The idea is a bad fragmented situation. At this time we need ORM. There are a large number of ORM frameworks that can be used in the PHP world, such as: PHP ORM/Persistence Layer Framework,

What is ORM? Any full-stack framework in any modern language will inevitably have an ORM function, that is, Object-Relational Mapping. To put it bluntly, it is to establish a mapping relationship between the database table and the object. Operations on object members can be synchronized directly with the database. What the user sees is the modification of the members of the object. In fact, the ORM helps the user complete the operation of the database layer. I won’t go into details here. Everyone can choose the ORM framework that suits them according to their own preferences.

Reference:
This article is mainly used to summarize knowledge. The content of this article is only my personal understanding. If there are any mistakes, please correct me. Text or code from other places may be quoted during the writing of the article. If there is any infringement, please contact me in time. I would like to express my gratitude to the author of the article who referred to it during the writing process!

The above is the detailed content of Detailed explanation of the operation of mysql database connection using PHP. For more information, please follow other related articles on the PHP Chinese website!