Five common PHP database problems (2)_PHP tutorial

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

Listing 9. Getfiles_good.php

require_once("DB.php");

function get_files( $name )
{
​$rows = array();

​$dsn = 'mysql://root:password@localhost/good_multi';
​$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->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 );
?>

Not only is the code shorter, it's also easier to understand and more efficient. Instead of executing two queries, we execute one query.

Although this question may sound far-fetched, in practice we usually conclude that all tables should be in the same database unless there are very compelling reasons.

Question 4: Not using relationships

Relational databases are different from programming languages ​​in that they do not have array types. Instead, they use relationships between tables to create a one-to-many structure between objects, which has the same effect as an array. One problem I've seen in applications is where engineers try to use the database like a programming language, by creating arrays using text strings with comma-separated identifiers. See the pattern below.

Listing 10. Bad.sql

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

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 chooses to create a files field that contains a comma-separated list of file ids. To get a list of all files for a particular user, the programmer must first read the rows from the users table, then parse the text of the files and run a separate SELECT statement for each file. The code is shown below.

Listing 11. Get.php

require_once("DB.php");

http://www.bkjia.com/PHPjc/632449.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/632449.htmlTechArticleA better way to do all these things is to load the data into a database and then execute the query, like below query. Listing 9. Getfiles_good.php ?php require_once(DB.p...