Storing global site preferences [PHP/MySQL]

Question

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 system 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?

Answer 1

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.