Five common PHP database problems _php Tutorial

Uncover the five common database problems that occur 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 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.

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

The alternative code that uses PEAR DB is shown below.

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.

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);
INSERT into Users VALUES (1, Jane, 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. The updated pattern is shown below.

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

We have added a NOT NULL flag to indicate that the field must not be empty. We also added the AUTO_INCREMENT flag to indicate that the field is AutoIncrement, adding the PRIMARY KEY flag to indicate that the field is an ID. These changes speed up. Listing 6 shows the updated PHP code, which will insert the user into the table.

Listing 6. add_user_good.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 ());}

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

Now that I'm not getting the maximum ID value, I'm using the INSERT statement to insert the data, and then use the SELECT statement to retrieve the ID of the last inserted record. The code is much simpler and more efficient than the original version and its associated schema.

Issue 3: Using multiple databases

Occasionally, we see an application in which each table is in a separate database. It is reasonable to do so in a very large database, but for a generic application, this level of fragmentation is not required. In addition, relational queries cannot be executed across databases, which can affect 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 such data divided into 4 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 statements into one database, and then load the users SQL statements into another database. The PHP code used to query the database for files associated with a particular user is shown below.

Listing 8. getfiles.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 that contains the user table and retrieves the ID of the given user. The Get_files function connects to a file table and retrieves the file rows associated with a given 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

Require_once ("db.php");

function Get_files ($name)
{
$rows = Array ();

$DSN = Mysql://root:password@localhost/good_multi;
　

http://www.bkjia.com/PHPjc/508372.html www.bkjia.com true http://www.bkjia.com/PHPjc/508372.html techarticle uncover the five common database problems that occur in PHP applications-including database schema design, database access, and business logic code that uses the database-and their resolution ...

