Database Setup

PHPMaker can connect to MySQL, PostgresSQL, Microsoft SQL Server, Oracle and SQLite. Click the icon in the toolbar to go to Database tab.

Notes

This article discusses connection to your database by PHPMaker, NOT by PHP. After being generated, the scripts connect to database by PHP and its database drivers on your web server, which can be a completely different environment. For example, you may connect to your local database on your Windows PC with PHPMaker, but you may upload the generated scripts to your production Linux web server and the production database server may not be installed on the same server.
If you have a development database server installed locally on the same computer that PHPMaker is installed, then you probably use "localhost" as server name when you connect to it with PHPMaker. After being uploaded to the production server, the generated scripts will try to connect to the production database server using the same connection info. If the production database server uses different info, the connection will fail. You can use either Database_Connecting server event (see Server Events and Client Scripts) or the advanced setting Connection info of production server (JSON) (see Advanced Settings) to change the connection information during runtime.

MySQL

Select MySQL as database type. Note that MySQL 4.1 and older (or MariaDB 10.1 and older) are NOT supported.

There are 3 connection methods you can choose:

Direct Connection (Default)

Enter your database host/server name (or IP address), username, password and port number (default is 3306 for MySQL),
Select your database,
Click the Connect button to load the database information.

Encrypted Connection (Optional)

From v2021 PHPMaker supports encrypted connection to MySQL server. If you have configured your MySQL server to use encrypted connection, you can click the button next to the Database type setting and enter the additional connection options:

The options are:

SSL CA Cert - The path name of the Certificate Authority (CA) certificate file.
SSL Cert - The path name of the client public key certificate file.
SSL Cipher List - The list of permissible ciphers for connection encryption. Optional, usually you can leave it blank.
SSL Key - The path name of the client private key file.
SSL TLS Protocol - The preferred TLS protocol version, reported to the server when establishing a secure connection. Optional, usually you can leave it blank.

Note Since these options are usually different on the production server, you must use Database_Connecting server event (see Server Events and Client Scripts) or the advanced setting Connection info of production server (JSON) (see Advanced Settings) to set additional connection information during runtime, see Connection Details for mysqli and pdo_mysql.

SSH Connection (for Remote Database)

From v2021 PHPMaker also supports using SSH to secure the network connection between itself and a MySQL server. Then you execute shell commands in the same fashion as if you were physically operating the remote machine. If your MySQL server is remote and it supports SSH connection, you can click the button next to the Database type setting and enter the additional connection options:

The options are:

**SSH Host** - The name or IP address of SSH server.
**SSH User** - The user id on SSH server.
**SSH Password** - The user password on SSH server. Required if you use password to login.
**SSH Port** - The number of port on SSH server to connect.
**SSH Private Key** - The location of the client private key. Required if you use private key to login.
**SSH Passphrase** - The client key passphrase. Required if your private key has a passphrase.
**SSH Cipher List** - The list of ciphers that client agrees to use, separated by colons.

**Note** Remote connection is slow, you should use SSH connection ONLY IF you cannot create the same database in your development environment.

URL Connection (for Remote Database)

(Also see Tutorial - Connecting Remote MySQL using PHPMaker Connection Script)

While the direct connection method is quick and easy, some remote MySQL servers may not allow remote connection, or are behind a firewall that does not allow you to connect to server directly at the specified port. PHPMaker supports HTTP tunneling for connecting to these servers if HTTP connections are allowed:

Upload the tunnel.php provided by PHPMaker to your site. The tunnel.php can be found under your installed folder, usually C:\Program Files (x86)\PHPMaker <version>\.
Enter your connection information, select URL for connection method, enter the URL of the script (e.g. https://servername/path/tunnel.php), you can test the URL by clicking the Test button,
Click the Connect button to load the database information. PHPMaker will connect to the database server through the PHP script over HTTP.

Notes

Remote connection is slow, you should use URL connection ONLY IF you cannot create the same database in your development environment.
You may need to create user in your database as 'username'@'%', the wildcard as host implies that the user can connect from any client host.
If the MySQL server is not installed on the web server (e.g. hosting providers may use separate MySQL servers), then the server is not "localhost", you should enter the IP address of the MySQL server.
While you can use HTTPS for the URL of the script (e.g. https://servername/path/tunnel.php), the script does not support encrypted connection as with direct connection (see above).

PostgreSQL

Select PostgreSQL as database type. Note that PostgreSQL 9.3 and older are NOT supported.

There are 2 connection methods you can choose:

Direct Connection (Default)

Enter your database host/server name (or IP address), username, password and port number (default is 5432 for PostgreSQL),
Select your database,
Click the Connect button to load the database information.

Encrypted Connection

From v2021 PHPMaker supports encrypted connection to PostgreSQL server. If you have configured your PostgreSQL server to use encrypted connection, you can click the button (next to the Database type setting) and enter the additional connection options:

The options are:

SSL CA Cert - The path name of the Certificate Authority (CA) certificate file.
SSL Cert - The path name of the client public key certificate file.
SSL Cipher List - The list of permissible ciphers for connection encryption. Optional, usually you can leave it blank.
SSL Key - The path name of the client private key file.
SSL TLS Protocol - The preferred TLS protocol version, reported to the server when establishing a secure connection. Optional, usually you can leave it blank.
SSLMode - SSL connection priority. May be Disable, Allow, Prefer, and Require. The default value is Disable, which means that only an unencrypted SSL connection will be attempted.

SSH Connection (for Remote Database)

Similar to [above](dbsetup.html?id=ssh-connection-for-remote-database) for MySQL.

**Note** Remote connection is slow, you should use SSH connection ONLY IF you cannot create the same database in your development environment.

URL Connection (for Remote Database)

Note The URL connection method is NOT supported for PostgreSQL anymore.

Microsoft SQL Server

Notes

If you have installed Microsoft SQL Server 2012 Native Client (x86) or the new Microsoft OLE DB Driver for SQL Server (x86) on your computer, then Microsoft SQL Server database type will be available for selection.
The Microsoft PHP drivers for PHP for SQL Server and Microsoft ODBC Driver for SQL Server must be installed on the web server for PHP processor to connect. See System Requirements. Note that SQL Server 2008 and older are NOT supported.
If the SQL Server is a remote server (not on the same machine as PHPMaker), make sure your SQL Server allows remote connection, you can use SQL Server Management Studio to check and configure.
If you have firewall enabled on the server, make sure it allows inbound traffic on port 1433 for TCP/IP. If the SQL Server uses another port number, make sure you have set up the firewall rules accordingly.

Select Microsoft SQL Server as database type,
Enter the name or IP of the SQL server, (if the server name includes an instance name, e.g. localhost/SQLEXPRESS, make sure you include it)
Enter the Username and Password,
Select the database you want or just enter the name of your database,
Click the Connect button to load the database information.

Oracle

Notes

From v2021 PHPMaker can connect to Oracle database without Oracle client installed on your computer.
To use direct connection without Oracle client, you need to enter the service name in the format of host:port/service_name, e.g. "localhost:1521/XE". However, note that PHP oci8 extension still need Oracle client to connect. Therefore, if you test the PHP scripts locally on your PC, you still need to install Oracle client. Make sure the path of your Oracle client (e.g. oci.dll) is among the Path variable under Windows Environment Variables -> System variable.

Select Oracle as database type,
Enter the Oracle Service name, e.g. "localhost:1521/XE"
Enter the Username and Password,
Select the Schema you want or just enter the Schema name,
Click the Connect button to load the database information.

SQLite

Select SQLite as database type,
Select the SQLite database file by clicking the button,
Specify the database path where the database file will reside under the your project folder,
Click the Connect button to load the database information.

Note Database path is relative to project folder. Use slashes "/" as path delimiter, no leading slash. e.g. If the project folder of your website is D:\websites\demo and you enter "data/" in this textbox, the folder for the database will be D:\websites\demo\data.

Dynamic Table Loading

By default all tables in the database are loaded. It is convenient but loading and sychronization could be slow if your database contains a large number of tables or fields. If this option is enabled, a table will only be loaded when you select it in the database pane. If you just use a few tables out of a large database, this feature enables you to work much faster than before. To enable this feature, simply check Load tables dynamically BEFORE pressing the Connect button.