{"id":1267,"date":"2018-01-19T19:22:38","date_gmt":"2018-01-19T19:22:38","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/phplib-and-multiple-databases-page-4\/"},"modified":"2018-01-19T19:24:39","modified_gmt":"2018-01-19T19:24:39","slug":"phplib-and-multiple-databases-page-4","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/phplib-and-multiple-databases-page-4\/","title":{"rendered":"PHPLib and Multiple Databases Page 4"},"content":{"rendered":"
\n
\n
By Peter Moulding<\/div>\n
on November 7, 2000<\/div>\n<\/p><\/div>\n
\n
\n Now, for the connect code change. Notice how the connect() code checks the
\nexistence of a connection and creates the connection if the connection does
\nnot exist. This connect() function is run before every database query.
\nUnfortunately, it only selects the database once when it makes the
\nconnection. If your PHP pages use more than one database, the connect()
\n code will not pick up the change of database.<\/div>\n
\n There are several ways to change the code. We are looking for the change
\n that has the least impact on PHPLIB and lets us display the status of the
\n database activity, if we need to diagnose a problem. The two things we need
\n outside PHPLIB are the connection id and the database name. Therefore, make both
\n external to PHPLIB. In common.php3:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

$db_connection\u00a0<\/span>=\u00a0<\/span>0<\/span>;\u00a0<\/span>\/\/\u00a0Common\u00a0database\u00a0connection\u00a0id.
\n
<\/span>$db_database\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>;\u00a0<\/span>\/\/\u00a0Name\u00a0of\u00a0current\u00a0database.<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n Next, we change PHPLIB to store the connection id and database name in these
\n fields. The rest of your code can set and use the same fields. If you need
\n to find which database is in use when diagnosing a problem, insert this in
\n your page:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

<\/span>Print(<\/span>\"<p>db_database:\u00a0\"\u00a0<\/span>.\u00a0<\/span>$db_database\u00a0<\/span>.\u00a0<\/span>\"<\/p>\"<\/span>);<\/p>\n

<\/span>?><\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n (There are shorter ways to write the print line. This way highlights the
\n variable name in editors that have colour coding. It also works reliable
\n with arrays and other compound variable names.)<\/div>\n
\n How do we get connect() to use our new variables? We could add an extra
\n line near the top so you have:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

<\/span>{
\n
\u00a0\u00a0\u00a0\u00a0<\/span>globals\u00a0$db_connect<\/span>,\u00a0<\/span>$db_database<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0<\/span>\/*\u00a0Handle\u00a0defaults\u00a0*\/<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n The extra line makes our extra variables available within connect().<\/div>\n
\n Here is a more sophisticated way. Straight after the definition of
\n $db_database, add:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

<\/span>function\u00a0<\/span>db_connect<\/span>(<\/span>$db_connect_host<\/span>=<\/span>\"\"<\/span>,\u00a0<\/span>$db_connect_user<\/span>=<\/span>\"\"<\/span>,<\/span>$db_connect_pass<\/span>=<\/span>\"\"<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0<\/span>globals\u00a0$db_connect<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0if(!empty(<\/span>$db_connect_host<\/span>))\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$db_connect\u00a0<\/span>=\u00a0<\/span>mysql_pconnect<\/span>(<\/span>$db_connect_host<\/span>,\u00a0
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$db_connect_user<\/span>,\u00a0<\/span>$db_connect_pass<\/span>);
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0return(<\/span>$db_connect<\/span>);
\n
}<\/p>\n

function\u00a0<\/span>db_database<\/span>(<\/span>$db_database_new<\/span>=<\/span>\"\"<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0<\/span>globals\u00a0$db_database<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0if(!empty(<\/span>$db_database_new<\/span>))\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$db_database\u00a0<\/span>=\u00a0@<\/span>mysql_select_db<\/span>(<\/span>$db_database_new<\/span>,\u00a0<\/span>db_connect<\/span>());
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0return(<\/span>$db_database<\/span>);
\n
}<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font> <\/div>\n

\n By defining these common functions once, you can get the common variables
\n in all sorts of places, without having to add the globals line all over
\n the place. Here is the common() function using our db functions:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

<\/span>function\u00a0<\/span>connect<\/span>(<\/span>$Database\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>,\u00a0<\/span>$Host\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>,\u00a0<\/span>$User\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>,\u00a0<\/span>$Password\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0<\/span>\/*\u00a0Handle\u00a0defaults\u00a0*\/
\n
\u00a0\u00a0\u00a0\u00a0<\/span>if\u00a0(<\/span>\"\"\u00a0<\/span>==\u00a0<\/span>$Database<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$Database\u00a0<\/span>=\u00a0<\/span>$this<\/span>-><\/span>Database<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0if\u00a0(<\/span>\"\"\u00a0<\/span>==\u00a0<\/span>$Host<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$Host\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>=\u00a0<\/span>$this<\/span>-><\/span>Host<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0if\u00a0(<\/span>\"\"\u00a0<\/span>==\u00a0<\/span>$User<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$User\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>=\u00a0<\/span>$this<\/span>-><\/span>User<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0if\u00a0(<\/span>\"\"\u00a0<\/span>==\u00a0<\/span>$Password<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$Password\u00a0<\/span>=\u00a0<\/span>$this<\/span>-><\/span>Password<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0<\/span>\/*\u00a0establish\u00a0connection,\u00a0select\u00a0database\u00a0*\/
\n
\u00a0\u00a0\u00a0\u00a0<\/span>if\u00a0(\u00a0<\/span>0\u00a0<\/span>==\u00a0<\/span>db_connect<\/span>())\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$this<\/span>-><\/span>Link_ID\u00a0<\/span>=\u00a0<\/span>db_connect<\/span>(<\/span>$Host<\/span>,\u00a0<\/span>$User<\/span>,\u00a0<\/span>$Password<\/span>);
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0(!<\/span>$this<\/span>-><\/span>Link_ID<\/span>)\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$this<\/span>-><\/span>halt<\/span>(<\/span>\"pconnect($Host,\u00a0$User,\u00a0$Password)\u00a0failed.\"<\/span>);
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return\u00a0<\/span>0<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0if\u00a0(<\/span>0\u00a0<\/span>!=\u00a0<\/span>db_connect<\/span>())\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if(<\/span>$Database\u00a0<\/span>!=\u00a0<\/span>db_database<\/span>())\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$this<\/span>-><\/span>Database\u00a0<\/span>=\u00a0<\/span>db_database<\/span>(<\/span>$Database<\/span>))
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0if(empty(<\/span>$this<\/span>-><\/span>Database<\/span>))\u00a0{
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>$this<\/span>-><\/span>halt<\/span>(<\/span>\"cannot\u00a0use\u00a0database\u00a0\"\u00a0<\/span>.\u00a0<\/span>$this<\/span>-><\/span>Database<\/span>);
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return\u00a0<\/span>0<\/span>;
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0}
\n
\u00a0\u00a0\u00a0\u00a0return\u00a0<\/span>$this<\/span>-><\/span>Link_ID<\/span>;
\n
}<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n Note the slight changes.<\/div>\n
\n The test for the database is taken outside the test for the connect so that
\n connect() can check for a new database, even when there is a current
\n connection. It means we compare db_connect() to 0 twice as often. The result is worth the
\n slight extra processing.<\/div>\n
\n We place the database connection and the database selection outside of
\n PHPLIB, so we can use the same functions for database selection everywhere
\n else in your PHP code.<\/div>\n
\n There is only one disadvantage at this stage: We assume the same host, user
\n and password for all database accesses. If you have a user logging on, then
\n accessing a special database with special privileges, you will have to set
\n up a special connection for that access. How? Define variables:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

$db_host\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>;
\n
<\/span>$db_user\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>;
\n
<\/span>$db_pass\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>;<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n Expand the db_database() function to compare the current user and host to
\n the special user and host. You could also add:<\/div>\n
\n<\/p>\n

<?php<\/p>\n

$db_type\u00a0<\/span>=\u00a0<\/span>\"\"<\/span>;<\/p>\n

<\/span>?>
\n
<\/span>
\n<\/span>
\n<\/code><\/font><\/div>\n

\n…and store in it the type of database, mysql, Oracle, etc. so you can access
\n multiple databases.<\/div>\n<\/div>\n

<\/p>\n

\n
\u00ab Previous Page<\/a><\/div>\n
1<\/a> <\/div>\n
| <\/div>\n
2<\/a> <\/div>\n
| <\/div>\n
3<\/a> <\/div>\n
| <\/div>\n
4<\/div>\n
| <\/div>\n
5<\/a> <\/div>\n
| <\/div>\n
6<\/a> <\/div>\n
Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

By Peter Moulding on November 7, 2000 Now, for the connect code change. Notice how the connect() code checks the existence of a connection and creates the connection if the connection does not exist. This connect() function is run before every database query. Unfortunately, it only selects the database once… <\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1267","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1267","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1267"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1267\/revisions"}],"predecessor-version":[{"id":3160,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1267\/revisions\/3160"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1267"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1267"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1267"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}