How to Connect to the Different Database Engine in Web Applications that Generated by PHPMaker 9.2.0

Sometimes you want to connect to the different database engines from your main web application that generated by PHPMaker. Since PHPMaker uses ADODB Library to access the databases, then we can now achive it easily and quickly.

In this article, I will show you step by step how you can do it from PHPMaker. We will start by customizing the template files, afterwards adding the code in the Server Events in order to connect and accessing the other database.

Let's assume that our main project uses MySQL as the main database. In other words, from the main project that uses MySQL, we are going to connect to MSSQL database. By using this technique, then you can create another new database connection to another database engines which suits to your needs.

[hidepost]
Follow these steps:

1. First of all, create a new PHPMaker project which will connect to MSSQL database, afterwards generate all script files using PHPMaker as always. Test your web application and make sure the connection to MSSQL database can be established properly. This is important, since we will use the generated ew_Connect() function code into the main project that uses MySQL.
2. Next step, create another new PHPMaker project which will connect to MySQL database (as the main project). Of course, in this case, you have to separate the output folder of web applications for both database engines in order to avoid files replacement among them.
3. Now open your generated phpfn9.php file that belongs to the web application that uses MSSQL database. Find the ew_Connect() function in that file, and then copy and paste all the code of the function (of course, starting from the function name until the closing curly braces) to your PHPMaker template (\Script\phpfn.php) file that belongs to MySQL project.
4. Rename the copied ew_Connect function in that \Script\phpfn.php, for example, become: ew_Connect_MSSQL. In other words, you will have two functions which will connect to MySQL and MSSQL databases. Make sure their name are different and there are no wrong syntax in the ew_Connect_MSSQL function.
5. Now go to ew_Connect_MSSQL function. Find the following code:

   $conn
   

   and then replace it with the following:

   $conn_MSSQL
   

6. Still inside the ew_Connect_MSSQL function, remove, yes just remove the following code:

   	if (EW_CODEPAGE > 0)
   		$conn->charPage = EW_CODEPAGE;
   	$conn->Connect($info, FALSE, FALSE);
   
   	// Set date format
   	if (EW_DEFAULT_DATE_FORMAT <> "")
   		$conn->Execute("SET DATEFORMAT ymd");
   	$conn->raiseErrorFn = '';
   
   	// Database connected event
   	Database_Connected($conn);
   

   as we don't need it for our case.

7. Starting this step, now we will work in the PHPMaker template that belongs to MySQL database. Open \Script\phpcommon-scripts.php file, and find this code:

   // Open connection
   if (!isset($conn)) $conn = ew_Connect();
   

   after the last line of that code, please insert the following code:

   // Open connection to MSSQL
   if (!isset($conn_MSSQL)) $conn_MSSQL = ew_Connect_MSSQL();
   

   this is important in order to establish the connection to MSSQL in each generated web page, as well as the ew_Connect() function in the main project.

8. Still in that \Script\phpcommon-scripts.php file, go to the first line and first column in the file, and find this code:

   //
   // Page class constructor
   //
   function __construct() {
   global $conn, $Language, $UserAgent;
   

   then replace it with the following code:

   //
   // Page class constructor
   //
   function __construct() {
   global $conn, $Language, $UserAgent, $conn_MSSQL; // <-- declare $conn_MSSQL as a global variable, too!
   &#91;/code&#93;
   </li>
   
   <li>
   That's all for customizing template. Now you are ready to connect to the MSSQL database by using <strong>$conn_MSSQL</strong> variable. Keep in mind that you have to declare the variable as global wherever you want to use it. For example, you want to use it inside the <strong>Row_Inserting()</strong> function of Server Events, then write the following code in the first line inside the function:
   [code lang="php"]
   global $conn_MSSQL;
   

9. Still in that Row_Inserting() function, now you can use the variable to execute the SQL as well you can do it in your main project which uses MySQL.
   For example:

   $conn_MSSQL->Execute("DELETE FROM yourtablename WHERE yourfieldname = 'yourkey'");
   

[/hidepost]