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

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.

Answer 2

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'];
}

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.