Analysis and introduction to 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 database use business logic code - and their solutions. If only one way to use the database was correct.

You can create PHP database design, database access and database-based PHP business logic code in many ways, 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.

List 1. Access/get.php

<ol class="dp-j">
<li class="alt"><span><span>＜?php   </span></span></li>
<li><span>function get_user_id( $name )   </span></li>
<li class="alt"><span>{   </span></li>
<li>
<span>$db = mysql_connect( </span><span class="string">'localhost'</span><span>, </span><span class="string">'root'</span><span>, </span><span class="string">'password'</span><span> );   </span>
</li>
<li class="alt">
<span>mysql_select_db( </span><span class="string">'users'</span><span> );   </span>
</li>
<li>
<span>$res = mysql_query( </span><span class="string">"SELECT id FROM users WHERE login='"</span><span>.$name.</span><span class="string">"'"</span><span> );   </span>
</li>
<li class="alt">
<span class="keyword">while</span><span>( $row = mysql_fetch_array( $res ) ) { $id = $row[</span><span class="number">0</span><span>]; }   </span>
</li>
<li>
<span class="keyword">return</span><span> $id;   </span>
</li>
<li class="alt"><span>}   </span></li>
<li>
<span>var_dump( get_user_id( </span><span class="string">'jack'</span><span> ) );   </span>
</li>
<li class="alt"><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-c">
<li class="alt"><span><span>＜?php   </span></span></li>
<li>
<span class="keyword">require_once</span><span>(</span><span class="string">"DB.php"</span><span>);   </span>
</li>
<li class="alt">
<span class="keyword">function</span><span> get_user_id( </span><span class="vars">$name</span><span> )   </span>
</li>
<li><span>{   </span></li>
<li class="alt">
<span class="vars">$dsn</span><span> = </span><span class="string">'mysql://root:password@localhost/users'</span><span>;   </span>
</li>
<li>
<span class="vars">$db</span><span> =& DB::Connect( </span><span class="vars">$dsn</span><span>, </span><span class="keyword">array</span><span>() );   </span>
</li>
<li class="alt">
<span class="keyword">if</span><span> (PEAR::isError(</span><span class="vars">$db</span><span>)) { </span><span class="keyword">die</span><span>(</span><span class="vars">$db</span><span>-＞getMessage()); }   </span>
</li>
<li>
<span class="vars">$res</span><span> = </span><span class="vars">$db</span><span>-＞query( </span><span class="string">'SELECT id FROM users WHERE login=?'</span><span>,</span><span class="keyword">array</span><span>( </span><span class="vars">$name</span><span> ) );   </span>
</li>
<li class="alt">
<span class="vars">$id</span><span> = null;   </span>
</li>
<li>
<span class="keyword">while</span><span>( </span><span class="vars">$res</span><span>-＞fetchInto( </span><span class="vars">$row</span><span> ) ) { </span><span class="vars">$id</span><span> = </span><span class="vars">$row</span><span>[0]; }   </span>
</li>
<li class="alt">
<span class="keyword">return</span><span> </span><span class="vars">$id</span><span>;   </span>
</li>
<li><span>}   </span></li>
<li class="alt">
<span>var_dump( get_user_id( </span><span class="string">'jack'</span><span> ) );   </span>
</li>
<li><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. Then, the query data is sent in through 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-c">
<li class="alt"><span><span>DROP TABLE IF EXISTS users;   </span></span></li>
<li><span>CREATE TABLE users (   </span></li>
<li class="alt"><span>id MEDIUMINT,   </span></li>
<li><span>login TEXT,   </span></li>
<li class="alt"><span>password TEXT   </span></li>
<li><span>);   </span></li>
<li class="alt">
<span>INSERT INTO users VALUES ( 1, </span><span class="string">'jack'</span><span>, </span><span class="string">'pass'</span><span> );   </span>
</li>
<li>
<span>INSERT INTO users VALUES ( 2, </span><span class="string">'joan'</span><span>, </span><span class="string">'pass'</span><span> );   </span>
</li>
<li class="alt">
<span>INSERT INTO users VALUES ( 1, </span><span class="string">'jane'</span><span>, </span><span class="string">'pass'</span><span> ); </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

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.

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

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

http://www.bkjia.com/PHPjc/445745.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/445745.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 include database schema design, database access...