Quickly learn how to use PHP database files

PHP is still quite common, So I studied PHP database files and shared them with you here, hoping to be useful to you. We can see that in an application, each table is in a separate database. It is reasonable to do this in a very large database, but for general applications, this level of separation is not required. In addition, you cannot execute relational queries across databases, which affects 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 1 shows the data that is divided into four files.

Listing 1. PHP database file

 
 

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

You can create database design, database access, and database-based PHP business logic code in many ways, but it generally ends with errors and how to fix these problems. 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. The get_user function connects to the 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 rows associated with the specified 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 2. PHP database file Getfiles. php

 
 

  
  
＜?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 );  
  
  
?＞  
 
 

Listing 3. Getfiles_good.php

 
 

  
  
＜?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 );  
  
  
?＞  
 
 

This code is not only shorter, but also easier to understand and efficient. We do not execute two queries, but execute one query. Although this problem may sound far-fetched, in practice, we usually sum up that all the tables should be in the same database unless there is a very pressing reason.