Common PHP database solutions_PHP tutorial

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 solutions. If only one way to use the database was correct. There are many ways you can create PHP database design, database access, and database-based PHP business logic code, but they usually end up with errors. This article explains five common problems that arise in database design and PHP code that access the database, and how to fix them when you encounter them.

PHP Database Problem 1: Using 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.

Listing 1. Access/get.php

<ol class="dp-xml">
<li class="alt"><span><span>＜?php  </span></span></li>
<li class=""><span>function get_user_id( $name )  </span></li>
<li class="alt"><span>{  </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_connect</font></span><span>( 'localhost', 'root', 'password' );  </span>
</li>
<li class="alt"><span>mysql_select_db( 'users' );  </span></li>
<li class=""><span> </span></li>
<li class="alt">
<span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_query</font></span><span>( "SELECT id FROM users WHERE </span><span class="attribute"><font color="#ff0000">login</font></span><span>=</span><span class="attribute-value"><font color="#0000ff">'".$name."'</font></span><span>" );  </span>
</li>
<li class="">
<span>while( $</span><span class="attribute"><font color="#ff0000">row</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">mysql_fetch_array</font></span><span>( $res ) ) { $</span><span class="attribute"><font color="#ff0000">id</font></span><span> = $row[0]; }  </span>
</li>
<li class="alt"><span> </span></li>
<li class=""><span>return $id;  </span></li>
<li class="alt"><span>}  </span></li>
<li class=""><span> </span></li>
<li class="alt"><span>var_dump( get_user_id( 'jack' ) );  </span></li>
<li class=""><span>?＞  </span></li>
</ol>

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.

Listing 2. Access/get_good.php

<ol class="dp-xml">
<li class="alt"><span><span>＜?php  </span></span></li>
<li class=""><span>require_once("DB.php");  </span></li>
<li class="alt"><span>function get_user_id( $name )  </span></li>
<li class=""><span>{  </span></li>
<li class="alt">
<span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/users'</font></span><span>;  </span>
</li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() );  </span>
</li>
<li class="alt"><span>if (PEAR::isError($db)) { die($db-＞getMessage()); }  </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db-＞query( 'SELECT id FROM users WHERE </span><span class="attribute"><font color="#ff0000">login</font></span><span>=?',array( $name ) );  </span>
</li>
<li class="alt">
<span>$</span><span class="attribute"><font color="#ff0000">id</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">null</font></span><span>;  </span>
</li>
<li class="">
<span>while( $res-＞fetchInto( $row ) ) { $</span><span class="attribute"><font color="#ff0000">id</font></span><span> = $row[0]; }  </span>
</li>
<li class="alt"><span>return $id;  </span></li>
<li class=""><span>}  </span></li>
<li class="alt"><span>var_dump( get_user_id( 'jack' ) );  </span></li>
<li class=""><span>?＞ </span></li>
</ol>

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. The query data is then sent in via the array at the end of the query() method.

PHP database 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 symbol. 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.

Listing 3. Badid.sql

<ol class="dp-xml">
<li class="alt"><span><span>DROP TABLE IF EXISTS users;  </span></span></li>
<li class=""><span>CREATE TABLE users (  </span></li>
<li class="alt"><span>id MEDIUMINT,  </span></li>
<li class=""><span>login TEXT,  </span></li>
<li class="alt"><span>password TEXT  </span></li>
<li class=""><span>);  </span></li>
<li class="alt"><span>INSERT INTO users VALUES ( 1, 'jack', 'pass' );  </span></li>
<li class=""><span>INSERT INTO users VALUES ( 2, 'joan', 'pass' );  </span></li>
<li class="alt"><span>INSERT INTO users VALUES ( 1, 'jane', 'pass' ); </span></li>
</ol>

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

<ol class="dp-xml">
<li class="alt"><span><span>＜?php  </span></span></li>
<li class=""><span>require_once("DB.php");  </span></li>
<li class="alt"><span> </span></li>
<li class=""><span>function add_user( $name, $pass )  </span></li>
<li class="alt"><span>{  </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">rows</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">array</font></span><span>();  </span>
</li>
<li class="alt"><span> </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/bad_badid'</font></span><span>;  </span>
</li>
<li class="alt">
<span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() );  </span>
</li>
<li class=""><span>if (PEAR::isError($db)) { die($db-＞getMessage()); }  </span></li>
<li class="alt"><span> </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db-＞query( "SELECT max(id) FROM users" );  </span>
</li>
<li class="alt">
<span>$</span><span class="attribute"><font color="#ff0000">id</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">null</font></span><span>;  </span>
</li>
<li class="">
<span>while( $res-＞fetchInto( $row ) ) { $</span><span class="attribute"><font color="#ff0000">id</font></span><span> = $row[0]; }  </span>
</li>
<li class="alt"><span> </span></li>
<li class=""><span>$id += 1;  </span></li>
<li class="alt"><span> </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">sth</font></span><span> = $db-＞prepare( "INSERT INTO users VALUES(?,?,?)" );  </span>
</li>
<li class="alt"><span>$db-＞execute( $sth, array( $id, $name, $pass ) );  </span></li>
<li class=""><span> </span></li>
<li class="alt"><span>return $id;  </span></li>
<li class=""><span>}  </span></li>
<li class="alt"><span> </span></li>
<li class="">
<span>$</span><span class="attribute"><font color="#ff0000">id</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">add_user</font></span><span>( 'jerry', 'pass' );  </span>
</li>
<li class="alt"><span> </span></li>
<li class=""><span>var_dump( $id );  </span></li>
<li class="alt"><span>?＞  </span></li>
</ol>

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 is the alternative? Use the auto-increment feature in MySQL to automatically create a unique ID for each insert.

http://www.bkjia.com/PHPjc/446456.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/446456.htmlTechArticleWe will encounter many problems when using PHP to connect to the database. This article reveals common problems that occur in PHP applications. Database issues - including database schema design, database access...