Common PHP Database Solution Analysis Introduction _php Skill

We encounter a lot of problems when we use PHP to connect to a database, which exposes common database problems that arise 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.

PHP database problem: direct use of MySQL

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

Note Use the Mysql_connect function to access the database. Also note the query, which uses string concatenation 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 abstractions from a specific database selection. So your code can be at IBM without too much tweaking? 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 allows your application to be protected from SQL injection attacks.

Listing 2. access/get_good.php

   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 strings in the $DSN. Besides, we passed? The operator uses $name variables in SQL. The queried data is then sent in through the array at the end of the query () method.

PHP database problem: Do not use the AutoIncrement feature

Like most modern databases, MySQL can create the AutoIncrement unique identifier on a per-record basis. In addition, we'll still see the code that runs a SELECT statement first to find the largest ID, then increases that ID by 1 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 the Users VALUES (1, ' Jack ', ' Pass ');  
INSERT into the Users VALUES (2, ' Joan ', ' Pass ');  
INSERT into the Users VALUES (1, ' Jane ', ' Pass '); 

The ID field here 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 that adds the user to this type of pattern.

Listing 4. add_user.php

The code in add_user.php first executes a query to find the maximum value for the ID. The file then runs an INSERT statement with an ID value plus 1. The code will fail in a race condition on a heavily loaded server. In addition, it is inefficient. So what's the alternative? Use the AutoIncrement feature in MySQL to automatically create a unique ID for each insert.

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

Hope that through the introduction of this article, you can make your PHP database solutions, more understanding.