Now instead of getting the maximum id value, I directly use the INSERT statement to insert the 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 its associated patterns.
Question 3: Using multiple databases
Occasionally, we see an application in which each table is in a separate database. It is reasonable to do this in very large databases, but for general applications, this level of splitting is not required. Furthermore, relationship queries cannot be performed across databases, which can affect the overall idea of using relational databases, not to mention that managing tables across multiple databases will be more difficult.
So, what should multiple databases look like? First, you need some data. Listing 7 shows such data divided into 4 files.
:
Copy the codeThe code is as follows:
CREATE TABLE files (
id MEDIUMINT,
user_id MEDIUMINT,
name TEXT,
path TEXT
);
Load_files.sql:
Copy the codeThe code is as follows:
INSERT INTO files VALUES ( 1, 1, '', 'files/' );
INSERT INTO files VALUES ( 2, 1, '', 'files/' );
:
Copy the codeThe code is as follows:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id MEDIUMINT,
login TEXT,
password TEXT
);
Load_users.sql:
Copy the codeThe code is as follows:
INSERT INTO users VALUES ( 1, 'jack', 'pass' );
INSERT INTO users VALUES ( 2, 'jon', 'pass' );
Listing 7. Database Files
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.
Copy the codeThe code is as follows:
<?php
require_once("");
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 );
?>
Listing 8.
The get_user function connects to a 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 lines associated with the given user.
A better way to do all of these things is to load the data into a database and then execute a query, like the one below.
Copy the codeThe code is as follows:
<?php
require_once("");
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
=? AND =files.user_id",
array( $name ) );
while( $res->fetchInto( $row ) ) { $rows[] = $row; }
return $rows;
}
$files = get_files( 'jack' );
var_dump( $files );
?>
Listing 9. Getfiles_good.php
Previous page123Next pageRead the full text