PHP is still relatively commonly used, so I studied the PHP database file and shared it with you here. I hope it will be useful to everyone. We will see an application where each table is in a separate database. This is reasonable in very large databases, but for general applications this level of partitioning is not required. Additionally, relational queries cannot be performed across databases, which takes away from the whole idea of using a relational database, not to mention that it would be more difficult to manage tables across multiple databases. So, what should multiple databases look like? First, you need some data. Listing 1 shows such data split into 4 files.
Listing 1. PHP database files
<ol class="dp-xml"> <li class="alt"><span><span>Files.sql: </span></span></li> <li class=""><span>CREATE TABLE files ( </span></li> <li class="alt"><span>id MEDIUMINT, </span></li> <li class=""><span>user_id MEDIUMINT, </span></li> <li class="alt"><span>name TEXT, </span></li> <li class=""><span>path TEXT </span></li> <li class="alt"><span>); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Load_files.sql: </span></li> <li class=""><span>INSERT INTO files VALUES ( 1, 1, 'test1.jpg', 'files/test1.jpg' ); </span></li> <li class="alt"><span>INSERT INTO files VALUES ( 2, 1, 'test2.jpg', 'files/test2.jpg' ); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Users.sql: </span></li> <li class=""><span>DROP TABLE IF EXISTS users; </span></li> <li class="alt"><span>CREATE TABLE users ( </span></li> <li class=""><span>id MEDIUMINT, </span></li> <li class="alt"><span>login TEXT, </span></li> <li class=""><span>password TEXT </span></li> <li class="alt"><span>); </span></li> <li class=""><span> </span></li> <li class="alt"><span>Load_users.sql: </span></li> <li class=""><span>INSERT INTO users VALUES ( 1, 'jack', 'pass' ); </span></li> <li class="alt"><span>INSERT INTO users VALUES ( 2, 'jon', 'pass' ); </span></li> </ol>
Listing 2. PHP database file Getfiles.php
<ol class="dp-xml"> <li class="alt"><span><span><?php </span></span></li> <li class=""><span>require_once("DB.php"); </span></li> <li class="alt"><span> </span></li> <li class=""><span>function get_user( $name ) </span></li> <li class="alt"><span>{ </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/bad_multi1'</font></span><span>; </span> </li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class=""><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db->query( "SELECT id FROM users WHERE </span><span class="attribute"><font color="#ff0000">login</font></span><span>=?",array( $name ) ); </span> </li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">null</font></span><span>; </span> </li> <li class=""> <span>while( $res->fetchInto( $row ) ) { $</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = $row[0]; } </span> </li> <li class="alt"><span> </span></li> <li class=""><span>return $uid; </span></li> <li class="alt"><span>} </span></li> <li class=""><span> </span></li> <li class="alt"><span>function get_files( $name ) </span></li> <li class=""><span>{ </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">uid</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">get_user</font></span><span>( $name ); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">rows</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">array</font></span><span>(); </span> </li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">dsn</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">'mysql://root:password@localhost/bad_multi2'</font></span><span>; </span> </li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">db</font></span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class="alt"><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class=""><span> </span></li> <li class="alt"> <span>$</span><span class="attribute"><font color="#ff0000">res</font></span><span> = $db->query( "SELECT * FROM files WHERE </span><span class="attribute"><font color="#ff0000">user_id</font></span><span>=?",array( $uid ) ); </span> </li> <li class=""><span>while( $res->fetchInto( $row ) ) { $rows[] = $row; } </span></li> <li class="alt"><span>return $rows; </span></li> <li class=""><span>} </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute"><font color="#ff0000">files</font></span><span> = </span><span class="attribute-value"><font color="#0000ff">get_files</font></span><span>( 'jack' ); </span> </li> <li class="alt"><span> </span></li> <li class=""><span>var_dump( $files ); </span></li> <li class="alt"><span>?> </span></li> </ol>
Listing 3. Getfiles_good.php
<ol class="dp-xml"> <li class="alt"><span><span><?php </span></span></li> <li class=""><span>require_once("DB.php"); </span></li> <li class="alt"><span> </span></li> <li class=""><span>function get_files( $name ) </span></li> <li class="alt"><span>{ </span></li> <li class=""> <span>$</span><span class="attribute">rows</span><span> = </span><span class="attribute-value">array</span><span>(); </span> </li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">dsn</span><span> = </span><span class="attribute-value">'mysql://root:password@localhost/good_multi'</span><span>; </span> </li> <li class="alt"> <span>$</span><span class="attribute">db</span><span> =& DB::Connect( $dsn, array() ); </span> </li> <li class=""><span>if (PEAR::isError($db)) { die($db->getMessage()); } </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">res</span><span> = $db->query("SELECT files.* FROM users, files WHERE </span> </li> <li class="alt"> <span></span><span class="attribute">users.login</span><span>=? AND </span><span class="attribute">users.id</span><span>=</span><span class="attribute-value">files</span><span>.user_id", </span> </li> <li class=""><span>array( $name ) ); </span></li> <li class="alt"><span>while( $res->fetchInto( $row ) ) { $rows[] = $row; } </span></li> <li class=""><span> </span></li> <li class="alt"><span>return $rows; </span></li> <li class=""><span>} </span></li> <li class="alt"><span> </span></li> <li class=""> <span>$</span><span class="attribute">files</span><span> = </span><span class="attribute-value">get_files</span><span>( 'jack' ); </span> </li> <li class="alt"><span> </span></li> <li class=""><span>var_dump( $files ); </span></li> <li class="alt"><span>?> </span></li> </ol>
The code is not only shorter, but also easier to understand and 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 is a very compelling reason.