Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Database class not working with PostgreSQL
  • FuelPHP's DB class automatically add backtick (`) to column and table names which doesn't work on postgresql (v 9.5.5 that I'm using).

    Fuel\Core\Database_Exception [ 42601 ]:
    SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`" LINE 1: SELECT * FROM `qc_categories` WHERE `name` = 'Djhdjdjd' ^ with query: "SELECT * FROM `qc_categories` WHERE `name` = 'Djhdjdjd'"
  • HarroHarro
    Accepted Answer
    You can configure that in your DB configuration, using the key 'identifier'. For PostgreSQL you need to set this to "" (empty string).

    Check fuel/core/config/db.php for a PDO example.
  • Thanks. Is the backtick actually needed in mysql? I tried running query without backtick in phpmyadmin and it works.

    I want the app to be usable on both mysql and postgres so if I set it to empty will it affect mysql?
  • The backtick is "required" in MySQL only if the table or the column name is a reserved word. But it is best practice to always use it, because something can become a reserved word in the future.

    I assume you are talking about two different installations, or do you mean supporting two database engines at the same time?

    Normally, you deal with this in your db.php. You can add multiple database definitions if you want, one for MySQL and one for PostgreSQL, and use the "active" configuration value to select which one you are going to use.

    But again, assuming that database name, server name and probably username/password are unique per installation, you will have to update the db.php anyway.

    If you need to maintain these multiple installations in a single codebase, you can also use the environment. Create app/config/installa and app/config/installb, each with the correct db.php, and select the correct environment either in your htaccess or in your webserver configuration.
  • I'm doing it per installation. During installation database driver will be selected, mysql or postgres then I write to db.php file to set the db configuration for fuel php.

    I will have to perform a check whether to add backtick or not.

    Thanks a lot.
  • What you can do is have your default db.php, with the correct default settings, in app/config, and then write a db.php to app/config/production with only the changes to the default (assuming your app runs in production mode, which it should ;)).

    The two get merged when the app loads.
  • What I did is to write the database configuration inside development, production, staging and test and the user can then further modify the configuration per environment.

    I dynamically set the identifier depending on the select database during installation. Everything works properly both on mysql and postgres.

    Thanks a lot.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion