Five common PHP database problems _ PHP Tutorial

Five 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

<? 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 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 be stored in IBM without too many adjustments? DB2? , MySQL, PostgreSQL, or any other database you want to connect.

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.

Listing 2. Access/get_good.php

<? 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 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.

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 );
Insert into users VALUES (1, jane, pass );

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.

Listing 4. Add_user.php

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

$ Something = $ db-> prepare ("insert into users VALUES (?,?,?) ");
$ Db-> execute ($ TH, 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. 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.

Listing 5. Goodid. php

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

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.

Listing 6. Add_user_good.php

<? Php
Require_once ("DB. php ");

Function add_user ($ name, $ pass)
{
$ Dsn = mysql: // root: password @ localhost/good_genid;
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}

$ Something = $ db-> prepare ("insert into users VALUES (null ,?,?) ");
$ Db-> execute ($ TH, 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 );
?>

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.

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

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.

Listing 8. Getfiles. php

<? Php
Require_once ("DB. php ");

Function get_user ($ name)
{
$ Dsn = mysql: // root: password @ localhost/bad_multi1;
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> 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 );
?>

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.

Listing 9. Getfiles_good.php

<? Php
Require_once ("DB. php ");

Function get_files ($ name)
{
$ Rows = array ();

$ Dsn = mysql: // root: password @ localhost/good_multi;
　

Five common database problems in the PHP app -- including database pattern design, database access and database use business logic code -- and their solutions...