Setting up a database connection

Both the IDE and the PHP applications need to be able to access the same database. On this page, we describe the steps you need to follow to specify database access so that you can use them in your applications.

Note! We get questions if the generated system is database independent. The boring answer is 'it depends'. As Databases are not standard, and you can supply your own SQL statements it is obvious that if you use a certain Database construct that is not supported by some other database, that you cannot 'just change the connection to another type of database' and expect it to work.

So if you need to create a real database-independent project that needs to run on different databases, say Oracle and MySQL, then you MUST be aware of the differences. So the complex answer is: PHsPeed can work with most databases, but you have to generate your project for each database separately to be safe. For most users, this will not be an issue. If you need one application that needs to be connected dynamically, depending on the customer (common in Saas environments) then this is not a problem, you can specify the credentials in the code.

In PHsPeed you define all your database connections in a single form. That way you can keep the connections together and you don't have to specify them over-and-over again.

1. Install your database client.

Depending on the database you use, you must install the appropriate client on your system. For MySQL, MariaDB, and SQLite you don't have to do anything when you are using the internal system. However, if you want to use a remote database, or a database of a different flavor (Progress, Oracle, etc.), you must set it up correctly before you can use PHsPeed. In general, you can use any database you like when there is a suitable odbc driver available. For some databases, the PDO database layer uses the native drivers.

2. Define your PHP connection string

The PHP Connection string is required as it takes care of PHP binding to the database. Some databases can have a different setup where not all of the connection parameters are set up in the PDO string but in a separate configuration file. Sometimes, it is up to the user how the database is setup. PHsPeed provides some basic connection strings you can use, but if needed, you can change them into a form that works for you. Btw. The strings are available in a pull down field, so you don't have to type them over.

Min

Min

  • The 'ID' will be the name that you will use in your PHP components to bind the application to the database.
  • If you select a database then the PDO connection string will be prefilled automatically. Depending on your usage you might have to change this.
  • If you have used an ODCB connection then you need to specify the ODBC driver. The drop-down that appears shows all the available drivers on your current workstation.

3. Setup database connection

As the final step, you must specify a connection that will be used in your project. This connection will allow PHsPeed to access your database meta-data, the content, and some parameters to be able to generate the correct SQL.

Min

To add a new connection, enter a unique name and click the add button. Then select the connection and supply:

  • database: Select the required database driver for PHsPeed IDE
  • datatype mapping: Supply the mapping to use for your database when the metadata is not returning datatype names (like varchar, int etc.) but numerics. In general this is required for ODBC drivers. The generic will do, most of the times, but you can specify your own.
  • Filter: Filter to be used on the metadata for databases that are not able to separate between user tables and system tables. (deprectated)
  • PDO Driver: The name of the pdo string that you have defined in the pdo-connection string tab.
  • ODBC Driver: Will be filled automatically if you select an odbc pdo driver, but you can modify it here.
  • DB Host: Ip of the host, usually 'localhost'.
  • DB File: name of the file to be used for the database (SQlite only).
  • Server port: the number of the port to use, i.e. 3306 for MySQL.
  • User id, password: credentials to be able to open the database. The user MUST have sufficient rights to retrieve metadata.
  • DSN: sometimes required for ODBC connections.
  • Charset: UTF8 or ISO (default UTF8).
  • Persistent connection: Maintains your database connection over sessions for a configurable amount of time. Depends on the database and used ODBC driver. Commonly set in the ODBC driver connection itself.
  • Database: The name of the database.
  • Schema: Sometimes required to allow for metadata. Usually can be left blank.
  • Select template: This is REQUIRED, and is used to generate valid SQL statements. Although SQL is supposed to be a standard, there are differences. As PHsPeed limits search results for forms (nobody is going to scroll through thousands of records) it uses a limit statement. Mysql uses a select ... where ... limit a, b; Progress uses select top ... .... ... from where. PHsPeed needs to know wink
  • Field template: It is advised not to use hyphens in field and table names. Use _ instead of - . However sometimes you cannot avoid and the character needs to be escaped in SQL statements. Sometimes by a ` sometimes by a double quote. Also database dependent,so you need to specify. If you don't use hyphens, again strongly advised, then you can leave this field empty.
  • Date format, the format of dates in the database, required for generating correct conversions.
  • Click on ok to save the connection.