Creating Your Own Support for Multiple Databases
 |
If you have a simple application that doesn't require complex database interaction, but you still want it to work with multiple databases, then you can create your own set of database wrapper functions.
You have to take into account the different database-specific limitations that may arise and code to the lowest common denominator. That is, you can't use the useful "AUTO_INCREMENT" column feature that is supported in MySQL if you also want to support MS SQL or PostgreSQL databases, since they do not implement that feature. Similarly, MySQL doesn't support secondary keys, so you cannot use that convention.
This next example provides a sample database wrapper that supports MySQL, MS SQL, and PostgreSQL databases. It is called DBlib and contains the following functions listed in Table 3-1:
| FUNCTION | DESCRIPTION |
|---|---|
| connectDB(DB_ID) | Connects to the database server and returns a DB_CONNECTION. |
| selectDB(DB_ID) | Selects which database should be used on the server. |
| queryDB(DB_ID, QUERY) | Sends an SQL query to the database. Returns a RESULT set. |
| returnDBarray(DB_ID, RESULT) | Returns an array of a row in the RESULT set. |
| numrowsDB(DB_ID, RESULT) | Returns the number of affected rows from the last query. |
| closeDB(DB_ID, DB_CONNECTION) | Closes the connection to the database. |
The first part of the example is the DBlib.php file, which you include in your own application.
Script 3 2 DBlib.php
[View full width]
1. <? 2. function connectDB($db) { 3. global $host, $database, $username, $password; 4. switch($db) { 5. case ("psql"): 6. $conn_string = "host=" . $host . " dbname=" . $database . " user=" . $username . "password=" . $password; 7. $dbconn = pg_connect($conn_string) or die ("Error Connecting to PostgreSQL DB"); 8. return $dbconn; 9. break; 10. case ("mysql"): 11. $dbconn = mysql_connect($host, $username, $password) or die ("Error Connecting to
| LINE | DESCRIPTION |
|---|---|
| 2 | Declare the connectDB function. The function requires the type of database ($db) as an argument. |
| 3 | Declare global variables that can be accessed by this function. The variables are required to connect to the database. |
| 4 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 5 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 6 | Generate a connection string for a PostgreSQL database. |
| 7 | Execute the pg_connect() function to connect to the PostgreSQL server. If there is an error, then kill the script and provide an error message. |
| 8 | If there is not an error, then return the $dbconn variable, which is the database connection handler and is required for many of the other functions. |
| 9 | Break out of the switch statement, since the case has been satisfied. |
| 10 | If $db = "mysql", then the user wants to use a MySQL database. |
| 11 | Execute the mysql_connect() function to connect to the MySQL server. If there is an error, then kill the script and provide an error message. |
| 12 | If there is not an error, then return the $dbconn variable, which is the database connection handler and is required for many of the other functions. |
| 13 | Break out of the switch statement, since the case has been satisfied. |
| 14 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 15 | Execute the mssql_connect() function to connect to the MS SQL server. If there is an error, then kill the script and provide an error message. |
| 16 | If there is not an error, then return the $dbconn variable, which is the database connection handler and is required for many of the other functions. |
| 17 | Break out of the switch statement, since the case has been satisfied. |
| 18 19 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 20 | Return 0 (false), so that any functions using this function know that the function failed. |
| 21 | End the switch statement. |
| 22 | End the function declaration. |
| 24 | Declare the selectDB function. The function requires the type of database ($db) as an argument. |
| 25 | Declare a global variable that can be accessed by this function. The variable is required to select the database. |
| 26 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 27 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 28 | Return 1 (true), so that the function accessing this function knows that the call to the SelectDB function was successful. PostgreSQL syntax specifies which database to use during pg_connect(), which should have been run before this function. |
| 29 | Break out of the switch statement, since the case has been satisfied. |
| 30 | If $db = "mysql", then the user wants to use a MySQL database. |
| 31 | Execute the mysql_select_db() function to select the proper database on the MySQL server. If there is an error, then kill the script and provide an error message. |
| 32 | If there is not an error, then return 1 (true), so that the function accessing this function knows that the call to the SelectDB function was successful. |
| 33 | Break out of the switch statement, since the case has been satisfied. |
| 34 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 35 | Execute the mssql_select_db() function to select the proper database on the MS SQL server. If there is an error, then kill the script and provide an error message. |
| 36 | If there is not an error, then return 1 (true), so that the function accessing this function knows that the call to the SelectDB function was successful. |
| 37 | Break out of the switch statement, since the case has been satisfied. |
| 38 39 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 40 | Return 0 (false), so that any functions using this function know that the function failed. |
| 41 | End the switch statement. |
| 42 | End the function declaration. |
| 44 | Declare the queryDB function. The function requires the type of database ($db) and the SQL query ($query) as arguments. |
| 45 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 46 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 47 | Run the query using pg_exec() and check if there is an error. |
| 48 | If there was an error, then set $result to 0. |
| 49 | End the if statement. |
| 50 | Break out of the switch statement, since the case has been satisfied. |
| 51 | If $db = "mysql", then the user wants to use a MySQL database. |
| 52 | Run the query using mysql_query() and check if there is an error. |
| 53 | If there was an error, then set $result to 0. |
| 54 | End the if statement. |
| 55 | Break out of the switch statement, since the case has been satisfied. |
| 56 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 57 | Run the query using mssql_query() and check if there is an error. |
| 58 | If there was an error, then set $result to 0. |
| 59 | End the if statement. |
| 60 | Break out of the switch statement, since the case has been satisfied. |
| 61 62 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 63 | Return 0 (false), so that any functions using this function know that the function failed. |
| 64 | End the switch statement. |
| 65 | Return the value of $result to the function calling this function. If the $result = 0, then the calling function knows an error has occurred. |
| 66 | End the function declaration. |
| 68 | Declare the returnDBarray function. The function requires the type of database ($db) and the result set from the previous query ($result) as arguments. |
| 69 | Verify that the result does not equal 0. If it does, then there was an error with the previous query. If there was no error, then continue. |
| 70 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 71 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 72 | Fetch the array using pg_fetch_array() and check if there is an error. |
| 73 | If there was an error, then set $array to 0. |
| 74 | End the if statement. |
| 75 | Break out of the switch statement, since the case has been satisfied. |
| 76 | If $db = "mysql", then the user wants to use a MySQL database. |
| 77 | Fetch the array using mysql_fetch_array() and check if there is an error. |
| 78 | If there was an error, then set $array to 0. |
| 79 | End the if statement. |
| 80 | Break out of the switch statement, since the case has been satisfied. |
| 81 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 82 | Fetch the array using mssql_fetch_array() and check if there is an error. |
| 83 | If there was an error, then set $array to 0. |
| 84 | End the if statement. |
| 85 | Break out of the switch statement, since the case has been satisfied. |
| 86 87 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 88 | Set $array to 0 (false), so that any functions using this function know that the function failed. |
| 89 | End the switch statement. |
| 90 | Return the value of $result to the function calling this function. If the $result = 0, then the calling function knows an error has occurred. |
| 91 | End the if statement that checked to make sure that $result did not equal 0. |
| 92 | End the function declaration. |
| 94 | Declare the closeDB function. The function requires the type of database ($db) and the database connection ($dbconn) as arguments. |
| 95 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 96 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 97 | Run pg_close() and check if there is an error. |
| 98 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 99 100 | If there was not an error, then notify the calling function that the close succeeded. |
| 101 | End the if statement. |
| 102 | Break out of the switch statement, since the case has been satisfied. |
| 103 | If $db = "mysql", then the user wants to use a MySQL database. |
| 104 | Run mysql_close() and check if there is an error. |
| 105 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 106 107 | If there was not an error, then notify the calling function that the close succeeded. |
| 108 | End the if statement. |
| 109 | Break out of the switch statement, since the case has been satisfied. |
| 110 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 111 | Run mssql_close() and check if there is an error. |
| 112 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 113 114 | If there was not an error, then notify the calling function that the close succeeded. |
| 115 | End the if statement. |
| 116 | Break out of the switch statement, since the case has been satisfied. |
| 117 118 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 119 | Return 0 to notify the calling function that the function failed. |
| 120 | End the switch statement. |
| 121 | End the function declaration. |
| 123 | Declare the numrowsDB function. The function requires the type of database ($db) and the result set from the previous query ($result) as arguments. |
| 124 | Start a switch statement that checks to see which of the cases should be run, depending on the value of $db. |
| 125 | If $db = "psql", then the user wants to use a PostgreSQL database. |
| 126 | Run pg_numrows() and check if there is an error. |
| 127 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 128 129 | If there was not an error, then notify the calling function that the close succeeded. |
| 130 | End the if statement. |
| 131 | Break out of the switch statement, since the case has been satisfied. |
| 132 | If $db = "mysql", then the user wants to use a MySQL database. |
| 133 | Run mysql_num_rows() and check if there is an error. |
| 134 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 135 136 | If there was not an error, then notify the calling function that the close succeeded. |
| 137 | End the if statement. |
| 138 | Break out of the switch statement, since the case has been satisfied. |
| 139 | If $db = "mssql", then the user wants to use a MS SQL database. |
| 140 | Run mssql_num_rows() and check if there is an error. |
| 141 | If there was an error, then return 0, notifying the calling function that the close failed. |
| 142 143 | If there was not an error, then notify the calling function that the close succeeded. |
| 144 | End the if statement. |
| 145 | Break out of the switch statement, since the case has been satisfied. |
| 146 147 | If none of the cases have been satisfied, then display a message that the $db value sent to the function was not valid. |
| 148 | Return 0 to notify the calling function that the function failed. |
| 149 | End the switch statement. |
| 150 | End the function declaration. |
The next part of the example is the SQL required to create the table for the sample application:
Script 3 3 addressbook.sql
1. CREATE TABLE addressbook ( 2. first VARCHAR(32), 3. last VARCHAR(32), 4. home VARCHAR(16), 5. cell VARCHAR(16), 6. work VARCHAR(16));
The final bit is the example application, which uses the DBlib.php file as its database wrapper, allowing the application to be used with three different database back-ends without having to change any of the code. See Figure 3-2 for the output produced by this script:
Script 3 4 customDB.php
1. <? 2. $page = "customDB.php"; 3. 4. require_once("DBlib.php"); 5. /* REQUIRED FOR DBlib.php */ 6. //$db = "psql"; //PostgreSQL Database 7. //$db = "mysql"; //MySQL Database 8. $db = "mssql"; // MS SQL Database 9. $host = "192.168.0.1"; 10. $database = "php"; 11. $username = "mssqluser"; 12. $password = "password"; 13. /**************************/ 14. 15. function display_addresses ($db) { 16. global $dbconn; 17. selectDB($db, $dbconn); 18. $sql = "select * from addressbook"; 19. if(!$result = queryDB($db, $sql)) { 20. echo "<P>Error with query!"; 21. } 22. $rows = numrowsDB($db, $result); 23. if($rows == 0) { 24. echo "There are entries in your addressbook."; 25. } else { 26. ?><table border=1><? 27. while($row = returnDBarray($db, $result)) { 28. ?> 29. <tr><td colspan=2><b><?=$row['first'];?> <?=$row['last'];?></b></td></tr> 30. <tr><td>Home: </td><td><?=$row['home'];?></td></tr> 31. <tr><td>Cell: </td><td><?=$row['cell'];?></td></tr> 32. <tr><td>Work: </td><td><?=$row['work'];?></td></tr> 33. <? 34. } 35. ?></table><? 36. } 37. } 38. 39. function add_address($db, $HTTP_POST_VARS) { 40. global $dbconn; 41. selectDB($db, $dbconn); 42. $query = "insert into addressbook values ('" . 43. $HTTP_POST_VARS['first'] . "','" . 44. $HTTP_POST_VARS['last'] . "','" . 45. $HTTP_POST_VARS['home'] . "','" . 46. $HTTP_POST_VARS['cell'] . "','" . 47. $HTTP_POST_VARS['work'] . "')"; 48. if(!queryDB($db, $query)) { 49. echo $query; 50. return 0; 51. } else { 52. return 1; 53. } 54. } 55. 56. function add_address_form(){ 57. global $page; 58. ?> 59. <h3>Add An Address:</h3> 60. <form action=<?=$page?> method=post> 61. <p>First Name: <input type="text" name="first"> 62. <br>Last Name: <input type="text" name="last"> 63. <br>Home: <input type="text" name="home"> 64. <br>Cell: <input type="text" name="cell"> 65. <br>Work: <input type="text" name="work"> 66. <p><input type="submit" name="add_address" value="Add Entry!"> 67. </form> 68. <? 69. } 70. /***** MAIN *****/ 71. ?> 72. <h3>Address Book</h3> 73. <p><a href=<?=$page?>?action=add>Add An Entry</a> 74. <p> 75. <? 76. $dbconn = connectDB($db); 77. if(isset($add_address)) { 78. if(!add_address($db, $HTTP_POST_VARS)) { 79. echo "<h3>ERROR ADDING ENTRY!</h3>"; 80. } else { 81. echo "<h3>ENTRY ADDED!</h3>"; 82. } 83. } elseif(isset($action) && $action == "add") { 84. add_address_form(); 85. } 86. ?> 87. <h4>Current Addresses:</h4> 88. <? 89. display_addresses ($db); 90. if(!closeDB($db, $dbconn)) { 91. echo "<p>ERROR CLOSING DB CONNECTION"; 92. } 93. ?>
Figure 3-2. customDB.php
| LINE | DESCRIPTION |
|---|---|
| 2 | Declare the name of the page so that it can be used in a function that prints out a form action. This is useful when testing the script if you want to quickly give it a new name. |
| 4 | Require the DBlib.php file so that this script has access to the database wrapper. |
| 6 8 | The different databases that this script can access. Uncomment only one. |
| 9 12 | Database connection information required to connect to the database server and access the specific database. |
| 15 | Declare the display_addresses function. This function takes one argument, $db, which is defined above. |
| 16 | Make the $dbconn variable, the connection handler to the database, available to this function. |
| 17 | Run the selectDB function from DBlib.php. |
| 18 | Generate an SQL statement to select all of the entries in the address book table. |
| 19 | Query the database using the queryDB function from DBlib.php. If there is not an error, then $result should contain a valid database query result. |
| 20 | If there was an error, display an error message to the screen. |
| 22 | Execute the numrowsDB function from DBlb.php. |
| 23 24 | If there are no rows in the result set, then the database is empty. Notify the user that there are no entries in the address book. |
| 25 | If there are rows in the result set, then continue executing the function. |
| 26 | Create a table to display the results. |
| 27 | Loop through each row in the result set using the returnDBarray function from DBlib.php. |
| 29 34 | Display the data in the current row. |
| 35 | Close the table. |
| 36 | End the if statement started on line 23. |
| 37 | End the function declaration. |
| 39 | Declare the add_address function. It takes as arguments the database type ($db) and the variables sent from the add_address_form, which is defined later in the script. |
| 40 | Make the $dbconn variable accessible to this function. |
| 41 | Select the database using the selectDB function from DBlib.php. |
| 42 47 | Generate an SQL statement that inserts the data from the form into the database. |
| 48 49 | If the query fails, then echo the query to help debug. |
| 50 | Return 0, since the query failed. |
| 51 53 | If the query didn't fail, then return 1. |
| 56 69 | Declare a function that prints a standard form to the browser that allows the user to enter a new address book entry. |
| 70 | Start the main part of the script. |
| 72 | Print a heading for the page. |
| 73 | Create a link that, when clicked, displays the form so the user can add another entry. |
| 76 | Create a connection to the database using the connectDB function from DBlib.php. |
| 77 | If the $add_address variable is set, then run the add_address() function to add a new entry to the address book. |
| 78 79 | If the query fails, then print an error message. |
| 80 82 | If the query is successful, then print a message telling the user. |
| 87 | Print a heading for the current addresses in the database. |
| 89 | Run the display_addresses function to print out. |
| 90 | Close the database connection using the closeDB function fromDBlib.php. |
| 91 | If there is an error closing the database connection, then notify the user. |
| 92 | End the if statement that began on line 77. |
| |
| Top |
EAN: 2147483647
Pages: 92