Manage php application settings stored in database

Question

i have some settings stored in a mysql database table "settings" for a php web application. The table has two columns, simply "option_name" and "option_value". I've build a class to retrieve or change those settings (usually are changed through checkboxes) and i was wondering if it could be considered a good approach:

class Settings {
    public function __construct(PDO $db_connection) {
        $this->db_connection = $db_connection;
    }

    public function getValue($setting_name) {
        $sql = "SELECT option_value FROM settings WHERE option_name = ?";
        $stmt = $this->db_connection->prepare($sql);
        $stmt->execute([$setting_name]);
        return $stmt->fetchColumn();
    }

    public function setValue($setting_name, $value) {
        // Some code here to sanitize value
        if (!is_bool($value)) {
            throw new InvalidArgumentException("value must be boolean");
        }
        $sql = "UPDATE settings SET option_value = ? WHERE option_name = ?";
        $stmt = $this->db_connection->prepare($sql);
        return $stmt->execute([$value, $option_name)];
    }
}

This way every time a method is used, a query is sent to database. Also the class is just a 'wrapper' for the queries. Is this considered a good way to manage settings? I'd love to hear some suggestions

Hard to tell without more information. How many settings are there? I would probably load them all at once. — jeroen
– jeroen, Commented Feb 7, 2017 at 13:50

Leto · Accepted Answer · 2017-02-07 14:02:36Z

1

I was using that kind of solution and it worked really well for me in some web apps.

In my experience, queries to that table kind of grow in number and execution time when you start putting more and more settings in it so I usually made some sort of memory cache for that purposes (if the framework doesn't do it for you). Even though some would say that indexing option_name would make queries insignificant, on the profiling side their impact could be seen.

That cache gets filled on startup time, and refreshed on setValue (and inserts) so the selects won't need to be issued on getValue. Only problem is that if you manually change the value through the DB (or your migration script does), the cache needs to be refreshed. Multiple nodes of a single app make this even more of a hassle, but it can be done.

Even so, if your application needs this kind of dynamic configuration changes, or you have multiple settings on multiple instances of the app I would say go for it. You have run time flexibility and the database dumps would capture those settings so you wouldn't have to pull it from some external files.

P.S. Sorry for the long answer, this is my first one on StackOverflow. Have fun!

answered Feb 7, 2017 at 14:02

Leto

463 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Egidio Docile Over a year ago

Thanks, it was really helpful

Collectives™ on Stack Overflow

Manage php application settings stored in database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related