Analysis and introduction to common PHP database solutions_php skills

We will encounter many problems when using PHP to connect to the database. This article reveals common database problems that occur in PHP applications - including database schema design, database access and business logic code that uses the database - and their solution. If only one way to use the database was correct.

PHP database problem: use MySQL directly

A common problem is that older PHP code uses the mysql_ function directly to access the database. Listing 1 shows how to access the database directly.

List 1. Access/get.php

＜?php  
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' ) );  
?＞ 

Note that the mysql_connect function is used to access the database. Also note the query, which uses string concatenation to add the $name parameter to the query. There are two good alternatives to this technology: the PEAR DB module and the PHP Data Objects (PDO) classes. Both provide abstractions from specific database selections. So your code can run without much tweaking on IBM® DB2®, MySQL, PostgreSQL, or any other database you want to connect to. Another value of using the PEAR DB module and the PDO abstraction layer is that you can use the ? operator in SQL statements. Doing so makes SQL easier to maintain and protects your application from SQL injection attacks.

List 2. Access/get_good.php

＜?php  
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' ) );  
?＞ 

Note that all direct use of MySQL has been eliminated, except for the database connection string in $dsn. Additionally, we use the $name variable in SQL via the ? operator. The query data is then sent in via the array at the end of the query() method.

PHP database problem: the auto-increment function is not used

Like most modern databases, MySQL has the ability to create auto-incrementing 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.

List 3. Badid.sql

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' ); 

Here the id field 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.

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.

＜?php  
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 );  
?＞ 

I hope that through the introduction of this article, you can have a better understanding of PHP database solutions.