Common PHP Database Solutions _php Tutorials

We encounter a lot of problems when using PHP to connect to a database, and this article exposes common database problems 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 is correct. You can create PHP database design, database access, and database-based PHP business logic code in a number of ways, but in the end it usually ends in error. This article explains the five common problems that occur in the database design and the PHP code that accesses the database, and how to fix them when they encounter these problems.

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

 
 

  
  

  
 
  
  

  
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 The Mysql_connect function is used to access the database. Also note the query, which uses a string connection to add $name parameters to the query. There are two good alternatives to this technique: the PEAR DB module and the PHP Data Objects (PDO) class. Both provide abstraction from a specific database selection. So, your code doesn't need to be too much tuned for IBM? Run on 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 it in SQL statements. Operator. Doing so makes SQL easier to maintain and can protect your application from SQL injection attacks.

Listing 2. access/get_good.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 the places that are used directly to MySQL are eliminated, except for the database connection string in the $dsn. In addition, we pass? operator to use $name variable in SQL. The queried data is then sent in through an array at the end of the query () method.

PHP Database Issue 2: Do not use the auto-increment feature

Like most modern databases, MySQL can create an AutoIncrement unique identifier on a per-record basis. In addition, we will still see the code that runs a SELECT statement first to find the maximum ID, then adds 1 to the ID and finds a new record. Listing 3 shows an example of a bad pattern.

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

 
  
  

The ID field here is simply specified as an integer. So, although 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 that adds the user to this type of pattern.

Listing 4. add_user.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);
  
  

  
? >
 
 

The code in add_user.php first executes a query to find the maximum value of the ID. The file then runs an INSERT statement with the ID value plus 1. The code will fail in a race condition on a heavily loaded server. In addition, it is 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.html www.bkjia.com true http://www.bkjia.com/PHPjc/446456.html techarticle we encounter a lot of problems when using PHP to connect to a database, and this article exposes common database problems in PHP applications-including database schema design, database visits ...

