PHP Database FAQ Summary 1th 3 page _php Tutorial

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.

The following is the referenced content: 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 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. As a result, your code doesn't need to be too much tuned to run on ibm®db2®, MySQL, PostgreSQL, or any other database you want to connect to. Another value of the

using the PEAR DB module and the PDO abstraction layer is that you can use the operator in the SQL statement. Doing so makes SQL easier to maintain and can protect your application from SQL injection attacks. The replacement code for

using PEAR DB is shown below.

The following is the referenced content: 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 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.
Copy CodeThe code is as follows:
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

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.
Copy CodeThe code is as follows:
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 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's the alternative? Use the auto-increment feature in MySQL to automatically create a unique ID for each insert. The updated pattern is shown below.
Copy CodeThe code 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

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.
Copy CodeThe code is as follows:
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

http://www.bkjia.com/PHPjc/320364.html www.bkjia.com true http://www.bkjia.com/PHPjc/320364.html techarticle 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 eventually ...

