Summary of common PHP database problems

Expose five common database problems in PHP applications-including database pattern design, database access and use of database business logic code-and their solutions. Expose five common database problems in PHP applications-including database pattern design, database access and use of database business logic code-and their solutions.

If there is only one way to use the database, it is correct ......

You can create database design, database access, and database-based PHP business logic code in many ways, but it generally ends with an error. This article describes the five common problems in the PHP code for database design and database access, and how to fix these problems.

Question 1: use MySQL directly

A common problem is that older PHP code directly uses the mysql _ function to access the database. Listing 1 shows how to directly access the database.

Listing 1. Access/get. php

Note that the mysql_connect function is used to access the database. Pay attention to the query. use string connection to add the $ name parameter to the query.

This technology has two good alternatives: the pear db module and the PHP Data Objects (PDO) class. Both provide abstraction from the choice of a specific database. Therefore, your code can run on IBM DB2, MySQL, PostgreSQL, or any other database you want to connect to without too much adjustment.

Another value of using the pear db module and the PDO abstraction layer is that you can use it in SQL statements? Operator. This makes SQL easier to maintain and protects your applications from SQL injection attacks.

The alternative code for using pear db is as follows.

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

Listing 2. Access/get_good.php

Note that all the locations that directly use MySQL are eliminated, except for the database connection strings in $ dsn. In addition, we pass? The operator uses the $ name variable in SQL. Then, the queried data is sent in through the array at the end of the query () method.

Question 2: Do not use the auto increment function

Like most modern databases, MySQL can create automatic incremental unique identifiers based on each record. In addition, we will still see the code, that is, first run a SELECT statement to find the largest id, then add this id to 1, and find a new record. Listing 3 shows an example of bad mode.

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' ); INSERT INTO users VALUES ( 1, 'jane', 'pass' );

Listing 3. Badid. SQL
Here, the id field is simply specified as an integer. Therefore, although it should be unique, we can add any value, as shown in the INSERT statements following the CREATE statement. Listing 4 shows the PHP code that adds users to this type of mode.

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

Listing 4. Add_user.php

The code in add_user.php first executes a query to find the maximum value of the id. Then, the file adds 1 to the id value to run an INSERT statement. This code fails in the race condition on a server with heavy loads. In addition, it is also inefficient.

So what is the alternative? Use the automatic incremental feature in MySQL to automatically create a unique ID for each insert. The updated mode is as follows.

DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT NOT NULL AUTO_INCREMENT, login TEXT NOT NULL, password TEXT NOT NULL, PRIMARY KEY( id ) ); INSERT INTO users VALUES ( null, 'jack', 'pass' ); INSERT INTO users VALUES ( null, 'joan', 'pass' ); INSERT INTO users VALUES ( null, 'jane', 'pass' );

Listing 5. Goodid. php

The not null flag is added to indicate that the field must NOT be blank. We also added the AUTO_INCREMENT flag to indicate that the field is automatically incremental, and added the primary key flag to indicate that the field is an id. These changes speed up. Listing 6 shows the updated PHP code, which inserts users into the table.

 getMessage()); } $sth = $db->prepare( "INSERT INTO users VALUES(null,?,?)" ); $db->execute( $sth, array( $name, $pass ) ); $res = $db->query( "SELECT last_insert_id()" ); $id = null; while( $res->fetchInto( $row ) ) { $id = $row[0]; } return $id; } $id = add_user( 'jerry', 'pass' ); var_dump( $id ); ?>

Listing 6. Add_user_good.php

Now, instead of getting the largest id value, I directly use the INSERT statement to INSERT data, and then use the SELECT statement to retrieve the id of the last inserted record. This code is much simpler and more efficient than the original version and related modes.

Question 3: using multiple databases

Occasionally, we can see that every table in an application is in a separate database. It is reasonable to do this in a very large database, but for general applications, this level of separation is not required. In addition, you cannot execute relational queries across databases, which affects the overall idea of using relational databases, not to mention the difficulty of managing tables across multiple databases.

So what should multiple databases look like? First, you need some data. Listing 7 shows the data that is divided into four files.

Files. SQL:

CREATE TABLE files ( id MEDIUMINT, user_id MEDIUMINT, name TEXT, path TEXT );

Load_files. SQL:

INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' ); INSERT INTO files VALUES ( 2, 1, 'test2.jpg', 'files/test2.jpg' );

Users. SQL:

DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT );

Load_users. SQL:

INSERT INTO users VALUES ( 1, 'jack', 'pass' ); INSERT INTO users VALUES ( 2, 'jon', 'pass' );

Listing 7. database files
In the multi-database version of these files, you should load the SQL statement into one database and then load the users SQL statement into another database. The PHP code used to query files associated with a specific user in the database is as follows.

 getMessage()); } $res = $db->query( "SELECT id FROM users WHERE login=?", array( $name ) ); $uid = null; while( $res->fetchInto( $row ) ) { $uid = $row[0]; } return $uid; } function get_files( $name ) { $uid = get_user( $name ); $rows = array(); $dsn = 'mysql://root:password@localhost/bad_multi2'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } $res = $db->query( "SELECT * FROM files WHERE user_id=?", array( $uid ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?>

Listing 8. Getfiles. php
The get_user function connects to the database containing the user table and retrieves the ID of the given user. The get_files function connects to the file table and retrieves the file rows associated with the specified user.

A better way to do all of these things is to load the data into a database and then execute the query, such as the following query.

 getMessage()); } $res = $db->query( "SELECT files.* FROM users, files WHERE users.login=? AND users.id=files.user_id", array( $name ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?>

Listing 9. Getfiles_good.php

This code is not only shorter, but also easier to understand and efficient. We do not execute two queries, but execute one query.
Although this problem may sound far-fetched, in practice, we usually sum up that all the tables should be in the same database unless there is a very pressing reason.
Question 4: Do not use the link
Different from programming languages, relational databases do not have the array type. Instead, they use the relationship between tables to create one or more structures between objects, which has the same effect as arrays. One problem I see in the application is that engineers try to use the database as a programming language by using text strings with comma-separated identifiers to create arrays. See the following mode.

DROP TABLE IF EXISTS files; CREATE TABLE files ( id MEDIUMINT, name TEXT, path TEXT ); DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT, files TEXT ); INSERT INTO files VALUES ( 1, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO files VALUES ( 2, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO users VALUES ( 1, 'jack', 'pass', '1,2' );

Listing 10. Bad. SQL
A user in the system can have multiple files. In programming languages, arrays should be used to represent files associated with a user. In this example, the programmer selects to create a file field, which contains a list of file IDs separated by commas. To get a list of all files of a specific user, the programmer must first read rows from the user table, parse the file text, and run a separate SELECT statement for each file. The code is as follows.

 getMessage()); } $res = $db->query( "SELECT files FROM users WHERE login=?", array( $name ) ); $files = null; while( $res->fetchInto( $row ) ) { $files = $row[0]; } $rows = array(); foreach( split( ',',$files ) as $file ) { $res = $db->query( "SELECT * FROM files WHERE id=?", array( $file ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?>

Listing 11. Get. php
This technology is slow, difficult to maintain, and does not make good use of the database. The only solution is to rebuild the schema and convert it back to the traditional relational form, as shown below.

DROP TABLE IF EXISTS files; CREATE TABLE files ( id MEDIUMINT, user_id MEDIUMINT, name TEXT, path TEXT ); DROP TABLE IF EXISTS users; CREATE TABLE users ( id MEDIUMINT, login TEXT, password TEXT ); INSERT INTO users VALUES ( 1, 'jack', 'pass' ); INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'media/test1.jpg' ); INSERT INTO files VALUES ( 2, 1, 'test1.jpg', 'media/test1.jpg' );

Listing 12. Good. SQL
Here, each file is related to users in the file table through the user_id function. This may be the opposite of thinking of anyone who views multiple files as arrays. Of course, arrays do not reference the objects they contain-in fact, and vice versa. However, in relational databases, this is how it works, and queries are much faster and simpler. Listing 13 shows the corresponding PHP code.

 getMessage()); } $rows = array(); $res = $db->query( "SELECT files.* FROM users,files WHERE users.login=? AND users.id=files.user_id", array( $name ) ); while( $res->fetchInto( $row ) ) { $rows[] = $row; } return $rows; } $files = get_files( 'jack' ); var_dump( $files ); ?>

Listing 13. Get_good.php
Here, we perform a query on the database to obtain all rows. The code is not complex, and it uses the database as its original use.
Question 5: n + 1 mode
I do not know how many times I have seen such a large application. the code first retrieves some entities (such as customers) and then retrieves them one by one, obtain detailed information about each object. We call it the n + 1 mode, because the query needs to be executed so many times-one query retrieves the list of all entities, and then executes a query for each of the n entities. This is not a problem when n = 10, but when n = 100 or n = 1000? Then there will certainly be inefficiency. Listing 14 shows an example of this mode.

DROP TABLE IF EXISTS authors; CREATE TABLE authors ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name TEXT NOT NULL, PRIMARY KEY ( id ) ); DROP TABLE IF EXISTS books; CREATE TABLE books ( id MEDIUMINT NOT NULL AUTO_INCREMENT, author_id MEDIUMINT NOT NULL, name TEXT NOT NULL, PRIMARY KEY ( id ) ); INSERT INTO authors VALUES ( null, 'Jack Herrington' ); INSERT INTO authors VALUES ( null, 'Dave Thomas' ); INSERT INTO books VALUES ( null, 1, 'Code Generation in Action' ); INSERT INTO books VALUES ( null, 1, 'Podcasting Hacks' ); INSERT INTO books VALUES ( null, 1, 'PHP Hacks' ); INSERT INTO books VALUES ( null, 2, 'Pragmatic Programmer' ); INSERT INTO books VALUES ( null, 2, 'Ruby on Rails' ); INSERT INTO books VALUES ( null, 2, 'Programming Ruby' );

Listing 14. Schema. SQL
This mode is reliable with no errors. The problem lies in accessing the database to find the code for all the books of a given author, as shown below.

 getMessage()); } function get_author_id( $name ) { global $db; $res = $db->query( "SELECT id FROM authors WHERE name=?", array( $name ) ); $id = null; while( $res->fetchInto( $row ) ) { $id = $row[0]; } return $id; } function get_books( $id ) { global $db; $res = $db->query( "SELECT id FROM books WHERE author_id=?", array( $id ) ); $ids = array(); while( $res->fetchInto( $row ) ) { $ids []= $row[0]; } return $ids; } function get_book( $id ) { global $db; $res = $db->query( "SELECT * FROM books WHERE id=?", array( $id ) ); while( $res->fetchInto( $row ) ) { return $row; } return null; } $author_id = get_author_id( 'Jack Herrington' ); $books = get_books( $author_id ); foreach( $books as $book_id ) { $book = get_book( $book_id ); var_dump( $book ); } ?>

Listing 15. Get. php
If you look at the following code, you may think, "Hey, this is really clear ." First, obtain the author id, the list of books, and information about each book. Indeed, it is clear, but is it efficient? The answer is No. See how many queries are executed to retrieve Jack Herrington's books. Get the id at a time, get the book list at another time, and then execute a query for each book. Five queries are required for three books!
The solution is to use a function to execute a large number of queries, as shown below.

 getMessage()); } function get_books( $name ) { global $db; $res = $db->query( "SELECT books.* FROM authors,books WHERE books.author_id=authors.id AND authors.name=?", array( $name ) ); $rows = array(); while( $res->fetchInto( $row ) ) { $rows []= $row; } return $rows; } $books = get_books( 'Jack Herrington' ); var_dump( $books ); ?>

Listing 16. Get_good.php
Now you need a quick and single query to retrieve the list. This means that I will probably have several methods of these types with different parameters, but there is no choice. If you want to have an extended PHP application, you must use the database effectively, which means more intelligent queries.

The problem in this example is that it is a bit clear. Generally, the n + 1 or n * n issues of these types are much more subtle. They only occur when the database administrator runs the query analyzer on the system when the system has performance problems.

Conclusion

Databases are powerful tools, just like all powerful tools. they will be abused if you do not know how to use them correctly. The trick to identify and solve these problems is to better understand the underlying technology. For a long time, I have heard complaints from business logic writers who do not want to understand database or SQL code. They use databases as objects and wonder why performance is so bad.

They don't realize how important it is to understand SQL to transform a database from a difficult necessity to a powerful consortium. If you use a database every day but are not familiar with SQL, read The Art of SQL, which is well written and practical. it can guide you to understand The database.