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 describes the five common problems that arise in the database design and the PHP code that accesses the database, and how to fix them when they are encountered.
question 1: Use 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.
The following are the referenced contents: <?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 '));
?> |
Listing 1. access/get.php
Note that the Mysql_connect function is used 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. As a result, your code can run on ibm®db2®, MySQL, PostgreSQL, or any other database you want to connect to without too much tweaking. Another value of
using the PEAR DB module and the PDO abstraction layer is that you can use the operator in an SQL statement. Doing so makes SQL easier to maintain and allows your application to be protected from SQL injection attacks. The alternative code for the
using PEAR DB is shown below.
The following are the referenced contents: <?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 '));
?> |
Listing 2. access/get_good.php
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.
Issue 2: 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.
Copy Code code as follows:
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 ');
Listing 3. Badid.sql
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.
Copy Code code as follows:
<?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);
?>
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. The updated pattern is shown below.
Copy Code code 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 the users VALUES (null, ' Jack ', ' Pass ');
INSERT into the users VALUES (null, ' Joan ', ' Pass ');
INSERT into the users VALUES (null, ' Jane ', ' Pass ');
Listing 5. goodid.php
We added the 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 inserts the user into the table.
Copy Code code as follows:
<?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);
?>
Listing 6. add_user_good.php
Current 1/3 page
123 Next read the full text
