Five common PHP database problems (1)_PHP tutorial

If only one way to use a database is correct, there are many ways you can create database design, database access and database-based PHP business logic code, but 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.

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-c"><li class="alt"><span><span><?php  </span></span></li><li><span class="keyword">function</span><span> get_user_id( </span><span class="vars">$name</span><span> )  </span></li><li class="alt"><span>{  </span></li><li><span>  </span><span class="vars">$db</span><span> = 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> </span></li><li class="alt"><span>  </span><span class="vars">$res</span><span> = mysql_query( </span><span class="string">"SELECT id FROM users WHERE login='"</span><span>.</span><span class="vars">$name</span><span>.</span><span class="string">"'"</span><span> );  </span></li><li><span>  </span><span class="keyword">while</span><span>( </span><span class="vars">$row</span><span> = mysql_fetch_array( </span><span class="vars">$res</span><span> ) ) { </span><span class="vars">$id</span><span> = </span><span class="vars">$row</span><span>[0]; }  </span></li><li class="alt"><span> </span></li><li><span>  </span><span class="keyword">return</span><span> </span><span class="vars">$id</span><span>;  </span></li><li class="alt"><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></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) class. Both provide abstractions from specific database selections. As a result, 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.

Alternative code using PEAR DB is shown below.

Listing 2. Access/get_good.php

<ol class="dp-c">
<span><span> </span></span><li><span><?php  </span></li><li class="alt"><span class="keyword">require_once</span><span>(</span><span class="string">"DB.php"</span><span>);  </span></li><li><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>  </span><span class="vars">$dsn</span><span> = </span><span class="string">'mysql://root:password@localhost/users'</span><span>;  </span></li><li><span>  </span><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>  </span><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> </span></li>
<li class="alt">
<span>  </span><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>
</li>
<li>
<span>  </span><span class="keyword">array</span><span>( </span><span class="vars">$name</span><span> ) );  </span>
</li>
<li class="alt">
<span>  </span><span class="vars">$id</span><span> = null;  </span>
</li>
<li>
<span>  </span><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> </span></li>
<li>
<span>  </span><span class="keyword">return</span><span> </span><span class="vars">$id</span><span>;  </span>
</li>
<li class="alt"><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 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. Then, the query data is sent in through the array at the end of the query() method.

1

http://www.bkjia.com/PHPjc/445827.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/445827.htmlTechArticle If only one way of using a database is correct, you can create database design, database access and database-based PHP business logic code in many ways, but in the end...