Storing global site preferences [PHP/MySQL]

Can anyone recommend the best practice for storing general site preferences? For example, the default page title if the script doesn't set one, or the number of featured items to display in a content box, or a list of thumbnail sizes that the sy开发者_如何学编程stem should make when a picture is uploaded. Centralizing these values has the obvious benefit of allowing one to easily alter preferences that might be used on many pages.

My default approach was to place these preferences as attribute/value pairs in a *gulp* EAV table.

This table is unlikely ever to become of a significant size, so I'm not too worried about performance. The rest of my schema is relational. It does make for some damn ugly queries though:

$sql = "SELECT name, value FROM preferences"
.    " WHERE name = 'picture_sizes'"
.    " OR name = 'num_picture_fields'"
.    " OR name = 'server_path_to_http'"
.    " OR name = 'picture_directory'";
$query = mysql_query($sql);
if(!$query) {
    echo "Oops! ".mysql_error();
}
while($results = mysql_fetch_assoc($query)) {
    $pref[$results['name']] = $results['value'];
}

Can anyone suggest a better approach?

---

In my application, I use this structure:

CREATE TABLE `general_settings` (
  `setting_key` varchar(255) NOT NULL,
  `setting_group` varchar(255) NOT NULL DEFAULT 'general',
  `setting_label` varchar(255) DEFAULT NULL,
  `setting_type` enum('text','integer','float','textarea','select','radio','checkbox') NOT NULL DEFAULT 'text',
  `setting_value` text NOT NULL,
  `setting_options` varchar(255) DEFAULT NULL,
  `setting_weight` int(11) DEFAULT '0',
  PRIMARY KEY (`setting_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Example data:

mysql> select * from general_settings;
+-----------------------------+---------------+------------------------------+--------------+-------------------------------+---------------------------------------+----------------+
| setting_key                 | setting_group | setting_label                | setting_type | setting_value                 | setting_options                       | setting_weight |
+-----------------------------+---------------+------------------------------+--------------+-------------------------------+---------------------------------------+----------------+
| website_name                | website       | Website Name                 | text         | s:6:"DeenTV";                 | NULL                                  |              1 | 

I store a serialized value in setting_value column. I got this trick from wordpress way to save settings in database.

setting_options column is used for a select, radio, or checkbox setting_type. It will contain a serialized array value. In admin, this value will be displayed as a options, so admin can choose one of it.

Since I use CodeIgniter, I have a model to get a single value from the particular setting_key, so it's quite easy to use.

---

That looks fine the way you're doing it.

If you're worried that your queries are looking ugly, you could try cleaning up your SQL a bit.

Here's a cleaner version of the query you gave in your question:

SELECT name, value FROM preferences
WHERE name IN ('picture_sizes','num_picture_fields','server_path_to_http','picture_directory')";

Or perhaps create a stored function to return a preference value; for example, using a stored function like this:

DELIMITER $$

CREATE FUNCTION `getPreference` (p_name VARCHAR(50)) RETURNS VARCHAR(200)
BEGIN
  RETURN (SELECT `value` FROM preferences WHERE `name` = p_name);
END $$

DELIMITER ;

You could get your preferences using a query like this:

SELECT getPreference('server_path_to_http')

You sacrifice a bit of speed by not having your preferences hard-coded (obviously). But if you plan to enable a "site administrator" to change the default preferences - you should keep them in the database.

---

I think that's a perfectly acceptable structure, especially for small amounts of configuration like you have.

You could also store these settings in an .ini file and call parse_ini_file. If you need a bit more flexibility than INI allows (eg: nested arrays, etc), then you could just put them all into a .php file and include that.

If you still want to go with the configuration in the database, then (given that there's only a handful of rows) perhaps just read all the records in one go and cache it.

$config = array();
$result = mysql_query("SELECT * FROM config");
while ($row = mysql_fetch_assoc($result)) {
    $config[$row['name']] = $row['value'];
}

---

I would think that going with an included file will save you some hassle further on - especially if you ever want to include an array as one of your variables. If you plan on changing configuration variables on the fly then perhaps its better to db it, but if its going to remain relatively static I would recommend a 'config.php' file

---

A lot of applications, including e.g. Wordpress, make use of serialization and unserialization. It allows you to create a very simple table structure maybe with even just one record (e.g. with a site_id for your project(s)).

All your (many, many) variables in an array are serialized to a string and stored. Then fetched and unserialized back to your array structure.

Pro: You don't have to plan perfect config structures beforehand, doing lots of ALTER TABLE stuff.

Con: You can't search through your serialized array structure by means of SQL.

Commands:

string serialize  ( mixed $value  )
mixed unserialize  ( string $str  )

Works also with your objects. Unserializing an object can make use of the __wakeup() method.

---

Just create a configure class and store each value you want in variable of the class.

include this class in all files which is calling.

You can access this class in all files now and by declaring global in all function you can access the configure class.

Hope this help.

---

My approach to this problem is to create a table which a separate column for each config variable, just as you would with any other dataset, and to set the primary key in such a way that the table is incapable of containing more than a single entry. I do this by setting up the primary key as an enum with only one allowed value, like so:

CREATE TABLE IF NOT EXISTS `global_config` (
  `row_limiter` enum('onlyOneRowAllowed') NOT NULL DEFAULT 'onlyOneRowAllowed',#only one possible value
  `someconfigvar` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `someotherconfigvar` varchar(32) DEFAULT 'whatever',
  PRIMARY KEY(`row_limiter`)#primary key on a field which only allows one possible value
) ENGINE = InnoDB;

INSERT IGNORE INTO `global_config` () VALUES ();#to ensure our one row exists

Once you have done this setup, any of the values can then be modified with a simple UPDATE statement, looked up with a simple SELECT statement, joined onto other tables to be used in more complex queries, etc.

Another benefit of this approach is that it allows for proper data types, foreign keys, and all the other things the come along with proper database design to ensure database integrity. (Just be sure to make your foreign keys ON DELETE SET NULL or ON DELETE RESTRICT rather than ON DELETE CASCADE). For example, let's say that one of your config variables is the user ID of the site's primary administrator, you could expand the example with the following:

CREATE TABLE IF NOT EXISTS `global_config` (
  `row_limiter` enum('onlyOneRowAllowed') NOT NULL DEFAULT 'onlyOneRowAllowed',
  `someconfigvar` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `someotherconfigvar` varchar(32) DEFAULT 'whatever',
  `primary_admin_id` bigint(20) UNSIGNED NOT NULL,
  PRIMARY KEY(`row_limiter`),
  FOREIGN KEY(`primary_admin_id`) REFERENCES `users`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB;

INSERT IGNORE INTO `global_config` (`primary_admin_id`) VALUES (1);#assuming your DB is set up that the initial user created is also the admin

This assures that you always have a valid configuration in place, even when a configuration variable needs to reference some other entity in the database.