PHP Database FAQ Summary Page 1/3

If there is only one way to use the database is correct...

There are many ways you can create database design, database access, and database-based PHP business logic code, but it usually ends up in error. This article describes five common problems that arise in the PHP code for database design and accessing databases, and how to fix them when you encounter them.

Question 1: 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.

The following is the quoted content:

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

Listing 1. Access/
Note that the mysql_connect function is used to access the database. Also be aware of the query, where string concatenation is used 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) class. Both provide abstractions from specific database choices. Therefore, your code can run on IBM® DB2®, MySQL, PostgreSQL, or any other database you want to connect to without much tweaking.

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 applications from SQL injection attacks.

The alternative code using PEAR DB is shown below.

The following is the quoted content:

<?php
require_once("");

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 places that use MySQL directly are 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.

Question 2: No automatic increment function is used

Like most modern databases, MySQL is able to create automatic incremental unique identifiers on a per-record basis. Besides that, we will still see code that first runs a SELECT statement to find the largest id, then increments the id by 1, and finds a new record. Listing 3 shows an example bad pattern.

Copy the codeThe 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.

Here the id field is simply specified as an integer. So, although it should be unique, we can add any value as shown in several INSERT statements after the CREATE statement. Listing 4 shows PHP code that adds users to this type of pattern.

Copy the codeThe code is as follows:

<?php 
require_once(""); 

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 executes a query to find the maximum value of id. The file then runs an INSERT statement with the id value plus 1. This code fails in race conditions on a heavily loaded server. In addition, it is also inefficient.

So what is the alternative? Use the auto-increment feature in MySQL to automatically create a unique ID for each insert. The updated mode is shown below.

Copy the codeThe 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.

We have 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 automatically incremented and the PRIMARY KEY flag to indicate that the field is an id. These changes speed up the pace. Listing 6 shows the updated PHP code that inserts users into the table.

Copy the codeThe code is as follows:

<?php 
require_once(""); 

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

123Next pageRead the full text