PHP Database FAQ Summary Page 1/3_PHP Tutorial-PHP Tutorial-php.cn

PHP Database FAQ Summary Page 1/3_PHP Tutorial

WBOY

Release： 2016-07-21 15:45:18

Original

1085 people have browsed it

如果只有一种方式使用数据库是正确的……

您可以用很多的方式创建数据库设计、数据库访问和基于数据库的 PHP 业务逻辑代码，但最终一般以错误告终。本文说明了数据库设计和访问数据库的 PHP 代码中出现的五个常见问题，以及在遇到这些问题时如何修复它们。

问题 1：直接使用 MySQL

一个常见问题是较老的 PHP 代码直接使用 mysql_ 函数来访问数据库。清单 1 展示了如何直接访问数据库。

以下为引用的内容：

function get_user_id( $name )
{
$db = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'users' );

$res = mysql_query( "SELECT id FROM users WHERE login='".$name."'" );
while( $row = mysql_fetch_array( $res ) ) { $id = $row[0]; }

return $id;
}

var_dump( get_user_id( 'jack' ) );
?>

清单 1. Access/get.php
注意使用了 mysql_connect 函数来访问数据库。还要注意查询，其中使用字符串连接来向查询添加 $name 参数。

该技术有两个很好的替代方案：PEAR DB 模块和 PHP Data Objects (PDO) 类。两者都从特定数据库选择提供抽象。因此，您的代码无需太多调整就可以在 IBM® DB2®、MySQL、PostgreSQL 或者您想要连接到的任何其他数据库上运行。

使用 PEAR DB 模块和 PDO 抽象层的另一个价值在于您可以在 SQL 语句中使用 ? 操作符。这样做可使 SQL 更加易于维护，且可使您的应用程序免受 SQL 注入攻击。

使用 PEAR DB 的替代代码如下所示。

以下为引用的内容：

require_once("DB.php");

function get_user_id( $name )
{
$dsn = 'mysql://root:password@localhost/users';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

$res = $db->query( 'SELECT id FROM users WHERE login=?',
array( $name ) );
$id = null;
while( $res->fetchInto( $row ) ) { $id = $row[0]; }

return $id;
}

var_dump( get_user_id( 'jack' ) );
?>

Listing 2. Access/get_good.php
Note that all direct references to MySQL have been eliminated, except for the database connection string in $dsn. Additionally, we use the $name variable in SQL via the ? operator. Then, the query data is sent in through the array at the end of the query() method.

Problem 2: Not using the auto-increment feature

Like most modern databases, MySQL has the ability to create auto-increment unique identifiers on a per-record basis. Beyond that, we'll still see code that first runs a SELECT statement to find the largest id, then increments that id by 1, and finds a new record. Listing 3 shows an example bad pattern.

Copy code The code is as follows:

DROP TABLE IF EXISTS users; 
CREATE TABLE users ( 
id MEDIUMINT , 
login TEXT, 
password TEXT 
); 
INSERT INTO users VALUES ( 1, 'jack', 'pass' ); 
INSERT INTO users VALUES ( 2, 'joan', 'pass' ); 
INSERT INTO users VALUES ( 1, 'jane', 'pass' ); 

Listing 3. Badid.sql

The id field here is Simply specified as an integer. So, even though it should be unique, we can add any value as shown in several INSERT statements following the CREATE statement. Listing 4 shows the PHP code to add a user to this type of pattern.

Copy code The code is as follows:

require_once("DB.php"); 

function add_user( $name, $pass ) 
{ 
$rows = array(); 

$dsn = 'mysql://root:password@localhost/bad_badid'; 
$db =& DB::Connect( $dsn, array() ); 
if (PEAR::isError($db)) { die($db->getMessage()); } 

$res = $db->query( "SELECT max(id) FROM users" ); 
$id = null; 
while( $res->fetchInto( $row ) ) { $id = $row[0]; } 

$id += 1; 

$sth = $db->prepare( "INSERT INTO users VALUES(?,?,?) " ); 
$db->execute( $sth, array( $id, $name, $pass ) ); 

return $id; 
} 

$ id = add_user( 'jerry', 'pass' ); 

var_dump( $id ); 
?> 

Listing 4. Add_user.php

The code in add_user.php first performs a query to find the maximum value of id. The file then runs an INSERT statement with the id value increased by 1. This code will fail in a race condition on a heavily loaded server. Plus, it's also inefficient.

So what's the alternative? Use the auto-increment feature in MySQL to automatically create a unique ID for each insert. The updated schema looks like this.

Copy code The code is as follows:

DROP TABLE IF EXISTS users; 
CREATE TABLE users ( 
id MEDIUMINT NOT NULL AUTO_INCREMENT, 
login TEXT NOT NULL, 
password TEXT NOT NULL, 
PRIMARY KEY( id ) 
); 

INSERT INTO users VALUES ( null, 'jack', 'pass' ); 
INSERT INTO users VALUES ( null, 'joan', 'pass' ); 
INSERT INTO users VALUES ( null, 'jane', 'pass' ); 

Listing 5. Goodid.php

We added the NOT NULL flag to indicate that the field must not be empty. We also added the AUTO_INCREMENT flag to indicate that the field is auto-incrementing, and the PRIMARY KEY flag to indicate that the field is an id. These changes speed things up. Listing 6 shows the updated PHP code that inserts the user into the table.

Copy code The code is as follows:

require_once("DB.php"); 

function add_user( $name, $pass ) 
{ 
$dsn = 'mysql://root:password@localhost/good_genid'; 
$db =& DB::Connect( $ dsn, array() ); 
if (PEAR::isError($db)) { die($db->getMessage()); } 

$sth = $db->prepare ( "INSERT INTO users VALUES(null,?,?)" ); 
$db->execute( $sth, array( $name, $pass ) ); 

$res = $db ->query( "SELECT last_insert_id()" ); 
$id = null; 
while( $res->fetchInto( $row ) ) { $id = $row[0]; } 

return $id; 
} 

$id = add_user( 'jerry', 'pass' ); 

var_dump( $id ); 
?> 

Listing 6. Add_user_good.php